Solved

date time calculation

Posted on 2016-07-27
9
42 Views
Last Modified: 2016-08-31
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
Comment
Question by:Maliki Hassani
  • 3
  • 2
  • 2
9 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 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

by:Glenn Ray
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

by:Maliki Hassani
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:Maliki Hassani
ID: 41732095
Like is it when it turns 6 hrs or 5. (Anything) is non compliant.
0
 
LVL 48

Expert Comment

by:PortletPaul
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


12am in excel is the beginning of a day
0
 
LVL 27

Expert Comment

by:Glenn Ray
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 48

Expert Comment

by:PortletPaul
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now