Solved

# date time calculation

Posted on 2016-07-27
Medium Priority
67 Views
Hi,

I have a simple question but I think I have had a long day to be questioning this. :)

I want to see if this is compliant vs. Non-compliant.

Received date = 01/05/2016 1:53:10 PM
Ack_lttr_sent_date = 01/13/2016 12:00:00AM

Is this time difference <= 5 days (not including Saturday and Sunday)

Thanks
0
Question by:Maliki Hassani
• 3
• 2
• 2

LVL 27

Accepted Solution

Glenn Ray earned 2000 total points
ID: 41732087
No.  It's seven (7) business days.

Try the NETWORKDAYS function  to see for yourself.  Assume
A1: 01/05/2016 1:53:10 PM
B1: 01/13/2016 12:00:00 AM

then
=NETWORKDAYS(A1,B1) = 7

Regards,
Glenn
1

LVL 27

Expert Comment

ID: 41732090
PS  If you're having to measure on a 24-hour clock, then modify the formula like so:
=NETWORKDAYS(A1,B1)+IF(TIMEVALUE(TEXT(B1,"hh:mm:ss"))>TIMEVALUE(TEXT(A1,"hh:mm:ss")),0,-1)
0

Author Comment

ID: 41732093
There are weekends between those dates. I think it's like 5.(something ) hours. I wasn't sure if 5.78 hours is compliant if saying <=5

Thanks
0

Author Comment

ID: 41732095
Like is it when it turns 6 hrs or 5. (Anything) is non compliant.
0

LVL 49

Expert Comment

ID: 41732307
"12:00 AM"  In Excel (see image below) is the beginning of the day (i.e. a time of 00:00:00)

Jan 2016
5   6    7   8  9 10  11  12  13
T   W   T   F  S   S   M   T    W
1    2   3   4            5     6    7    --<< working days

0

LVL 27

Expert Comment

ID: 41732439
The NETWORKDAYS function takes into account weekends and holidays, if you specify them.  I stand by my earlier solutions.  It's seven business days on a calendar basis or 6 days and change if including hh:mm
0

LVL 49

Expert Comment

ID: 41732544
@Glenn absolutely, I was attempting to display:

D6 in the image is =networkdays(D3,D4)
(and is 7.000)

If you need to deal with hours then it is less that 7.0     (approximately 6.421412037 working days)
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.