Excel: 3 Conditions Issue

0
votes

Below is an excel formula i created that will result in either of the following:

Weekend, Week Night, or Week Day

If Saturday or Sunday shows in column AL my result is 'Weekend'. If result falls on a Week day (Monday-Friday) between hours of 12a-7:59p the result will be 'Week Day'; and 'Week Night' as my false value.

Below is my fomula:

=IF(OR(AL2="Saturday",AL2="Sunday"),"Weekend",IF(AND(AL2="Monday",AL2="Tuesday",AL2="Wednesday",AL2="Thursday",AL2="Friday",D2>TIME(12,0,0),D2<TIME(7,59,0)),"Week Day","Week Night"))

The Weekend part of the formula works; but all my others result in Week Night

Please help!

2
excelif-statement
TIME(12,0,0) midnight should be TIME(0,0,0)? - findwindow
Change the AND to an OR for checking the weekdays (like you have for checking the weekend days earlier in the formula) - tigeravatar

2 Answers

0
votes

Try

=IF(OR(AL2="Saturday",AL2="Sunday"),"Weekend",IF(AND(OR(AL2="Monday",AL2="Tuesday",AL2="Wednesday",AL2="Thursday",AL2="Friday"),D2>TIME(0,0,0),D2<TIME(7,59,0)),"week day","week night"))
0
votes

The times that bracket the week night/week day options are a little unclear but perhaps this can be used or modified to suit.

="Week"&IF(LEFT(AL2)="s", "end", IF(AND(HOUR(D2)<16, HOUR(D2)>=0), " day", " night"))

That considers a Week day to be from midnight to 4 pm (e.g. 00:00:00-15:59:59).