time difference

[Linda]

I am trying to get the length of time between two dates excluding weekends
and holidays in hours and minutes.

 

[Mike H]

Linda,

Is a weekday day 24 hours or a working day of (say 09:00 - 17:00)

Mike

 

[Mike H]

Never mind, here's both solutions


Change your working day to suit.
=(NETWORKDAYS(A1,B1,Holidays)-1)*("17:00"-"08:00")+MOD(B1,1)-MOD(A1,1)

If a weekday day is 24 hours
=(NETWORKDAYS(A1,B1,Holidays)-1)+MOD(B1,1)-MOD(A1,1)

In both cases 'Holidays' is a named range that contains your holiday dates.
Format both as [hh]:mm

Mike

 

[Linda]

24 hours
--
Linda

Linda,

Is a weekday day 24 hours or a working day of (say 09:00 - 17:00)

Mike

 

[Linda]

Date In Date Out Time Counting

Counting all
Days

10/10/08 16:30 10/14/08 12:00 91:30:00
10/13/2008 16:30 10/16/2008 14:30 70:00:00
10/14/2008 15:00 10/16/2008 13:00 46:00:00
--
Linda
This is an example of what I am doing. When I use your formula, I get a
value error. Any idea what I am doing wrong? Thanks.

Never mind, here's both solutions


Change your working day to suit.
=(NETWORKDAYS(A1,B1,Holidays)-1)*("17:00"-"08:00")+MOD(B1,1)-MOD(A1,1)

If a weekday day is 24 hours
=(NETWORKDAYS(A1,B1,Holidays)-1)+MOD(B1,1)-MOD(A1,1)

In both cases 'Holidays' is a named range that contains your holiday dates.
Format both as [hh]:mm

Mike

Linda,

Is a weekday day 24 hours or a working day of (say 09:00 - 17:00)

Mike

 

[Peo Sjoblom]

Nothing wrong with his formula, if you get value error then you must have
text in one or the other cell

Using Mike's formula on your data I get

43:30:00
70:00:00
46:00:00

Your first number is wrong, 91:30:00 are the total number of hours not
excluding the weekend



--


Regards,


Peo Sjoblom

Date In Date Out Time Counting

Counting
all
Days

10/10/08 16:30 10/14/08 12:00 91:30:00
10/13/2008 16:30 10/16/2008 14:30 70:00:00
10/14/2008 15:00 10/16/2008 13:00 46:00:00
--
Linda
This is an example of what I am doing. When I use your formula, I get a
value error. Any idea what I am doing wrong? Thanks.

Never mind, here's both solutions


Change your working day to suit.
=(NETWORKDAYS(A1,B1,Holidays)-1)*("17:00"-"08:00")+MOD(B1,1)-MOD(A1,1)

If a weekday day is 24 hours
=(NETWORKDAYS(A1,B1,Holidays)-1)+MOD(B1,1)-MOD(A1,1)

In both cases 'Holidays' is a named range that contains your holiday
dates.
Format both as [hh]:mm

Mike

Linda,

Is a weekday day 24 hours or a working day of (say 09:00 - 17:00)

Mike

:

I am trying to get the length of time between two dates excluding
weekends
and holidays in hours and minutes.