time difference
I am trying to get the length of time between two dates excluding weekends
and holidays in hours and minutes. -- Linda |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 01:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com