IF statement for after hours/normal hours mapping

[genghiskhan]

Hi all, I have only one date and time (e.g. 02/07/2012 08:00) in A2 for ambulance callouts. After hours for me means before 08:00 and after 18:00 Mon to Fri; before 08:00 and after 12:00 Sat; and all days Sundays and public holidays. Even it is not possible for public holidays for you because we might be in different countries, please advise how to map this date and time field to "After Hours" in B2. Thanks so much from Mongolia.

 

[Andrew Poulsom]

Try:

=IF(OR(WEEKDAY(A2)=1,MOD(A2,1)<("08:00"+0),MOD(A2,1)>(IF(WEEKDAY(A2)=7,"12:00","18:00")+0)),"After Hours","")

 

[genghiskhan]

Try:

=IF(OR(WEEKDAY(A2)=1,MOD(A2,1)<("08:00"+0),MOD(A2,1)>(IF(WEEKDAY(A2)=7,"12:00","18:00")+0)),"After Hours","")

That's brilliant! I have tested it now and it works. GK

 

[genghiskhan]

Hi Andrew P, I would really appreciated if you could explain a little bit more on your formula; esp. MOD and OR parts. Thanks. GK

That's brilliant! I have tested it now and it works. GK

 

[Andrew Poulsom]

The MOD function returns the remainder after division of its first argument by its second argument. If the first argument is a date and time and its second argument is 1 the time will be returned (since dates and times are really just numbers).

 

[genghiskhan]

The MOD function returns the remainder after division of its first argument by its second argument. If the first argument is a date and time and its second argument is 1 the time will be returned (since dates and times are really just numbers).

thanks Andrew P. You are very kind. GK from Mongolia