# Solved Calculating Network Days between two date/time stamps

May 6, 2015 at 06:03:01
Specs: Excel 2010
 Help, I have two dates one in column F3 01/05/2015 12:07 and one in G3 06/05/2015 09:52 - both Uk format dd/mm/yyyy and time hrs and mins. I want to calculate the time taken between these two,our working hours 08:30 - 18:00 and business working days Mon -Fri. I want the answer to show HRS/MINS and everything I have tried has failed. I need the formula to deduct weekends if the range covers a weekend out of the answer, and to deduct the non working hours between 6pm and 8.30am. We run flexible holidays so no requirement to have a holidays column. Help!

See More: Calculating Network Days between two date/time stamps

#1
May 6, 2015 at 06:56:26

Report •

#2
May 6, 2015 at 08:05:58
 Mike thank you, the answer gives days and hours, what I am trying to show in the calc is HRS:Mins only as I have a secondary calc off the back of this to show whether we met SLA or not. So F3 = 01/05/2015 12:07 and G3 = 06/05/2015 09:52, I'll simplify the working day to 09:00 - 17:30. So calc should return 23 hrs15 mins which is 05:23 + 08:30 + 08:30 + 00:52. Ultimately this calc is then used in the secondary formula. I need to end up with whether the SLA being 2 hrs, 6 hrs, 24 hrs or 48 hrs was met, the SLA being based on the time taken from start to finish and whether this was above or below the SLA for the piece of work.Hopefully I'm being specific enough.message edited by GregMartin

Report •

#3
May 6, 2015 at 09:58:48
 Ok if all you want is HOURS / MINUTES try this.With your data like:```Work Day Starts in AA1 = 09:00 Work Day Ends in AA2 = 16:30 Holidays in AB1:AB5 Start Date & Time in A1 End Date & Time in B1 ```First, Format cell C1 as Custom, [HH]:MM the square brackets are necessary, then enter the formula:=IF(AND(INT(A1)=INT(B1),NOT(ISNA(MATCH(INT(A1),\$AB\$1:\$AB\$5,0)))),0,ABS(IF(INT(A1)=INT(B1),ROUND(24*(B1-A1),2),(24*(\$AA\$2-\$AA\$1)*(MAX(NETWORKDAYS(A1+1,B1-1,\$AB\$1:\$AB\$5),0)+INT(24*(((B1-INT(B1))-(A1-INT(A1)))+(\$AA\$2-\$AA\$1))/(24*(\$AA\$2-\$AA\$1))))+MOD(ROUND(((24*(B1-INT(B1)))-24*\$AA\$1)+(24*\$AA\$2-(24*(A1-INT(A1)))),2),ROUND((24*(\$AA\$2-\$AA\$1)),2))))))/24It is a very long formula, so you might want to just cut & paste.See how it works for you.MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
May 7, 2015 at 00:38:40
 Mike thanks again, unfortunately this returns a calc of 1.024 which I'm not sure this denotes. I followed you steps including formatting the [HR]:MM. I'm not sure if what I am asking for is possible and maybe I need to change my SLA calc to match the newtworkdays formula instead. To be honest I'm really not sure at this stage, what do you think?So 06/05/2015 09:52 minus 01/05/2015 12:07 based on a 7.5 hr working day should equated to 2 days 6 hrs approx. Seperately if I change my SLA calcs (eg the 2 hrs SLA to 0.27 of a day) so I can match against the networkdays calc it should give me what I want. What do you think?

Report •

#5
May 7, 2015 at 03:13:43
 The formula worked on my test sheet, so not sure why it does not for you.The data was ( In US format )``` A B C 1) Friday 05/01/2015 12:07 Wednesday 05/06/2015 09:52 23:15 ```formatting the [HR]:MM.The format should be [HH]:MM for Hours & Minuteschange my SLA calcsI have no idea what SLA means.MIKEhttp://www.skeptic.com/

Report •

#6
May 7, 2015 at 05:15:53
 MIke you are a genius no doubt how you come up with these formulas is so far beyond me, don't worry about SLA (Service Level Agreements) that's a calc off the back of the problem you are helping me with.I put everything back in and used the US format, literally copied your cells above custom format C1 to [HR]:MM and copied the formula and I got a result of 33:97, ggggrrrrrrrrrrrrrr .... this is really messing with my head.

Report •

#7
May 7, 2015 at 05:42:25
 OK, lets make sure everything is as it is supposed to be.If your system is set up to use the UK format dd/mm/yyyy then use that system, it should not matter, as Excel actually sees Dates asthe number of Days since Jan 1900First select your dates in cells A1 & B1,change the format to Numbers you should get: A1 = 42125.50 - ( 05/01/2015 12:07 )B1 = 42130.4111111111 - ( 05/06/2015 09:52 )Next check your Start & End Times in cell AA1 & AA2again, change the format to Numbers and you should get:AA1 = 0.38 ( 9:00 )AA2 = 0.73 ( 17:30 )If you have any Holidays, check cells AB1 thru AB5same as above.You keep using the Custom Format of [HR]:MM, is that the UK equivalent of [HH]:MM?MIKEhttp://www.skeptic.com/

Report •

#8
May 8, 2015 at 08:25:09
 HI MIke.Confirming that:A1 = 42125.50 - ( 05/01/2015 12:07 )B1 = 42160.41 - ( 05/06/2015 09:52 )AA1 = 0.38 ( 9:00 )AA2 = 0.73 ( 17:30 )No holidays entered as not required. Copied the formula as is above and the response has changed and is now 8.76042. Yep I now officially hate this work I'm doing, apologies I am using [HH]:MM as you had requested, the [HR] bit was a typo on my end.

Report •

#9
May 8, 2015 at 09:00:36
 Something is wrong: My cell B1 = 42130.4111111111 Your cell B1 = 42160.41My end date is May 6, 2015Yours is June 5, 2015Were getting confused with UK & USA date differences.Ain't Excel fun. :-)MIKEmessage edited by mmcconaghy

Report •

#10
May 13, 2015 at 07:03:23
 Sorry for the nil response, have been off for a few days .. Worked perfectly, may I say it again, you are an Excel Genius.Thank you so much Mike, really appreciated.

Report •