#### colinpax

##### Board Regular
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

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Whats in AA2?

#### Splith

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

#### shemayisroel

##### Well-known Member
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

##### MrExcel MVP, Moderator
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

##### Board Regular
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

##### MrExcel MVP, Moderator
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
AAAB
1Delivery DateNext Delivery Date
2Monday, 9 March 2009Monday, 8 March 2010
3Monday, 8 October 2007Monday, 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
AAAB
1Delivery DateNext Delivery Date
2Wednesday, 11 March 2009Wednesday, 10 March 2010
3Friday, 12 October 2007Friday, 10 October 2008
4Wednesday, 1 April 2009Wednesday, 31 March 2010
Delivery Date (2)

#### matthew230

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

Genius

#### jasonb75

##### Well-known Member
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 ")

Code:
``````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.

Last edited:

#### barry houdini

##### MrExcel MVP
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......

Replies
2
Views
175
Replies
7
Views
466
Replies
13
Views
466
Replies
1
Views
114
Replies
7
Views
358

1,191,074
Messages
5,984,480
Members
439,891
Latest member
maikii

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back