Calendar Days formula...
Hello,
I'm struggleing with a formula. I have a formula for adding workdays so I tried to use that to add calendar days and it's not working.
Here's what I typed:
=if(g3="","",(g3,21))
I want the result to come back with adding 21 days to the date entered in G3. However, if G3 is blank, then I would like the cell to remain blank.
Also, is there a formula that I could use were if the date lands on a holiday, could the result be the day prior?
Any assistance would be appreciated
So to be clear: you're saying you want to use all 7 days in your calculation but with the exception of Public Holidays ?
Which Excel version are you running ?
Can you post your existing WORKDAY formula ?
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I'm running Excel 2003
The workday formula that I have is =if(g3="","",workday(g3,21))
Hi,
Your formula is fine, since Workday() function will automatically skip week-ends ...
HTH=IF(G3,WORKDAY(G3,21),"")
that will work but, I will need to include weekends, but I'm not sure how to input the forumula to do so.
Dawn V, perhaps best to illustrate with examples.
I'm assuming from your post that your intention is to use all days with exception of public holidays - ergo WORKDAY is not valid.
However, it's not clear what should happen if public holidays occur between G3 and G3+21 -- should the holidays simply be ignored or should we treat the 21 as 21 non public holiday days ?
I'm guessing we should simply be ignoring any such dates given you state that where G3+21 lands on a Public Holiday we should simply skip back to last non-public holiday date prior to that date.
Do you have a range configured which holds the public holiday dates ?
Thank you for your assistance! I really appreciate it.
Yes, this would be what I am looking for.... I'm guessing we should simply be ignoring any such dates given you state that where G3+21 lands on a Public Holiday we should simply skip back to last non-public holiday date prior to that date.
No, I do not have the date range. If it is easier, I could just take an example of adding the calendar days.
Adding Calendar days is as simple as simple can be - this is all stems from the fact that dates in Excel are simply integers and as such can be added / subtracted etc as per any other number - it's simply that we can format them to appear as dates.
=IF(G3="","",G3+21)
would give you your calendar calculation ignoring issues of public holidays.
Adjusting for public holidays is more convoluted given they could be consecutive ... if we assume the holiday dates are listed in H1:H10 then
=IF(G3="","",G3+MAX(IF(ISNA(MATCH(G3+ROW(A$1:INDEX(A:A,21)),$H$1:$H$10,0)),ROW(A$1:INDEX(A:A,21)))))
confirmed with CTRL + SHIFT + ENTER
would return for you the last date up to and including G3+21 which was not a public holiday.
If weekends were being ignored we'd simply use WORKDAY and add our optional holiday range.
Thank you very much! :o)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks