Show date as day of the week

Jessica Randall

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

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

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

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

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

 

https://app.smartsheet.com/b/publish?EQBCT=3522c35c29c34120bb5d1208ec1362cc

 

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

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

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:

 

https://app.smartsheet.com/b/publish?EQBCT=183801de006a4c0999f9337e1fd00ecc

 

code:

=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:

 

https://www.smartsheet.com/blog/relative-and-absolute-references

Travis

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

 

https://app.smartsheet.com/b/launch?lx=vDnSjzBcshuK3dQxSOwB8l2F3tjZfBYMXSEruozjq1E

Jason.Valleau

Thanks JohnHinkle, this worked for me!