Calculating working hours

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I'm hoping someone can help me with the following problem.

I need to create a formula which calculates the different between two
date/time values excluding out of business hours.

Here's an example.
A1: 21/06/2005 10:00:00
A2: 23/06/2005 14:30:00

When using "=(B1-A1)*24" I can the result 52.5
If the business hours are between 08:30 and 17:00 the actual result required
is 21.5

I hope this makes sense.

Thanks in advance,

Mo..
 
Hi Bob,

Thanks for this, it worked a treat, I've justed realised I'll need to
exclude weekends, how would I implement this using networkdays?

Thanks in advance,

Mo..
 
=(NETWORKDAYS(A1,A2)-1)*8.5+(MOD(A2,1)-MOD(A1,1))*24

or

=(NETWORKDAYS(A1,A2, holidays)-1)*8.5+(MOD(A2,1)-MOD(A1,1))*24


if you want to exclude holidays
 
Back
Top