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....

Please Login or Register  to view this content.

Copy down.

Originally Posted by jhren

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

Please Login or Register  to view this content.

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.