WEEKDAY function returns incorrect day

Phil E

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

Paul Newcome

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.

Cory Page

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.

Phil E

@Cory Page

I'm using the standard "Created Date" column type.

Phil E

@Paul Newcome

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.

Paul Newcome

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

Phil E

@Paul Newcome

Thank you for that, I have it working now, and the Days align with the dates.

Paul Newcome

Happy to help. 👍️

Phil E

@Paul Newcome

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.

Paul Newcome

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?

Phil E

I am the sheet owner, and it's -4 (US/Eastern).

Paul Newcome

Have you tried just using the 8? I do remember a lot of times the change over was based on Seattle time (PST).

Phil E

@Paul Newcome

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:

Paul Newcome

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)

Phil E

@Paul Newcome

That seems to have done the trick, thank you again.

Paul Newcome

Happy to help. 👍️

As many times as I have done this... I forget about that noon piece every single time...