Converting text to date via formula
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!
Comments
-
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.
-
So glad to see something simple to solve this! Thanks a bunch!
-
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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!