Excel 2010 Formula to subtract one date and time from another (within certain times!)

Hi guys!

I'm looking for a solution (which I guess will be found by means of a complex formula) to substract column I in the attached spreadsheet from column H. This will give me a ‘time taken’ total. I need the total for each row
to show in hours and minutes only. But importantly if for example it is more than 1 day, it needs to read as e.g. 25hours, 20 minutes.

Importantly, the ‘clock’ has to only tick during office hours i.e. 8 am til 4pm. Therefore the ‘time taken’ totals need to be based on 8am til 4pm mon to fri only, and must exclude evenings and weekends.


I then need the function to determine whether this is more or less than a permitted standard of 4 hours. If it less than 4 hours I need it to return a value of Yes in column K. If not, then value should be No in column K.

Any assistance here much appreciated!!!

Many thanks
Justin

Hi justin8260

If I have followed you correctly! Try in J2 and copy down. The cells in the formula Q1=16:00 & P1=08:00.

Formula:

Please Login or Register  to view this content.

And format the cells as "[h]:mm" (without the quotes).

Then in K2 and copy down.

Formula:

Please Login or Register  to view this content.

See the attached file.