Error in WEEKDAY formula

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.

Find more posts tagged with

Formulas

Comments

Paul Newcome

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

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

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

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)

Sign in to join the conversation!

Quick Links

Trending discussions

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…