Date and Time

[nianchi111]

Dear friends,

I have to prepare a report and I'm not getting the calculation correct.

I have 2 timings I have to subtract and get the actual time taken to complete that work.

If I receive the email atfer 5:30 pm it should show as next day 9:00 am and if I receive a mail on friday after 5.30 it should show the time as Monday 9.00 am. Please help me this is a manual work for me.

I will give you some of the time from the report.

19/08/2011 10:21 22/08/2011 09:55
17/08/2011 13:41 18/08/2011 14:07
17/08/2011 13:18 18/08/2011 14:43
16/08/2011 15:55 18/08/2011 15:40
16/08/2011 12:19 19/08/2011 14:34
16/08/2011 10:53 18/08/2011 15:40
16/08/2011 10:14 17/08/2011 10:25

 

[Lewiy]

Give this formula a try - not fully tested, but I think it will do what you want, it assumes your two times are in columns A and B respectively:

=TEXT(IF(NETWORKDAYS(A2,B2)=1,B2-A2,((INT(A2)+TIME(17,0,0))-A2)+(B2-(INT(B2)+TIME(9,0,0)))+(TIME(8,0,0)*((NETWORKDAYS(A2,B2))-2))),"[hh]:mm:ss")

 

[nianchi111]

For 18/08/2011 12:46 19/08/2011 10:24 result is "#NAME?".

How to change to 17:30:00

Please help me.

 

[Lewiy]

Ah, ok, you need to add the Analysis ToolPak on your Add-Ins

 

[nianchi111]

Thank you so much.

 

[barry houdini]

Is it possible that you could receive an eamil at 07:00 on Tuesday and complete the work at noon that day? If so the time taken is 3:00 isn't it?

This formula will give that result

=(NETWORKDAYS(A2,B2)-1)*("17:30"-"9:00")+MOD(B2,1)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:30","9:00")

assumes that email can arrive at any time but that it will be completed within business hours

format result cell as [h]:mm