formula to Calculate Working Times between two dates and time - help needed

[aamirabro]

Hi all,
I want to calculate Working time, which only include time from 09:00(9am) to 18:00 (6pm) on weekdays Monday to Friday only.

I m using this Formula

=if(AND (OR (E11>=Time(18,0,0),E11<time(9,0,0) ),or="" (<="" span=""></time(9,0,0)>G11>=Time(18,0,0),G11<time(9,0,0)),networkdays(< span=""></time(9,0,0)),networkdays(<>D11,F11)=2),Time(0,0,0), (if(NETWORKDAYS(D11,F11) = 0, NETWORKDAYS(D11,F11), NETWORKDAYS(D11,F11)-1)*TIME(9,0,0)+G11-TIME(9,0,0)-If(OR(WEEKDAY(D11,1)=7,WEEKDAY(D11,1)=1),time(0,0,0),if(E11>=Time(9,0,0),if(E11>=Time(18,0,0),Time(9,0,0),E11-Time(9,0,0)),Time(0,0,0)))) )

I want a formula that can correctly calculate time spend in all the folowing scenarios.

> If Start Time is on week day in working hours and end time is on the same day but after working hours say like at 19:00 (7pm) it should only calculate time spent till 6 pm.

> If Start Time is on week day but after working hours and End time is also before any working hour start it should calculate 00:00:00 (zero time).

> so in short it should only STRICTLY calculate the work hours between start and end.

please modify the above formula or give any other formula that works great

by the way ....
D1 is start Date (only date).
F1 is End Date (only date).
E1 is start Time (only Time).
G1 is End Time (only Time).

work Hours : 09:00 (09 pm) to 18:00 (6 pm)
Work Days : Monday - Friday

any help will be appreciated ...

 

[barry houdini]

Hello aamirabro, welcome to MrExcel. Try this version for row 1

=(NETWORKDAYS(D1,F1)-1)*("18:00"-"9:00")+IF(NETWORKDAYS(F1,F1),MEDIAN(G1,"9:00","18:00"),"18:00")-MEDIAN(NETWORKDAYS(D1,D1)*E1,"9:00","18:00")

format result cell as [h]:mm

 

[aamirabro]

Thanks Barry Haudini for your help but as it turns out it is formatting result as X.XXXXX. what I Want is that it should format it as time like XX:XX:XX .

AND
here is my DATA.
D1 is start Date (only date). [9/7/2012 ]
F1 is End Date (only date). [9/7/2012]
E1 is start Time (only Time). [13:03:00]
G1 is End Time (only Time). [22:05:00]

my Above formula result [ 9:02:00]

Your formula Result [0.376388888888889]

Answer Needed [4:57:00]

as [22:05:00] is not working hour and I took my time to complete it in my Off Hour so it Should calculate time till last work hour that is [18:00:00]

please help me out ...
NOTE : I'm Using it in Google SpreadSheet

 

[barry houdini]

You should custom format the result cell as [h]:mm as I said - when I used my formula with your examples I got 4:57 as required

 

[aamirabro]

hi Barry as I told you I'm working on Google Spreadsheet , Can you please guide me how to format it in Google Spreadsheet.

do I have to format only the result cell on my data cells too.

thanks

 

[aamirabro]

Hi barry ...
after googling how to format I was able to format and it was actually very simple and I feel kind of embraced to have asked that question ...

so now the formula that you gave me is not working ... like for Eg

D1 : 9/7/2012 (start date)
E1: 10:00:00 (start Time)
F1: 9/7/2012 (End Date)
G1: 20:00:00 (End Time)

Result I1: 10:00:00 by using your formula

Result I1: 8:00:00 by using my formula and thats what I WANT because working hours at upto 18:00 so 10:00 to 18:00 is 8 hours.

Now Answer to the obvious question that arrises .... why I dont use my own Formula??? because it breaks down in some other conditions ....

NOTE : that i have change the formula and updated formula is

=if( OR ( NETWORKDAYS(D12,F12) = 0, AND( NETWORKDAYS(D12,F12) = 1, E12>Time(18,0,0) , G12>Time(18,0,0)) , AND( NETWORKDAYS(D12,F12) = 1, E12<Time(9,0,0) , G12<Time(9,0,0)) , AND( NETWORKDAYS(D12,F12) <= 2, E12>Time(18,0,0) , G12<Time(9,0,0)) ),Time(0,0,0), (if(NETWORKDAYS(D12,F12) = 0, NETWORKDAYS(D12,F12), NETWORKDAYS(D12,F12)-1)*TIME(9,0,0)+(if(G12<=Time(18,00,00) , if (G12<Time(9,0,0), Time (9,0,0) , G12 ) , Time (18,0,0)))-TIME(9,0,0)-If(OR(WEEKDAY(D12,1)=7,WEEKDAY(D12,1)=1),time(0,0,0),if(E12>=Time(9,0,0),if(E12>=Time(18,0,0),Time(9,0,0),E12-Time(9,0,0)),Time(0,0,0)))) )

thanks for taking your time ....

 

[barry houdini]

OK, I didn't see that you were use Googledocs, my suggestion works in Excel but for google it needs a small tweak - try this version

=(NETWORKDAYS(D1,F1)-1)*(ʺ18:00ʺ-ʺ9:00ʺ)+IF(NETWORKDAYS(F1,F1),MEDIAN(G1,ʺ9:00ʺ+0,ʺ18:00ʺ+0),ʺ18:00ʺ+0)-MEDIAN(NETWORKDAYS(D1,D1)*E1,ʺ9:00ʺ+0,ʺ18:00ʺ+0)

 

[aamirabro]

Hi barry
sorry to disturb you again but this formula has some syntax error(s) .
I have Checked it on both: microsoft excel and Google SpreadSheet

 

[aamirabro]

Problems with my code

D1 : 9/7/2012 (start date)
E1: 2:00:00 (start Time)
F1: 9/8/2012 (End Date)
G1: 9:10:00 (End Time)

Result: I1 : 0:10:00 using my formula which is wrong

RESULT that I Want is 9:00:00 as start time is on friday 2am and there was a full day of friday which is 9 hours and 10 mins of Saturday (9/8/2012) after 9am should not be counted.

any help will be appreciated .....

 

[barry houdini]

OK sorry, seems to have difficulty with the quotes when copied from here, try this version

=(NETWORKDAYS(D1,F1)-1)*(18-9)/24+IF(NETWORKDAYS(F1,F1),MEDIAN(G1,9/24,18/24),18/24)-MEDIAN(NETWORKDAYS(D1,D1)*E1,9/24,18/24)