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".
Comments
-
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.
-
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")))))))
-
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.
-
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????
-
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.
-
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.
-
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")))))))
-
Do appreciate the response but not sure what to do with that formula.
Total newB here. -
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
-
Any chance you could post a link to a sheet with this formula implemented.
-
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!!")))))))))))))
-
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.
-
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
-
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
-
Thanks JohnHinkle, this worked for me!
