Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula Needed-Business Days
I need a formula that calculates business days, but the NETWORKDAYS function
is not adequate. I need the formula to return the number of days between two dates, excluding a range of holidays. Since my store is open seven days a week, the NETWORKDAYS function will not suffice. When I used Quattro Pro in the past, it had a BDAY function, and it accepted parameters to indicate if weedends were to be included or not. Is there a comparable function in Excel? I am trying to calculate the number of days the store is open every week. I thought of using NETWORKDAYS and simply adding 2 (Saturday and Sunday). However, when holidays occur on a weekend, my formula returns the incorrect number of days. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula Needed-Business Days
So you only need to subtract holidays from your dates?
Put the holiday dates in let's say H1:H10 (adapt if there are more or less holidays than 10) =A2-A1-(SUMPRODUCT(--(H1:H10=A1),--(H1:H10<=A2))) with start date in A1 and end date in A2 depending on how you count you might want to include one day, if so =((A2-A1)+1)-(SUMPRODUCT(--(H1:H10=A1),--(H1:H10<=A2))) -- Regards, Peo Sjoblom (No private emails please) "MauiTim" wrote in message ... I need a formula that calculates business days, but the NETWORKDAYS function is not adequate. I need the formula to return the number of days between two dates, excluding a range of holidays. Since my store is open seven days a week, the NETWORKDAYS function will not suffice. When I used Quattro Pro in the past, it had a BDAY function, and it accepted parameters to indicate if weedends were to be included or not. Is there a comparable function in Excel? I am trying to calculate the number of days the store is open every week. I thought of using NETWORKDAYS and simply adding 2 (Saturday and Sunday). However, when holidays occur on a weekend, my formula returns the incorrect number of days. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula Needed-Business Days
Hi!
A1 = start date B1 = end date C1:C10 = list of holiday dates =B1-A1-SUMPRODUCT(--(C1:C10=A1),--(C1:C10<=B1))+1 Biff "MauiTim" wrote in message ... I need a formula that calculates business days, but the NETWORKDAYS function is not adequate. I need the formula to return the number of days between two dates, excluding a range of holidays. Since my store is open seven days a week, the NETWORKDAYS function will not suffice. When I used Quattro Pro in the past, it had a BDAY function, and it accepted parameters to indicate if weedends were to be included or not. Is there a comparable function in Excel? I am trying to calculate the number of days the store is open every week. I thought of using NETWORKDAYS and simply adding 2 (Saturday and Sunday). However, when holidays occur on a weekend, my formula returns the incorrect number of days. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding XY days to date in cells | Excel Discussion (Misc queries) | |||
need help with formula | Excel Discussion (Misc queries) | |||
Please help!! Vacation Accrual Formula | Excel Worksheet Functions | |||
Vacation Accrual Formula | Excel Worksheet Functions | |||
formula for filtering and a defaulting date | Excel Worksheet Functions |