Converting text to date via formula

kym4639

Good afternoon.

I have created a sheet with a form to capture reservation details for a live performance. There are 4 performance date options and form users identify their 1st-4th choices. Once received, they are assigned a date and the cell in the appropriate "Confirmed" column is marked YES. There are 4 "Confirmed" columns, one for each potential performance date. 

The formula below is working just fine. However, the text (eg. 04.25/18) is not being recognized as a DATE, so I cannot see the confirmed attendees in calendar view. The text (eg. 04/25/18) is coming through as simply TEXT. I've tried adding another column and somehow converting the TEXT to a DATE in that column and it still doesn't work.

=IF([Tu24 Confirmed]23 = "YES", "04/24/18", IF([W25 Confirmed]23 = "YES", "04/25/18", IF([Th26 Confirmed]23 = "YES", "04/26/18", IF([Fr27 Confirmed]23 = "YES", "04/27/18"))))

Is there anything that I can do short of manually entering a confirmed date for each row?

Thanks in advance for your help!

Shaine Greenwood

Hello,

Use the DATE function in these cases: https://help.smartsheet.com/function/date

Here's an example based on the formula that you provided:

=IF([Tu24 Confirmed]23 = "YES", DATE(2018,4,24), IF([W25 Confirmed]23 = "YES", DATE(2018,4,25), IF([Th26 Confirmed]23 = "YES", DATE(2018,4,26), IF([Fr27 Confirmed]23 = "YES", DATE(2018,4,27)))))

In Smartsheet formulas, quotes will only format values as text, even when you put quotes around something that looks like a date or number.

kym4639

So glad to see something simple to solve this! Thanks a bunch!

smantics

Hi! on a slightly similar note- I'm trying track birthdays of a large group. Since people do not want to put in the year they were born, the format they are entering into the text column is something like: "8/7"

I'd like to put this into a calendar and can concatenate the 8/7 with "/18" resulting in "8/7/18"... but not sure how to convert this into an actual date to be shown on a calendar to track.

Thoughts? is there a way to convert this into a useable calendar date?