difference of date/time only calculating workingdays/hours

[Susanne]

Can you please help me. I need to calculate the difference between two dates
which also contain the timestamp but I would like it to only count the normal
working time - 8 hours per day and not weekends

 

[Mike H]

Susanne,

Try this. It assumes you working day is 08:00 - 16:00 hrs so change to suit.
Format
the cell as [hh]:mm

Holidays is a named range that contains holiday dates. If you don't want
this simply delete ',holidays' form the formula

=(NETWORKDAYS(A1,B1,Holidays)-1)*("16:00"-"08:00")+MOD(B1,1)-MOD(A1,1)

Mike

 

[Bob Phillips]

What if A1 or B1 is a weekend date, or a holiday?

Or the time is outside of those working hours?

--
__________________________________
HTH

Bob

Susanne,

Try this. It assumes you working day is 08:00 - 16:00 hrs so change to
suit.
Format
the cell as [hh]:mm

Holidays is a named range that contains holiday dates. If you don't want
this simply delete ',holidays' form the formula

=(NETWORKDAYS(A1,B1,Holidays)-1)*("16:00"-"08:00")+MOD(B1,1)-MOD(A1,1)

Mike

Can you please help me. I need to calculate the difference between two
dates
which also contain the timestamp but I would like it to only count the
normal
working time - 8 hours per day and not weekends

 

[Mike H]

Bob,

I see your pont about the times, I assumed that the OP's times would be
within the working day and perhaps I should have pointed that out. I can't
see the issue with A1 or B1 being a weekend date

03/01/2009 08:00 05/01/2009 12:00

The formula evaluates as 4 for these 2 date/time for examples. What am I
missing?

Mike

What if A1 or B1 is a weekend date, or a holiday?

Or the time is outside of those working hours?

--
__________________________________
HTH

Bob

Susanne,

Try this. It assumes you working day is 08:00 - 16:00 hrs so change to
suit.
Format
the cell as [hh]:mm

Holidays is a named range that contains holiday dates. If you don't want
this simply delete ',holidays' form the formula

=(NETWORKDAYS(A1,B1,Holidays)-1)*("16:00"-"08:00")+MOD(B1,1)-MOD(A1,1)

Mike

Can you please help me. I need to calculate the difference between two
dates
which also contain the timestamp but I would like it to only count the
normal
working time - 8 hours per day and not weekends

 

[Mike H]

Bob,

I'll answer my own question about what I'm missing. I'm missing the fact
that there are too many instances when my previous effort falls over.
Incidentally I've posted that lots of times and nobody pointed out the
problem. Is this one better? To keep it shorter the start/End times are
referenced in C1 - C2

=(NETWORKDAYS(A1,B1)-1)*(C2-C1)+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),C2,C2),C2)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),C2,C1)

Thanks for the feedback and here's hoping for this one.

Mike

Bob,

I see your pont about the times, I assumed that the OP's times would be
within the working day and perhaps I should have pointed that out. I can't
see the issue with A1 or B1 being a weekend date

03/01/2009 08:00 05/01/2009 12:00

The formula evaluates as 4 for these 2 date/time for examples. What am I
missing?

Mike

What if A1 or B1 is a weekend date, or a holiday?

Or the time is outside of those working hours?

--
__________________________________
HTH

Bob

Susanne,

Try this. It assumes you working day is 08:00 - 16:00 hrs so change to
suit.
Format
the cell as [hh]:mm

Holidays is a named range that contains holiday dates. If you don't want
this simply delete ',holidays' form the formula

=(NETWORKDAYS(A1,B1,Holidays)-1)*("16:00"-"08:00")+MOD(B1,1)-MOD(A1,1)

Mike


:

Can you please help me. I need to calculate the difference between two
dates
which also contain the timestamp but I would like it to only count the
normal
working time - 8 hours per day and not weekends