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!

Find more posts tagged with

Formulas

Comments

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…