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!

Look at this thread and see if it offers any help: http://www.computing.net/answers/of...

See my last reply # 11

It's a bit complicated, so any questions please ask.MIKE

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

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,Formatcell C1 asCustom,[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))))))/24

It is a very long formula, so you might want to just cut & paste.

See how it works for you.

MIKE

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?

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 & Minutes

change mySLAcalcsI have no idea what SLA means.

MIKE

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.

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 as

the number of Days since Jan 1900First select your dates in cells A1 & B1,

change the format toNumbersyou 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 & AA2

again, change the format toNumbersand you should get:

AA1 = 0.38 ( 9:00 )

AA2 = 0.73 ( 17:30 )If you have any Holidays, check cells AB1 thru AB5

same as above.You keep using the Custom Format of [HR]:MM,

is that the UK equivalent of [HH]:MM?MIKE

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.

Something is wrong: My cell B1 = 421

30.4111111111

Your cell B1 = 42160.41My end date is May 6, 2015

Yours is June 5, 2015Were getting confused with UK & USA date differences.

Ain't Excel fun. :-)MIKE

message edited by mmcconaghy

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.

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History