Excel multiple IF, AND, OR nested statements - with date and time criteria

0
votes

I need to write an 'if' statement to output either DAY, NIGHT, or WEEKEND based on the day of the week and times as below:

output DAY if the date and time is Monday to Friday 7am to 9pm
output NIGHT if the date and time is Monday to Thursday 9pm to 7am
output WEEKEND if the date and time is Friday 9pm to Monday 7am.

My data comes like this below in half hourly increments :

24/04/2015 16:30
24/04/2015 18:00
24/04/2015 18:30
24/04/2015 20:30
24/04/2015 21:00
24/04/2015 21:30
24/04/2015 23:00
24/04/2015 23:30
25/04/2015 0:00
25/04/2015 0:30
25/04/2015 1:00
25/04/2015 10:00
25/04/2015 11:30
25/04/2015 22:00
25/04/2015 22:30
25/04/2015 23:00
25/04/2015 23:30
26/04/2015 0:00
26/04/2015 0:30
26/04/2015 18:30
26/04/2015 19:00
26/04/2015 19:30
26/04/2015 20:00
26/04/2015 20:30
26/04/2015 21:00
26/04/2015 21:30
26/04/2015 23:00
26/04/2015 23:30
27/04/2015 0:00
27/04/2015 0:30
27/04/2015 1:00
27/04/2015 6:30
27/04/2015 7:00
27/04/2015 7:30

(theres a total of 17,000 rows of half hourly data for an entire year, SO I have altered some of the days and times so that it's easier to work with and there should be some data that matches all 3 of the DAY, NGHT, WEEKEND criteria)

I have studied this solution here https://stackoverflow.com/a/15754238/1602250, and it makes sense but I can't get it to work.

I've output the day of the week into a 2nd column and tried this:

=IF(AND(A2="Fri",A1=">9:00:01 p.m.",A1="<7:00:01 a.m."),"WEEKEND") - but this needs to say between Fri9pm and Mon7am.

I've also tried this one, doesn't work either.
=IF(OR(A2="Mon",A2="Tue",A2="Wed",A2="Thu",A2="Fri"),IF(A1=">7:00:00 a.m.", A1="<9:00:00 p.m.", "DAY", IF(AND(OR(A2="Sat",A2="Sun", "WEEKEND")))

Please can someone help, I'm going half crazy...

1
exceldatetimeif-statementnested
Will take some time to dive into this. In the meantime I'd recommend using excelformulabeautifier.com to keep your thoughts straight. - sadtank

1 Answers

1
votes

I suppose your data in column A, and datatype is text. So I'll get date/time.

  1. Column B: Get date: =DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))
  2. Column C: Get time: =RIGHT(A2,LEN(A2)-11)
  3. Column D: Do req: =IF(AND(WEEKDAY(B2)>=2,WEEKDAY(B2)<=6,TIMEVALUE(C2)>=TIMEVALUE(TEXT("7:00","HH:mm")),TIMEVALUE(C2)<TIMEVALUE(TEXT("21:00","HH:mm"))),"DAY",IF(AND(WEEKDAY(B2)>=2,WEEKDAY(B2)<=5,OR(TIMEVALUE(C2)>TIMEVALUE(TEXT("7:00","HH:mm")),TIMEVALUE(C2)>=TIMEVALUE(TEXT("21:00","HH:mm")))),"NIGHT","WEEKEND"))

please see attachment. Hope this help.