Calculating a day from a date

I've got a column (called "Dates") formatted as dates. I would like the adjacent column to show the day of the week that date falls on. Using the formula =text((Date1), "dddd") brings back an #unparseable error in the cell. I've tried using brackets instead of parenthesis but it hasn't worked.

I must be messing up the formatting somewhere but I can't figure it out. Help, please!

Find more posts tagged with

Sheets

Hospitality

Calendar View

Formulas

IT & Operations

Conditional Formatting

Cell linking

Comments

Shaine Greenwood

Hello Dajah,

We're working on improvements for our date formatting for future versions of Smartsheet and I'll add your vote for a format that provides the day of the week to our enhancement request list for review by our Product team.

In the meantime, the best way to handle this would be to create a new text/number column to display your day of the week, then use a nested IF statement that places the day of the week depending on the weekday number. An example is as follows:

=IF(ISBLANK(Date1), "", IF(WEEKDAY(Date1) = 1, "Sunday", IF(WEEKDAY(Date1) = 2, "Monday", IF(WEEKDAY(Date1) = 3, "Tuesday", IF(WEEKDAY(Date1) = 4, "Wednesday", IF(WEEKDAY(Date1) = 5, "Thursday", IF(WEEKDAY(Date1) = 6, "Friday", IF(WEEKDAY(Date1) = 7, "Saturday"))))))))

The above will use the WEEKDAY function to return a text value for the day of the week, returning nothing if the column named "Date" is blank.

Daniel Lee

If Smartsheet could implement the CHOOSE function, you could greatly simplify the IF function in your example. Using CHOOSE, the same formula becomes

=CHOOSE(WEEKDAY(Date1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"). Don't know if that's under consideration by the dev team, or if CHOOSE is already implemented and I have the Smartsheet syntax wrong.

Thank you very much. This is exactly what I needed. Luckily, my column containing date was named as 'Date' and I could just copy paste your formula in a cell and then copy paste that cell in the full range.