Trying to figure out if I have a formula for a timeline - ie a date minus days like 8/17/10 - 14, which returns 8/3 -- I want to use an IF function or something of the sort if it returns a Saturday or a Sunday, to add one or two days to it so that it comes out on a Monday.
Here is what I attempted using Weekday and IF Function. But returning a NUM ERROR.
** Cell C15 contains my original Date
** I tried to say here that if it returns a 1 or 7 (Saturday and Sunday) then add days respectively.
=IF(WEEKDAY(C15-14,7),(C15-14)+2, IF(WEEKDAY(C15-14,1),(C15-14)+1,C15-14))
Using "standard" functions
=C15-14+LOOKUP(WEEKDAY(C15-14,2),{1,6,7},{0,2,1})
Using Analysis Toolpak WORKDAY function
=WORKDAY(C15-15,1)
to utilise the above pre XL2007 you must activate the aforementioned Add-In via Tools -> Addins
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks -- standard works well, just trying to figure out how to "read it"... I like to translate the formula so I know for next time!
C15-14 -- Lookup the weekday in the format where 1 = Sunday and 7 = Saturday. .... then I am a little lost on this {1,6,7},{0,2,1})
Thanks again!
WEEKDAY with return_type of 2 has Mon as Day 1 and Sun as Day 7.
LOOKUP(day,{1,6,7},{0,2,1})
searches the lookup_vector for the day and finds the last value <= day
given a result_vector (optional) is specified instead of returning 1,6 or 7 from the lookup_vector the LOOKUP returns the associated value of the match from the result_vector - ie the 0,2 or 1
the result is then added to the original calculation to adjust the date to a Monday
If you need to account for public holidays then I would suggest using WORKDAY as it has an optional holiday range parameter.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks