Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time difference
I am trying to get the length of time between two dates excluding weekends
and holidays in hours and minutes. -- Linda |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time difference
Linda,
Is a weekday day 24 hours or a working day of (say 09:00 - 17:00) Mike "Linda" wrote: I am trying to get the length of time between two dates excluding weekends and holidays in hours and minutes. -- Linda |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time difference
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" wrote: Linda, Is a weekday day 24 hours or a working day of (say 09:00 - 17:00) Mike "Linda" wrote: I am trying to get the length of time between two dates excluding weekends and holidays in hours and minutes. -- Linda |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time difference
24 hours
-- Linda "Mike H" wrote: Linda, Is a weekday day 24 hours or a working day of (say 09:00 - 17:00) Mike "Linda" wrote: I am trying to get the length of time between two dates excluding weekends and holidays in hours and minutes. -- Linda |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time difference
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" wrote: 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" wrote: Linda, Is a weekday day 24 hours or a working day of (say 09:00 - 17:00) Mike "Linda" wrote: I am trying to get the length of time between two dates excluding weekends and holidays in hours and minutes. -- Linda |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time difference
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 "Linda" wrote in message ... 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" wrote: 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" wrote: Linda, Is a weekday day 24 hours or a working day of (say 09:00 - 17:00) Mike "Linda" wrote: I am trying to get the length of time between two dates excluding weekends and holidays in hours and minutes. -- Linda |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time formula (difference of predicted and actual time) | Excel Discussion (Misc queries) | |||
find the difference between start time and end time when spanning. | Excel Discussion (Misc queries) | |||
subtract the time difference from another time difference | Excel Discussion (Misc queries) | |||
Time difference calculations, daylight savings time, Excel | Excel Discussion (Misc queries) | |||
Negative time should be allowed in Excel, eg time difference | Excel Discussion (Misc queries) |