Need a formula to indicate if the date and time is in working hours or out of working hour

Hi,

I have range of dates like below:
Submission Date
1/1/2015 13:31
1/1/2015 15:17
1/1/2015 13:28
1/1/2015 16:19
1/1/2015 18:09
1/1/2015 18:11
1/1/2015 17:00
1/1/2015 19:57
1/1/2015 23:14
1/2/2015 1:10
1/2/2015 0:59
1/2/2015 11:04
1/2/2015 12:58
1/2/2015 12:49
1/2/2015 14:48
1/2/2015 13:06

Or office working hours is from Sunday to Thursday from 7 am to 4 pm.
I need a formula in the column next to this column that will mention if this date and time is a working day or non-working day.

Regards,
Dipika

Assuming dates with times are in A1 and below....

=IF(AND(A1>=WORKDAY.INTL(A1,1,1)-1+7/24,A1<=WORKDAY.INTL(A1,1,1)-1+16/24),"WORKING","NOT")

Copy down.

Originally Posted by jhren

Assuming dates with times are in A1 and below....

=IF(AND(A1>=WORKDAY.INTL(A1,1,1)-1+7/24,A1<=WORKDAY.INTL(A1,1,1)-1+16/24),"WORKING","NOT")

Copy down.

Thanks a lot dear... it works :-) u saved my time.. it wud be helpful if u cud tell how this formula work.
so that i can use it in other reports as well.
thanks again...

WORKDAY.INTL(start_date, days, [weekend], [holidays])
WORKDAY.INTL(A1,1,1) takes date of A1 and adds one work day... the last "1" specifies Saturday and Sunday as weekend days,rather than Friday and Saturday, because it's followed by -1... so Friday has to register as a workday because subtracting 1 makes it a Thursday. If you have a list of holidays, insert as fourth element... but keep in mind the dates may have to be one day after the actual holiday.

The +7/24 and +16/24 after those 2 occurrences add the start (7:00 AM) and end (4:00 PM or 1600) to the WORKDAY.INTL() result, which is always 12:00 AM (0000).

The AND wraps two arguments to verify start time or later and end time or earlier. Returns TRUE when both arguments are TRUE, otherwise returns FALSE.