# Using dates without a date column.

Options

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

Tags:

• ✭✭✭✭✭✭
Options

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?

• Options

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

• ✭✭✭✭✭✭
Options

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)))

• Options

=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

• Options

It works!

Thank you very much for your help :)

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

• Options

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)))

• Options

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

Thanks,

Liam

• ✭✭✭✭✭✭
Options

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), ........................................................)

• Options

Thank you, it works :)

• ✭✭✭✭✭✭
Options

Excellent. Happy to help! 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!