Adding year plus Day

[colinpax]

Hi
I have a formula =if(AA2="","",edate(AA2,12)) which is working well for the Year

This is a delivery run thats requires that the pick up will aways be on a Monday.
I would like the exact day for the following years.

e.g.
Monday 2009 is the 9th March
Monday 2010 is the 8th March
Monday 2011 is the 8th March
Monday 2012 is the 6th March
Monday 2013 is the 4th March

I would appreciate it if it is possible or a alternative way of doing it.

Thanks Colin

 

[jasonb75]

Good point Barry, I was having a brain malfunction at the time and missed that one.

This would mean that the While - Wend loop in my function could be removed, that was only included to compensate for the leap year issue.

As for the date moving forwards, that has been taken care of with the custom function that I offered, by adding an extra week if the original calculation brings the new date forward to the end of the preceeding month.

Jason, adding 364 days will always give you a Monday if the original date is a Monday, leap years won't change that.

The only question then would be whether its acceptable for the date to get continually earlier in the year or whether it would need to be within a specific month or other timeframe......

 

[colinpax]

Hi Peter

Thanks for your effort. Very much appreciated.
I tested out the formula below on Monday-Tuesday-Wenesday Sheets and it works very well, Leap year fell on a Wednesday 29th 2013 (PERFECT)

=IF(AA2="","",edate(AA2,12)-1-(WEEKDAY(edate(AA2,12)-1)<>WEEKDAY(AA2)))
I also would like to thank Jason,Barry,& Mathew for their Input and prompt replies

Colin

 

[tusharm]

2013 is *not* a leap year.

If your pickup is every Nth weekday (N=1 means every week), all you need to do is keep adding 7*N to any known pickup date.

Hi Peter

Thanks for your effort. Very much appreciated.
I tested out the formula below on Monday-Tuesday-Wenesday Sheets and it works very well, Leap year fell on a Wednesday 29th 2013 (PERFECT)

=IF(AA2="","",edate(AA2,12)-1-(WEEKDAY(edate(AA2,12)-1)<>WEEKDAY(AA2)))
I also would like to thank Jason,Barry,& Mathew for their Input and prompt replies

Colin

 

[barry houdini]

I agree with Tushar. As you appear to be adding 52 weeks to the date each time why not just add 52*7 = 364 as Jason, suggested?, i.e.

=IF(AA2="","",AA2+364)