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.

Show date as day of the week


How can I use this forumla  =TEXT(A3, "ddd")  ??  This formula calculates the day of the week for the date and returns the abbreviated name of the day of the week (Thu).  Every time I try to use this formula, it says "not recognized". 





  • Travis
    Travis Employee
    edited 06/11/15

    Hello, we do not support a formula that will pull the week day name from a date, but I will add your vote for this enhancement request! It's possible a Community member has a workaround they could suggest. 

  • John Sauber
    John Sauber ✭✭✭✭✭✭

    You need 6 rows which will live permanently in your sheet (or on another sheet which you link to using cell linking). Each row will have a dummy task starting on each of the days of the week. For example:


    Row 115 has a dummy task which starts on Sunday

    Row 116 has a dummy task which starts on Monday


    Row 121 has a dummy task which starts on Saturday.


    Our "Start" column is called [Start On]. To find out what day of the week "today" is, use this formula:

    =IF(INT((TODAY() - $[Start On]$115) / 7) - (TODAY() - $[Start On]$115) / 7 = 0, "Sunday", IF(INT((TODAY() - $[Start On]$116) / 7) - (TODAY() - $[Start On]$116) / 7 = 0, "Monday", IF(INT((TODAY() - $[Start On]$117) / 7) - (TODAY() - $[Start On]$117) / 7 = 0, "Tuesday", IF(INT((TODAY() - $[Start On]$118) / 7) - (TODAY() - $[Start On]$118) / 7 = 0, "Wednesday", IF(INT((TODAY() - $[Start On]$119) / 7) - (TODAY() - $[Start On]$119) / 7 = 0, "Thursday", IF(INT((TODAY() - $[Start On]$120) / 7) - (TODAY() - $[Start On]$120) / 7 = 0, "Friday", IF(INT((TODAY() - $[Start On]$121) / 7) - (TODAY() - $[Start On]$121) / 7 = 0, "Saturday", "Error")))))))


    Replace "TODAY()" with a reference to the same row's start date, and you will get the day of the week each row's task starts on.


    The example for this in our sheet, with our column names:

    =IF(INT(([Start On]1 - $[Start On]$115) / 7) - ([Start On]1 - $[Start On]$115) / 7 = 0, "Sunday", IF(INT(([Start On]1 - $[Start On]$116) / 7) - ([Start On]1 - $[Start On]$116) / 7 = 0, "Monday", IF(INT(([Start On]1 - $[Start On]$117) / 7) - ([Start On]1 - $[Start On]$117) / 7 = 0, "Tuesday", IF(INT(([Start On]1 - $[Start On]$118) / 7) - ([Start On]1 - $[Start On]$118) / 7 = 0, "Wednesday", IF(INT(([Start On]1 - $[Start On]$119) / 7) - ([Start On]1 - $[Start On]$119) / 7 = 0, "Thursday", IF(INT(([Start On]1 - $[Start On]$120) / 7) - ([Start On]1 - $[Start On]$120) / 7 = 0, "Friday", IF(INT(([Start On]1 - $[Start On]$121) / 7) - ([Start On]1 - $[Start On]$121) / 7 = 0, "Saturday", "Error")))))))


  • Ian Mirfin

    Here is the solution that I came up with to solve a simlar problem.


    Using a few extra columns for references. (Which you can hide later)


     - BaseDate (Type Date) row 1 will have a date that you know what the date is

     - DateNumber (Text) 0 through to 6

     - DayName (Text) The Text you wish to use eg "Mon"

     - WeekDay Calc - Formula

    =IF(ISDATE([Due date]11), ROUND(((([Due date]11 - $[Base Date]$1) / 7 - INT(([Due date]11 - $[Base Date]$1) / 7)) * 7), 0), -1)

     - WeekDay Name - Formula

    =IF(ISBLANK([Due date]11), "", IF(WeekDayCalc11 = $DateNumber$1, $DayName$1, IF(WeekDayCalc11 = $DateNumber$2, $DayName$2, IF(WeekDayCalc11 = $DateNumber$3, $DayName$3, IF(WeekDayCalc11 = $DateNumber$4, $DayName$4, IF(WeekDayCalc11 = $DateNumber$5, $DayName$5, IF(WeekDayCalc11 = $DateNumber$6, $DayName$6, IF(WeekDayCalc11 = $DateNumber$7, $DayName$7, IF(ISDATE([Due date]11), "ERROR", "")))))))))


    For Example

    BaseDate 1 = 01/07/2013 (which is a Monday)

    DateNumber1 = 0 DayName1 = Mon

    DateNumber2 = 1 DayName2 = Tue

    DateNumber3 = 2 DayName3 = Wed

    DateNumber4 = 3 DayName4 = Thr

    DateNumber5 = 4 DayName5 = Fri

    DateNumber6 = 5 DayName6 = Sat

    DateNumber7 = 6 DayName7 = Sun


    Then use the formulas as above and you will get the day of the week you need. 


    If you want to use a different base date you could update the key to suit.




  • Daniela Patterson
    edited 09/01/15

    Hi all


    Considering all the walk around and to be honest complexity with it, would it not be best to just build in the baseline of the tool the ability to show day along with date in the field similar to MS Project????

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭

    I came up with the following solution.  I didn't see this thread until Travis pointed it out to me.




    I am currently using it to tell me if something is due this calendar week and display those items in a report.  This helps make sure people focus on critical items.   This also gives the added feature of showing which number day of the week this is, what I am calling the Ordinal Day.  This allows me to know how many days are left in the calendar week.

  • Marc

    Anyone offer a dumbed down explanation to get days of the week into SS? Having a tough time as I'm new to this and very surprised it isn't easily available.


  • JohnHinkle
    JohnHinkle ✭✭✭✭✭✭

    Try this:


    =IF(WEEKDAY([Start Date]6) = 1, "Sunday", IF(WEEKDAY([Start Date]6) = 2, "Monday", IF(WEEKDAY([Start Date]6) = 3, "Tuesday", IF(WEEKDAY([Start Date]6) = 4, "Wednesday", IF(WEEKDAY([Start Date]6) = 5, "Thursday", IF(WEEKDAY([Start Date]6) = 6, "Friday", IF(WEEKDAY([Start Date]6) = 7, "Saturday")))))))

  • Marc

    Do appreciate the response but not sure what to do with that formula. :) Total newB here.


  • JohnHinkle
    JohnHinkle ✭✭✭✭✭✭

    Marc, that is a nested IF formula. Lets break it down...


    IF formula:


    IF(logical_test, value_if_true, value_if_false)


    IF(this is true, do this)


    So, we want, if the weekday is 1 (first day of the week = sunday), then show Sunday.


    =IF(WEEKDAY(Cell1) = 1, "Sunday"


    This says, if the week day for Cell1 is 1, then show "Sunday" as the formula result.


    Now we add Monday to this.


    =IF(WEEKDAY(Cell1) = 1, "Sunday", IF(WEEKDAY(Cell1) = 2, "Tuesday"


    And that is a nested IF statement! 


    The formula above has 7 nested IFs, one for each day of the week.


    To use this in your sheet, replace [Start Date]6 with the cell containing the date, and the formula will display the day of the week.


    Read through this help center article on formula for more help: http://help.smartsheet.com/customer/en/portal/articles/775363-using-formulas

  • Marc

    Any chance you could post a link to a sheet with this formula implemented.

  • Lisa Pinto

    hi marc

    public link to a demo:





    =IF(WEEKDAY([Start Date]3) = 1, "Sun", (IF(WEEKDAY([Start Date]3) = 2, "Mon", (IF(WEEKDAY([Start Date]3) = 3, "Tues", (IF(WEEKDAY([Start Date]3) = 4, "Wed", (IF(WEEKDAY([Start Date]3) = 5, "Thurs", (IF(WEEKDAY([Start Date]3) = 6, "Fri", (IF(WEEKDAY([Start Date]3) = 7, "Sat", "Invalid Value!!")))))))))))))

  • Marc

    Do appreciate it but still struggling. Trying to get the start date in date to automatically put in the day of the week. Tried using the formula from the sample you posted (thank you for that) but I still can't get it to work correctly. It is just pulling the same Mon/Tue from the sheet and can't sort out what I shoul dbe changing.



  • Lisa Pinto

    1. I created a new column on row 3 called "Day"

    2. When I pasted the formula, [Start Date]3 is referring to the Start Date column, on the same row (row 3), so it's an instruction relative to where you are pasting the formula.


    Please read this link about relational cell references, I think that's what is messing up for you:



  • Travis
    Travis Employee

    Marc, this technique will not change the date format to include the day, but rather will show the day of a corresponding date.


    Click the following link to have a sheet copied to your account. You will be the owner of this sheet and can play with it to help you understand the workaround. 


    The formula is located in the  Week Day column (Text/Number) and will show the week day of the corresponding Start Date column (Date).



  • Jason.Valleau

    Thanks JohnHinkle, this worked for me!

This discussion has been closed.