| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
time interval between dates incl weekends excl holidays
Hi,
Can anyone help me on the calculation of the time elipsed between start date/time and end date/time, inculding weekend days and excluding holidays. I have tried using NETWORKDAYS; but that can't handle the weekend days. Tried using WORKDAY; but there I cannot subtract the two dates. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time interval between dates incl weekends excl holidays
Hi,
Go to ToolAdd-Ins and checkAnalysis ToolPak and then use NETWORKDAYS . Assuming your start date is in A1 end date is in B1 the formula is =NETWORKDAYS(A1,B1) Tim "douwe" wrote: Hi, Can anyone help me on the calculation of the time elipsed between start date/time and end date/time, inculding weekend days and excluding holidays. I have tried using NETWORKDAYS; but that can't handle the weekend days. Tried using WORKDAY; but there I cannot subtract the two dates. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time interval between dates incl weekends excl holidays
I'm sure someones going to come up with something much more elegant but try:-
=((A2-A1)*24)-(COUNTIF(C1:C8,"<"&A2)*24)-(COUNTIF(C1:C8,"<"&A1)*24) Start date and time in A1 End date and time in A2 Holiday list in C1 to C8 (allows 24 hrs for each holiday) Format formula as general Mike "douwe" wrote: Hi, Can anyone help me on the calculation of the time elipsed between start date/time and end date/time, inculding weekend days and excluding holidays. I have tried using NETWORKDAYS; but that can't handle the weekend days. Tried using WORKDAY; but there I cannot subtract the two dates. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time interval between dates incl weekends excl holidays
More detailed example. Assuming in cell A3 is 8-Aug-07 and in cell A2 is
9-Aug-07 this formula =NETWORKDAYS(A3,A2) will give you the total of 2 days. Format the cell with the formula as a General Number. Regards, Tim "Tim" wrote: Hi, Go to ToolAdd-Ins and checkAnalysis ToolPak and then use NETWORKDAYS . Assuming your start date is in A1 end date is in B1 the formula is =NETWORKDAYS(A1,B1) Tim "douwe" wrote: Hi, Can anyone help me on the calculation of the time elipsed between start date/time and end date/time, inculding weekend days and excluding holidays. I have tried using NETWORKDAYS; but that can't handle the weekend days. Tried using WORKDAY; but there I cannot subtract the two dates. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time interval between dates incl weekends excl holidays
Now Ill go to watch the movie on the TV. Tomorrow will check if my answer
was helpful. Best wishes, Tim "Tim" wrote: More detailed example. Assuming in cell A3 is 8-Aug-07 and in cell A2 is 9-Aug-07 this formula =NETWORKDAYS(A3,A2) will give you the total of 2 days. Format the cell with the formula as a General Number. Regards, Tim "Tim" wrote: Hi, Go to ToolAdd-Ins and checkAnalysis ToolPak and then use NETWORKDAYS . Assuming your start date is in A1 end date is in B1 the formula is =NETWORKDAYS(A1,B1) Tim "douwe" wrote: Hi, Can anyone help me on the calculation of the time elipsed between start date/time and end date/time, inculding weekend days and excluding holidays. I have tried using NETWORKDAYS; but that can't handle the weekend days. Tried using WORKDAY; but there I cannot subtract the two dates. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time interval between dates incl weekends excl holidays
Hi Tim,
Thanks for your reply; however Networkdays wil not work with weekend days. D. "Tim" wrote: More detailed example. Assuming in cell A3 is 8-Aug-07 and in cell A2 is 9-Aug-07 this formula =NETWORKDAYS(A3,A2) will give you the total of 2 days. Format the cell with the formula as a General Number. Regards, Tim "Tim" wrote: Hi, Go to ToolAdd-Ins and checkAnalysis ToolPak and then use NETWORKDAYS . Assuming your start date is in A1 end date is in B1 the formula is =NETWORKDAYS(A1,B1) Tim "douwe" wrote: Hi, Can anyone help me on the calculation of the time elipsed between start date/time and end date/time, inculding weekend days and excluding holidays. I have tried using NETWORKDAYS; but that can't handle the weekend days. Tried using WORKDAY; but there I cannot subtract the two dates. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time interval between dates incl weekends excl holidays
formula was missing a set of brackets
=((A2-A1)*24)-((COUNTIF(C1:C8,"<"&A2)*24)-(COUNTIF(C1:C8,"<"&A1)*24)) "Mike H" wrote: I'm sure someones going to come up with something much more elegant but try:- =((A2-A1)*24)-(COUNTIF(C1:C8,"<"&A2)*24)-(COUNTIF(C1:C8,"<"&A1)*24) Start date and time in A1 End date and time in A2 Holiday list in C1 to C8 (allows 24 hrs for each holiday) Format formula as general Mike "douwe" wrote: Hi, Can anyone help me on the calculation of the time elipsed between start date/time and end date/time, inculding weekend days and excluding holidays. I have tried using NETWORKDAYS; but that can't handle the weekend days. Tried using WORKDAY; but there I cannot subtract the two dates. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
time interval between dates incl weekends excl holidays
Hi Mike,
This wil work just fine; pure and simple. Thanks! D. "Mike H" wrote: formula was missing a set of brackets =((A2-A1)*24)-((COUNTIF(C1:C8,"<"&A2)*24)-(COUNTIF(C1:C8,"<"&A1)*24)) "Mike H" wrote: I'm sure someones going to come up with something much more elegant but try:- =((A2-A1)*24)-(COUNTIF(C1:C8,"<"&A2)*24)-(COUNTIF(C1:C8,"<"&A1)*24) Start date and time in A1 End date and time in A2 Holiday list in C1 to C8 (allows 24 hrs for each holiday) Format formula as general Mike "douwe" wrote: Hi, Can anyone help me on the calculation of the time elipsed between start date/time and end date/time, inculding weekend days and excluding holidays. I have tried using NETWORKDAYS; but that can't handle the weekend days. Tried using WORKDAY; but there I cannot subtract the two dates. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date/Time Difference Excluding Weekends & Holidays | Excel Worksheet Functions | |||
| Difference between 2 dates, incl weekends, with variable work days | Excel Discussion (Misc queries) | |||
| Difference between 2 dates, incl weekends, with variable work days | Excel Worksheet Functions | |||
| Formula - Excluding weekends & holidays | Excel Worksheet Functions | |||
| How to exclude weekends/holidays from plotted time series | Charts and Charting in Excel | |||
Linear Mode
