Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Calculating a day from a date

Dajah
Dajah
edited 12/09/19 in Archived 2016 Posts

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!

Comments

  • Shaine Greenwood
    Shaine Greenwood Employee
    edited 09/29/16

    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. Laughing

     

    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. 

     

     

  • 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.

  • MR
    MR ✭✭

    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.

This discussion has been closed.