Date time difference

[JMB]

Have tried all of the formulas relating to time date diff but cannot get the
weekend hours out of the equation eg
C3 = 14-Mar-2008 07:30
C4 = 17-Mar-2008 07:30
C5 = should equal 8
How or what determains the hrs which are output. I have tried the formular
using netwokdays & workdays.

Thanks JMB.

 

[Bob Phillips]

Why should it be 8. When does the day start and end?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JMB]

As I am ultimatly trying to get a cost for labour and the day starts @ 07:30
and finishes @ 16:00

JMB

 

[Bob Phillips]

I make that 8.5. So where does the break kick in? It al matters for the
solution.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JMB]

Bob,
OK, yes it's 8.5 Hrs...The break starts @ 16:00 on a Friday afternoon
and ends on Mondays @ 07:30. The idea is that we have supplimentry labour on
site and need to calculate the number of hours X payrate to get a total cost
for a given time period.

JMB

 

[Bob Phillips]

Try this

=(NETWORKDAYS(A1,A2)-(WEEKDAY(A1,2)<6)-(WEEKDAY(A2,2)<6))*8.5+
(IF(WEEKDAY(A1,2)<6,MIN(MAX((TIME(16,0,0)-MOD(A1,1))*24,0),8.5),0))+
(IF(WEEKDAY(A2,2)<6,MIN(MAX((MOD(A2,1)-TIME(7,30,0))*24,0),8.5),0))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)