Using dates without a date column.

Liam Burnett

Hi,

Is there a way to use dates in formulas without them being in a date column?

I am trying to reference a drop-down column that has some dates in it and then take 42 days off of them.

Is there a way to do this without the dates being in a date column as it needs to be a drop-down column.

Thanks,

Liam

Paul Newcome

My apologies. The MID statement should start at character 4 instead of 3.

=DATE(VALUE("20" + RIGHT([Drop Down]1, 2)), VALUE(MID([Drop Down]1, 4, 2)), VALUE(LEFT([Drop Down]1, 2)))

Paul Newcome

It is possible, but it would be much easier to use a helper column of the date type to convert it into a date first then reference the new column.

How is the data in the dropdown formatted?

mm/dd/yy

dd/mm/yy

dd/mm/yyyy

mm/dd/yyyy

other?

Liam Burnett

I have tried using a helper column already below:

The date column changes the drop down value into a date value.

And the dates are formatted dd/mm/yyyy

Thanks,

Liam

Paul Newcome

What formula are you using in the Date column?

To have an actual date, you would need to use a DATE function.

=DATE(yyyy, mm, dd)

To pull the year from the dropdown, you would use something along the lines of

=VALUE("20" + RIGHT([Drop Down]@row, 2))

The month:

=VALUE(MID([Drop Down]@row, 3, 2))

The day:

=VALUE(LEFT([Drop Down]@row, 2))

Then you would drop each of those formulas into the DATE function:

=DATE(VALUE("20" + RIGHT([Drop Down]@row, 2)), VALUE(MID([Drop Down]@row, 3, 2)), VALUE(LEFT([Drop Down]@row, 2)))

Liam Burnett

=DATE(VALUE("20" + RIGHT([Drop Down]1, 2)), VALUE(MID([Drop Down]1, 3, 2)), VALUE(LEFT([Drop Down]1, 2)))

When I use this I get #INVALID VALUE

Paul Newcome

My apologies. The MID statement should start at character 4 instead of 3.

=DATE(VALUE("20" + RIGHT([Drop Down]1, 2)), VALUE(MID([Drop Down]1, 4, 2)), VALUE(LEFT([Drop Down]1, 2)))

Liam Burnett

It works!

Thank you very much for your help :)

Paul Newcome

Happy to help! 👍️

Liam Burnett

Hi,

I have another questions relating to this.

I have some dates that are in this format 20-Feb-20. I have used a formula to change the feb into a 2 so it can be used in a date however the formula below now produces a #invalid value, why is this?

=DATE(VALUE("20" + MID([GW4]4, 7, 2)), VALUE([Column14]4), VALUE(LEFT([GW4]4, 2)))

Liam Burnett

Sorry to clarify GW4 holds the date and column14 holds the month number,

Thanks,

Liam

Paul Newcome

Your MID is grabbing the hyphen before the 20. If you are using

dd-MMM-yy

as your "text date" format, you can just use a RIGHT statement to grab the year in a similar fashion as to how you used a LEFT to grab the day.

=DATE(VALUE("20" + RIGHT([GW4]4, 2), ........................................................)

Liam Burnett

Thank you, it works :)

Paul Newcome

Excellent. Happy to help! 👍️