WEEKDAY function returns incorrect day
I have the following formula setup to capture the day of the week:
=IFERROR(IF(WEEKDAY([Date Created]@row) = 1, "Sunday", IF(WEEKDAY([Date Created]@row) = 2, "Monday", IF(WEEKDAY([Date Created]@row) = 3, "Tuesday", IF(WEEKDAY([Date Created]@row) = 4, "Wednesday", IF(WEEKDAY([Date Created]@row) = 5, "Thursday", IF(WEEKDAY([Date Created]@row) = 6, "Friday", IF(WEEKDAY([Date Created]@row) = 7, "Saturday"))))))), "")
For the most part, it works fine, however, I have a few rows where it returns the next day.
Example, several rows from 7/24/2023 return Monday, but a few return Tuesday. These rows are being entered via Forms, and were from our local site. I've tried adding the "DATEONLY" function inline, but it returns the same results. Below are a few of my cells showing the Date Created and the above formula's result. I do have a similar issue when our international sites are entering rows, so I'm not sure if that's a time zone issue on their part, but if they are entering via forms, does that even matter, if the user entering the data never accesses the parent sheet?
07/24/23 11:55 PM Tuesday
07/24/23 11:53 PM Tuesday
07/24/23 8:04 PM Tuesday
07/24/23 2:14 PM Monday
07/24/23 11:47 AM Monday
Answers
-
It very well could be a time zone issue. It isn't so much who is creating the row that determines the time zone. It is more so the time zone of the owner of the sheet. There are a number of solutions here in the Community that should help with getting the appropriate date, but the basic logic is looking for "PM" and a certain hour and then subtracting a day if it meets the criteria.
=DATEONLY([Created Date]@row) - IF(AND(FIND("P", [Created Date]@row)> 0, VALUE(MID([Created Date]@row, FIND(" ", [Created Date]@row) + 1, FIND(":", [Created Date]@row) - (FIND(" ", [Created Date]@row) + 1)))>= 8), 1, 0)
The 8 there close to the end is where you would enter the hour needed to adjust for.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
This is probably not the problem but just in case it is an issue with the data type coming in from the web forms do you have the column properties set to "Restrict to dates only"? Not sure if Smartsheets is treating some incoming forms date's as text or not but I know I ran into issue before with calculating days because it doesn't like text.
-
-
I'm somewhat following your logic, but I did try the formula you posted and I get an error. I just can't seem to grasp the formula itself, it seems like something is missing.
-
The formula I provided needs to go into a date type column if using it as a standalone. It will output the expected created date after adjusting for the time zone. You can either reference this new column in your formula(s), or you can nest it into your existing formula(s).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you for that, I have it working now, and the Days align with the dates.
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I'm still having some issues. It seems no matter what I adjust the "8" value to, some date is always incorrect. It almost seems like that "8" needs to be the same value as the hour of the day for it to calculate correctly, but you mention adjusting for time zone, and I'm just lost as to how to proceed from here.
I did manage to break down and decipher the formula into multiple columns (to help me understand), but it all keeps coming back to that value and it's relation to the hour of day.
-
The key is figuring out which hour triggers the push to the next day. In theory (and depending on your time zone), you could need to move into AM hours. What is the sheet owner's time zone?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I am the sheet owner, and it's -4 (US/Eastern).
-
Have you tried just using the 8? I do remember a lot of times the change over was based on Seattle time (PST).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I have tried every number from 1 to 13, and it won't correct everything. A lot of my issues seem to revolve around the 12-1PM time frame it seems: (Smartsheet default Create Date on left, corrected date per above formula on left, forumla set to a value of 8)
Other times late in the night seem fine:
-
And that makes perfect sense. 12 is greater than 8, so it is going to adjust it. Sorry about that. Try this one:
=DATEONLY([Created Date]@row) - IF(AND(FIND("P", [Created Date]@row)> 0, VALUE(MID([Created Date]@row, FIND(" ", [Created Date]@row) + 1, FIND(":", [Created Date]@row) - (FIND(" ", [Created Date]@row) + 1)))>= 8, VALUE(MID([Created Date]@row, FIND(" ", [Created Date]@row) + 1, FIND(":", [Created Date]@row) - (FIND(" ", [Created Date]@row) + 1))) <> 12), 1, 0)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
That seems to have done the trick, thank you again.
-
Happy to help. 👍️
As many times as I have done this... I forget about that noon piece every single time...
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!