Total Time taken

[srinivas_naik]

Hi All,


Need formula to calculate the total time between two dates

Eg:


A1=11/19/2010 7:34 AM

B1=11/23/2010 6:50 PM


Want to calculate total time between A1 and B1.

It should exclude weekends

It should take time only from morning 08:00 AM to 05:00 PM(weekdays).


Let me know how can i achieve this with a forumula.


Thanks in advance


Regards,

srinivas

 

[VaraK]

Hi Srinivas,


This is little long..but works.


=NETWORKDAYS(A1,B1)*9-IF(HOUR(A1)<8,0,(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))-TIME(8,0,0))*24)+IF(HOUR(B1)>17,0,(TIME(HOUR(B1),MINUTE(B1),SECOND(B1))-TIME(17,0,0))*24)


Change the format of the cell to general to see the time difference in hours.


Hope this helps.

 

[srinivas_naik]

Hi Vara,


thanks for the formula.


I just checked with few. The result as shown below:


10/3/2010 14:06 10/4/2010 3:39 -10.44

10/21/2010 11:06 10/21/2010 11:09 0.051666667

10/10/2010 5:50 10/14/2010 8:15 27.26111111

10/11/2010 6:15 10/11/2010 6:20 -1.662222222


The negative value should be 0 and I want the result in hh:mm:ss format. anyway to get in that format?


Thanks in advance

Regards,

Srinivas

 

[keymaster]

@Srinivas.. read this: http://chandoo.org/wp/2010/09/10/working-hours-formula/ You will find answer to your question.