Calculating Working Hours between days

Hello Kyriacos, welcome to MrExcel

Are working hours 07:30 to 14:30 Monday to Friday only?

If so you can use this formula

=(NETWORKDAYS(A2,B2)-1)*(J$3-J$2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),J$3,J$2),J$3)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),J$3,J$2)

where A2 contains start time/date, B2 contains end time/date and J2:J3 defines the working hours, so for your case J2 should be 07:30 and J3 should be 14:30

Format cell with the formula as [h]:mm

Hi Barry

Thanks the formula is working as expected,

something else........

i want also to calculate the holidays days as non working days, i've already create a range with name holiday.

How can i procced?

Best Regards..........

You can just insert the holiday range into each of the NETWORKDAYS functions, i.e.

=(NETWORKDAYS(A2,B2,holiday)-1)*(J$3-J$2)+IF(NETWORKDAYS(B2,B2,holiday),MEDIAN(MOD(B2,1),J$3,J$2),J$3)-MEDIAN(NETWORKDAYS(A2,A2,holiday)*MOD(A2,1),J$3,J$2)

barry houdini said:

You can just insert the holiday range into each of the NETWORKDAYS functions, i.e.

=(NETWORKDAYS(A2,B2,holiday)-1)*(J$3-J$2)+IF(NETWORKDAYS(B2,B2,holiday),MEDIAN(MOD(B2,1),J$3,J$2),J$3)-MEDIAN(NETWORKDAYS(A2,A2,holiday)*MOD(A2,1),J$3,J$2)

Click to expand...

Hi Barry, this is an amazing formula. If you dont mind, can you please explain the Median(Mod part of the formula). Thank You.

I have not seen much of that type of formula with the Median, Max, Min with Mod and Int in date and time Formulas etc...and would be greatful if you could explain Median(Mod part of the formula) so i can understand how we get the exact times between 2 days.

Thank you so much and much appreciated.

I have kinda picked up text parts of formulas but still trying to grasp time/day formulas with Formats etc... you seem to amaze me more and more with your date and time formulas.

Can you advise how i can learn more on date and time formulas and what advise can you give and which are the best date/time formulas to use?

i.e to work out working days, Holidays, Gannt charts, Birthdays etc....

Thank You

Thank you for that formula. I re-created it using (hopefully understandable) named ranges to make it easier for this novice to port to my workbook. So, I thought I would share and hopefully someone else may find it easier to adapt this very useful formula.

Thanks:

Formula:
=(NETWORKDAYS(Start,End,Holidays)-1)*(BizEnd-BizStart)+IF(NETWORKDAYS(End,End,Holidays),MEDIAN(MOD(End,1),BizEnd,BizStart),BizEnd)-MEDIAN(NETWORKDAYS(Start,Start,Holidays)*MOD(Start,1),BizEnd,BizStart)

Named Ranges being used:
Start (one cell): Start time of the duration to be measured
End (one cell): End time of the duration being measured
BizStart (one cell): Time of day the business hours begin
BizEnd (one cell): Time of day the business hours end
Holidays (list - I used a column): List of Holidays that fall on a weekday

Of course if I used this formula in a datasheet with many start and end values in (columns, say), I would replace the Start and End named ranges with relative cell values so it could be copied down properly where I needed it.

Hello,

I have a similar formula that includes an IF to only calculate if the end date is avaliable. The problem I have is for start and end times that are over 24 hours. Is there any way to modify the formula below to include the time taken to complete over 24 hours or in to include days/hour/min?

Thanks in advance....

=IF((NETWORKDAYS(F3,I3)-1)*("17:00"-"08:00")+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1),"17:00","08:00"),"17:00")-MEDIAN(NETWORKDAYS(F3,F3)*MOD(F3,1),"17:00","08:00")>=0, (NETWORKDAYS(F3,I3)-1)*("17:00"-"08:00")+IF(NETWORKDAYS(I3,I3),MEDIAN(MOD(I3,1),"17:00","08:00"),"17:00")-MEDIAN(NETWORKDAYS(F3,F3)*MOD(F3,1),"17:00","08:00"), " ")


Example time frame:
F3= 3/1/2012 08:00
I3 = 3/6/2012 14:30

Result should be 33:30:00 or 3 Days 6 Hours 30 mins

What result do you get? You should get the correct result with that formula.....but make sure you custom format result cell as [h]:mm, the square brackets means that it shows elapsed time, possibly over 24 hours

Barry, Thanks for the info, I thought I had the formating [h]:mm upon verifying it was not. Seems that all the numbers work now.

Been racking my brain for the past 4 day because I was sure of the formating. LOL the simplest thing are the ones that mess you up.

Once Again THANK YOU.

Dear Barry Houdini,

This is an excellent formula however, when I am trying to use your formula with little addition as below

=IF(OR(ISBLANK(A2),ISBLANK(B2)),"NA",IF(B2 <a2,"end
< A2 ,"End Date earlier than Start Date", YOUR FORMULA HERE))
where A2 has 1-Apr-13 08:38:55 and B2 has 1-Apr-13 17:38:54, Also I have 1-Apr-13 listed in my Holiday.
The result is giving me error in this senario but works well if 1-Apr-13 is not listed in my Holiday. Could you please help.

Many thanks,
Sreekanth</a2,"end>