L
Linda
I am trying to get the length of time between two dates excluding weekends
and holidays in hours and minutes.
and holidays in hours and minutes.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
M
Mike H
Linda,
Is a weekday day 24 hours or a working day of (say 09:00 - 17:00)
Mike
Is a weekday day 24 hours or a working day of (say 09:00 - 17:00)
Mike
M
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
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
L
Linda
24 hours
--
Linda
--
Linda
Mike H said:Linda,
Is a weekday day 24 hours or a working day of (say 09:00 - 17:00)
Mike
L
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.
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.
Mike H said: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
Mike H said:Linda,
Is a weekday day 24 hours or a working day of (say 09:00 - 17:00)
Mike
P
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
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
Linda said: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.
Mike H said: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
Mike H said: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.