Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate date difference exclude Wkend
Hello,
What formula to use to calculate number of days between two different dates excluding weekend and holiday? THanks Example: Start End #days 2/5/08 3/9/08 23 2/8/08 3/9/08 20 2/14/08 3/9/08 16 2/29/08 3/9/08 5 3/3/08 3/9/08 4 2/26/08 3/9/08 8 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate date difference exclude Wkend
You can use the following formula:
NETWORKDAYS(start_date,end_date,holidays) Look up NETWORKDAYS in Excel's help facility for all the details and suggestions, such as the recommended method for entering date, excluding holidays, etc... -- Kevin Backmann "Cam" wrote: Hello, What formula to use to calculate number of days between two different dates excluding weekend and holiday? THanks Example: Start End #days 2/5/08 3/9/08 23 2/8/08 3/9/08 20 2/14/08 3/9/08 16 2/29/08 3/9/08 5 3/3/08 3/9/08 4 2/26/08 3/9/08 8 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate date difference exclude Wkend
Try this:
If you have the Analysis ToolPak installed: C2: =NETWORKDAYS(A2,B2) Otherwise.... C2: =SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,A2):INDEX(A:A,B2)),2)<6)) Note: It appears that all of your date differences are off by 1. I get 24 weekdays from 05-FEB-2008 through 09-MAR-2008. If you do not want to count the start or end dates, just subtract 1 from the formulas. Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Cam" wrote in message ... Hello, What formula to use to calculate number of days between two different dates excluding weekend and holiday? THanks Example: Start End #days 2/5/08 3/9/08 23 2/8/08 3/9/08 20 2/14/08 3/9/08 16 2/29/08 3/9/08 5 3/3/08 3/9/08 4 2/26/08 3/9/08 8 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate date difference exclude Wkend
Look up the function NETWORKDAYS in Excel help.
-- David Biddulph "Cam" wrote in message ... Hello, What formula to use to calculate number of days between two different dates excluding weekend and holiday? THanks Example: Start End #days 2/5/08 3/9/08 23 2/8/08 3/9/08 20 2/14/08 3/9/08 16 2/29/08 3/9/08 5 3/3/08 3/9/08 4 2/26/08 3/9/08 8 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate date difference exclude Wkend
Try
=NETWORKDAYS(A1,B1) Mike "Cam" wrote: Hello, What formula to use to calculate number of days between two different dates excluding weekend and holiday? THanks Example: Start End #days 2/5/08 3/9/08 23 2/8/08 3/9/08 20 2/14/08 3/9/08 16 2/29/08 3/9/08 5 3/3/08 3/9/08 4 2/26/08 3/9/08 8 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Difference in Times with Considering Date | Excel Worksheet Functions | |||
formatt to Calculate difference in now() and date in cell, | Excel Discussion (Misc queries) | |||
Calculate Elapsed time: difference between 2 Date/Timestamp perio | Excel Worksheet Functions | |||
Calculate difference between 2 date and times with average | Excel Discussion (Misc queries) | |||
How do I calculate the difference between 2 dates (m,d,y) ? | Excel Worksheet Functions |