CODEWITHSUNDEEP
google-sheets
Brandon
Brandon

Reputation: 117

How to have the value within a cell change off of the condition of time of day and date?

I am looking to have cell X11 (currently "AM") change off of the condition of the time of the day and the day of the week to reflect my companies 3 shifts. I am struggling to find a way.

The shifts are: AM (Monday-Thursday, 7:00am - 6:00pm) PM (Monday-Thursday, 9:00pm - 7:00am) WKD (Friday-Sunday, 7:00am - 7:00pm)

Right now I have the shift cell (currently "AM", X11) hard keyed to change the shift times, however, ideally for example, when it is Thursday at 10pm, it would automatically change to "PM", and when it becomes Saturday at 7am, it would change to "WKD", and then Monday at 7am it changes to "AM".

See Here

Here are the current formulas

Upvotes: 0

Views: 67

Answers (1)

Nikko J.
Nikko J.

Reputation: 5543

Try this formula:

=IF(OR(AND(WEEKDAY(NOW(), 2) = 1, AND(HOUR(NOW()) >= 0, HOUR(NOW()) < 7)), AND(WEEKDAY(NOW(), 2) >= 5, AND(HOUR(NOW()) >= 7, HOUR(NOW()) <= 19))), "WKD", IF(AND(HOUR(NOW()) >= 7, HOUR(NOW()) <= 18), "AM", "PM"))

Test Cases:

enter image description here

Formula for each test case:

WEEKDAYS

  • Monday 7am: =IF(OR(AND(1=1, AND(7 >= 0, 7 < 7)), AND(1 >= 5, AND(7 >= 7, 7 <= 19))), "WKD", IF(AND(7 >= 7, 7 <= 18), "AM", "PM"))
  • Monday 9pm: =IF(OR(AND(1=1, AND(21 >= 0, 21 < 7)), AND(1 >= 5, AND(21 >= 7, 21 <= 19))), "WKD", IF(AND(21 >= 7, 21 <= 18), "AM", "PM"))
  • Thursday 6pm: =IF(OR(AND(4=1, AND(18 >= 0, 18 < 7)), AND(4 >= 5, AND(18 >= 7, 18 <= 19))), "WKD", IF(AND(18 >= 7, 18 <= 18), "AM", "PM"))
  • Friday 6am: =IF(OR(AND(5=1, AND(6 >= 0, 6 < 7)), AND(5 >= 5, AND(6 >= 7, 6 <= 19))), "WKD", IF(AND(6 >= 7, 6 <= 18), "AM", "PM"))

WEEKENDS

  • Monday 6am: =IF(OR(AND(1 >= 5, AND(6 >= 7, 6 <= 19)), 1 = 1, AND(6 > 0, 6 < 7)), "WKD", IF(AND(6 >= 7, 6 <= 18), "AM", "PM"))
  • Friday 7am: =IF(OR(AND(5 >= 5, AND(7 >= 7, 7 <= 19)), 5 = 1, AND(7 > 0, 7 < 7)), "WKD", IF(AND(7 >= 7, 7 <= 18), "AM", "PM"))

References:

Upvotes: 1

Related Questions