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

 

[shemayisroel]

Whats in AA2?

 

[Splith]

AA2 appears to be the 27th column reference. After Z it starts again with AA.

 

[shemayisroel]

AA2 appears to be the 27th column reference. After Z it starts again with AA.

What I meant was what value was in AA2...

 

[Peter_SSs]

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

If edate(AA2,12) does not produce a Monday, how do we determine which Monday you want? Would it always be the next Monday after edate(AA2,12)? or the previous Monday before edate(AA2,12)? or the nearest Monday to edate(AA2,12)? or something else?

 

[colinpax]

My apologies for the confusion.

Cell AA2 is where I have the previous year date and in cell AB2 is the formula
=if(AA2="","",edate(AA2,12)) which add next year date 9-Mar-10

Because it is a delivery run, the pick up is always a Monday for this client
and the date should be shown as the 8-March-10 which is a ("Monday")
not 9-March-10

all the date's goes into a merge letter and the Clients pay in advance yearly

Hope this is clear

thanks

 

[Peter_SSs]

My apologies for the confusion.

Cell AA2 is where I have the previous year date and in cell AB2 is the formula
=if(AA2="","",edate(AA2,12)) which add next year date 9-Mar-10

Because it is a delivery run, the pick up is always a Monday for this client
and the date should be shown as the 8-March-10 which is a ("Monday")
not 9-March-10

all the date's goes into a merge letter and the Clients pay in advance yearly

Hope this is clear

thanks

Try this. The second example covers a leap year so the adjustment is two days.

Excel Workbook
	AA	AB
1	Delivery Date	Next Delivery Date
2	Monday, 9 March 2009	Monday, 8 March 2010
3	Monday, 8 October 2007	Monday, 6 October 2008
Delivery Date

If the delivery day is not always a Monday, this more general solution might be suitable. Is the third example here OK?

Excel Workbook
	AA	AB
1	Delivery Date	Next Delivery Date
2	Wednesday, 11 March 2009	Wednesday, 10 March 2010
3	Friday, 12 October 2007	Friday, 10 October 2008
4	Wednesday, 1 April 2009	Wednesday, 31 March 2010
Delivery Date (2)

 

[matthew230]

AA2 appears to be the 27th column reference. After Z it starts again with AA.

Genius

 

[jasonb75]

The simplest solution is =AA2+364

The only time this will ever fail is if the dates span over Feb 29th

This will compensate for that when it happens, and also has its own IF to leave the cell blank if the source is also blank.

Use the code in a VBA module, with the formula =yearstart(AA2) in the required cell. Or enter the old date into the formula as =yearstart(" date ")

Function yearstart(olddate)
If olddate <> "" Then
ystart = DateValue(olddate) + 364
While Weekday(ystart) <> Weekday(olddate): ystart = ystart - 1: Wend
If Month(ystart) <> Month(olddate) Then ystart = ystart + 7
yearstart = ystart
Else: yearstart = ""
End If
End Function

Quick edit to add another line into the code, when the old year date is the 1st of the month, this will now move to the next monday (or weekday of the old date) instead of previous.

 

[barry houdini]

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