Formula for days of week a ticket was submitted
Hello,
I am looking for a formula to put the days of the week a form was submitted. i.e. monday, Tuesday, Wednesday, etc. I already have a created date column which i need to stay as it is but want another column for the specific day that it was.
Best Answers
-
@Jeremy Oesch The formula below will do what you want. You just need to update to reference "Date@row" to match your column name.
=IF(WEEKDAY(Date@row) = 1, "Sunday", IF(WEEKDAY(Date@row) = 2, "Monday", IF(WEEKDAY(Date@row) = 3, "Tuesday", IF(WEEKDAY(Date@row) = 4, "Wednesday", IF(WEEKDAY(Date@row) = 5, "Thursday", IF(WEEKDAY(Date@row) = 6, "Friday", IF(WEEKDAY(Date@row) = 7, "Saturday", "")))))))
-
It is a time zone issue. The first step is to figure out what hour the day starts going forward by one. Once you know that, we can use a helper column (easier to manage) to output the correct date and then reference that helper column in your nested IF.
In the below, replace the bold ## with whatever hour the date push happens.
=DATEONLY([Date Opened]@row) - IF(AND(FIND("P", [Date Opened]@row) > 0, VALUE(MID([Date Opened]@row, FIND(" ", [Date Opened]@row) + 1, FIND(":", [Date Opened]@row) - (FIND(" ", [Date Opened]@row) + 1))) >= ##, VALUE(MID([Date Opened]@row, FIND(" ", [Date Opened]@row) + 1, FIND(":", [Date Opened]@row) - (FIND(" ", [Date Opened]@row) + 1))) < 12), 1, 0)
Answers
-
@Jeremy Oesch The formula below will do what you want. You just need to update to reference "Date@row" to match your column name.
=IF(WEEKDAY(Date@row) = 1, "Sunday", IF(WEEKDAY(Date@row) = 2, "Monday", IF(WEEKDAY(Date@row) = 3, "Tuesday", IF(WEEKDAY(Date@row) = 4, "Wednesday", IF(WEEKDAY(Date@row) = 5, "Thursday", IF(WEEKDAY(Date@row) = 6, "Friday", IF(WEEKDAY(Date@row) = 7, "Saturday", "")))))))
-
It works great thank you very much!
-
Okay sorry but i was looking more into it and it did not work. It does give me the days but the days are incorrect. For example, The first two rows are dates 05/10/2024. The first one gives me friday which is correct but the second one gives me saturday? Now all days apart from the first column are a day or more off.
-
@Jeremy Oesch Did you pull the formula down through the rows or did you make it a column formula.
Check your formula in the working row and compare it to a non-working row. They should be exactly the same.
-
I converted it to a column formula for the "day of the week" column. Even when I create new column and pull them down they still have the same issue of being the wrong day. The other column "Date Opened" Isn't a formula and is just when a form was submitted and uses the column type date created. I don't have a "Working row" as it didnt work from the beginning. Sorry if I am misunderstanding what you are asking but I cant pull them down as we have a Dashboard that pulls data so i need it to be a column formula so it stays current.
-
It is a time zone issue. The first step is to figure out what hour the day starts going forward by one. Once you know that, we can use a helper column (easier to manage) to output the correct date and then reference that helper column in your nested IF.
In the below, replace the bold ## with whatever hour the date push happens.
=DATEONLY([Date Opened]@row) - IF(AND(FIND("P", [Date Opened]@row) > 0, VALUE(MID([Date Opened]@row, FIND(" ", [Date Opened]@row) + 1, FIND(":", [Date Opened]@row) - (FIND(" ", [Date Opened]@row) + 1))) >= ##, VALUE(MID([Date Opened]@row, FIND(" ", [Date Opened]@row) + 1, FIND(":", [Date Opened]@row) - (FIND(" ", [Date Opened]@row) + 1))) < 12), 1, 0)
-
Thank you Yes it a global tracker and thought it would follow my time settings. Thank you!
-
The DISPLAY will be based on your time zone, but the back-end data is not unfortunately. This is a known issue.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!