My Date Helper Row is Showing the Wrong Centure 1923 Instead of 2023

CathyP712

Hi All,

I created a sheet where my users will select from a dropdown of available dates. This column is set to Drop-Down so they only can select the dates I offer. (Column Name = Selected Date)

I have a helper column that is set to "Date" and has a formula to turn the text data into a Smartsheet date. (Column Name = Date Helper).

I have used the following formula for the "Date Helper" column:

=DATE((VALUE(RIGHT([Selected Date]@row, 2))), (VALUE(LEFT([Selected Date]@row, 2))), (VALUE(MID([Selected Date]@row, 4, 2))))

It's bringing back the correct month & day, but it's bringing back year values from the 1900s and not the current 2000s.

Example: I select "09/18/2023" from the drop-down in the "Selected Date" column. The formula in the "Date Helper" column coverts it to 09/18/1923.

Is there a way to get the formula to show the proper year?

Thanks! - Cathy

Paul Newcome

Try this:

=DATE(VALUE("20" + RIGHT([Selected Date]@row, 2)), VALUE(LEFT([Selected Date]@row, 2)), VALUE(MID([Selected Date]@row, 4, 2)))

CathyP712

Update: I found a work-around where I add 36525 to the formula to get 2023, but I'm sure there is a better option.

=36525 + DATE((VALUE(RIGHT([Selected Date]@row, 2))), (VALUE(LEFT([Selected Date]@row, 2))), (VALUE(MID([Selected Date]@row, 4, 2))))

Paul Newcome

Try this:

=DATE(VALUE("20" + RIGHT([Selected Date]@row, 2)), VALUE(LEFT([Selected Date]@row, 2)), VALUE(MID([Selected Date]@row, 4, 2)))

CathyP712

https://community.smartsheet.com/discussion/comment/395464#Comment_395464

Oh that worked, thank you!

May I ask why adding the "20" updates the date to 2023, no rush, just curious. Thank you :)

Paul Newcome

When outputting a date through a formula, the century defaults to 19## if you don't specify. It is just how Smartsheet is programmed.