Creating Timeline -- returning a weekday

**degross77** · 08-05-2010, 04:30 PM

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))

**DonkeyOte** · 08-05-2010, 04:34 PM

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

**degross77** · 08-05-2010, 04:41 PM

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!

**DonkeyOte** · 08-05-2010, 04:43 PM

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.