Error in WEEKDAY formula

Tamara K · 09/17/19

I'm struggling with a formula that seems to be broken.

I created a timesheet that has a cell that looks at the Auto Date/Time stamp and calls up the weekday. The formula is as follows:

=WEEKDAY([Date/Time]129)

It works great...unless the timestamp is 5:00PM or later. Then it results in the next day. So if the timestamp is 5:02 pm on Monday, it will try to tell me it's Tuesday. See image for more details.

I did verify that the formula is referencing the proper row/cell in all cases. Something else is happening. Any insights would be appreciated.

Paul Newcome · 09/19/19

It has to do with your timezone. I have seen this brought up a few other times as well. You can use an IF statement to basically tell it to subtract 1 if the time is 5p or later.

=IF(AND(CONTAINS("p", [Date/Time]@row), VALUE(MID([Date/Time]@row, FIND(" "< [Date/Time]@row) + 1, FIND(":", [Date/Time]@row) - (FIND(" "< [Date/Time]@row) + 1))) >= 5, WEEKDAY([Date/Time]@row) - 1, WEEKDAY([Date/Time]@row))

Give this a try and see if it works for you.

NOTE: I replaced numbered row references with "@row". It helps things run a little smoother, but if you are referencing a cell that is NOT on the same row as the formula, you will need to change it to the actual row number.

itsnotbroken · 11/24/21

I know this is 2 years old, but I have the same issue.

If it's a time zone issue, than I want to adjust the clock, not add a ton of code in a cell to work around a bug.

Is there somewhere in system admin settings to move my time zone?

itsnotbroken · 12/16/21

Hi,

Before I go too far on this,

I get the main idea. If the time contains a "P" AND it's after 5PM, then subtract a day, otherwise leave it alone.

That first If statement can’t find the “P” in the [Date/Time] system column

I should be getting a "NO"

That's a date field, right?

Kevin Turcotte · 03/22/23

Hey there is a good way to break this down into a helper columns. "Created" is the name of my auto date column when referencing the following formulas.

1) create a column to extract AM or PM. use the =right formula to take it from the created date column.

=RIGHT(Created@row, 2)

2) create another column to extract the hour using the mid formula. It is essential to use vlaue here because we want the number returned to be a value so we can add it to 12.

=VALUE(MID(Created@row, 10, 1))

3) create a third column to convert to 24 hour time. use the IF formula, and say if the value returned in step 1's column is PM, then add step 2's value (the hour) to 12 (to convert to 24h time). If not, then simply return the hour in that column.

=IF([AM PM Helper]@row = "PM", [Date Hour Extraction]@row + 12, [Date Hour Extraction]@row)

4) create a fourth column to do the magic. if the 24h time conversion is greater then 16 (aka 16:59 aka 4:59), then created date - 1. otherwise, simply return the created date.

=IF([24h time Conversion]@row > 16, Created@row - 1, Created@row)

5) Run the weekday() formula against step 4's returned value. In this example you can see we have data at 4:58PM and 5:20PM in this example. After this work, they both returned weekday of 1 which was Sunday and is accurate.

=WEEKDAY([Date Formatted to Time Zone]@row)

Error in WEEKDAY formula

Comments

Help Article Resources

Categories