date intervals calculation
 

I want to set out a table below which works in all circumstances. I am using
Excel 2003:

Effective date ?

Date Interval Date Days
OVERNIGHT ? ?
1 WEEK ? ?
1 MONTH ? ?
3 MONTHS
6 MONTHS
12 MONTHS

for instance for 5/ feb 2008

Date Interval Date Days
OVERNIGHT 6/2/08 1
1 WEEK 13/2/08 7
1 MONTH 5/3/08 29
3 MONTHS 7/4/08 62
6 MONTHS 5/8/08 90
12 MONTHS 5/2/09 366

So as you can see it should take into account leap years AND must be on the
next week day if the date falls at a weekend eg 7/4/08

date intervals calculation
 

Why is your 1 WEEK entry showing 13/02/2008 - shouldn't it be 12th
Feb? Ans shouldn't 3 months hence be in May? Do the days represent
elapsed days or working days? Not sure where 90 comes from.

Would you like to re-submit your example?

Pete

On Feb 5, 3:33*pm, martyn wrote:
I want to set out a table below which works in all circumstances. I am using
Excel 2003:

Effective date * *?

Date Interval * Date * *Days
OVERNIGHT * * * *? * * * * * * * ? * * *
1 WEEK * * * * *? * * * * * * * *?
1 MONTH * * * * ? * * * * * * * *?
3 MONTHS * * * * * * * *
6 MONTHS * * * * * * * *
12 MONTHS

for instance for 5/ feb 2008

Date Interval * Date * *Days
OVERNIGHT * * * 6/2/08 *1
1 WEEK * * * * *13/2/08 * * 7
1 MONTH * * * * 5/3/08 * * *29
3 MONTHS * * * * * * * *7/4/08 * * *62
6 MONTHS * * * * * * * *5/8/08 * * *90
12 MONTHS * * * 5/2/09 * *366

So as you can see it should take into account leap years AND must be on the
next week day * if the date falls at a weekend eg 7/4/08

date intervals calculation
 

"Pete_UK" wrote:

Why is your 1 WEEK entry showing 13/02/2008 - shouldn't it be 12th
Feb? Ans shouldn't 3 months hence be in May? Do the days represent
elapsed days or working days? Not sure where 90 comes from.

Would you like to re-submit your example?

Pete

On Feb 5, 3:33 pm, martyn wrote:
I want to set out a table below which works in all circumstances. I am using
Excel 2003:

Effective date ?

Date Interval Date Days
OVERNIGHT ? ?
1 WEEK ? ?
1 MONTH ? ?
3 MONTHS
6 MONTHS
12 MONTHS

for instance for 5/ feb 2008

Date Interval Date Days
OVERNIGHT 6/2/08 1
1 WEEK 13/2/08 7
1 MONTH 5/3/08 29
3 MONTHS 7/4/08 62
6 MONTHS 5/8/08 90
12 MONTHS 5/2/09 366

So as you can see it should take into account leap years AND must be on the
next week day if the date falls at a weekend eg 7/4/08


Sorry the above should have read :

OVERNIGHT 6/2/08 1
1 WEEK 12/2/08 7
1 MONTH 5/3/08 29
2 MONTHS 7/4/08 62
3 MONTHS 5/8/08 90
12 MONTHS 5/2/09 366

date intervals calculation
 

I'm sure I have over-complicated this, but here goes:

Put your reference date (eg 5/02/08) in B1, and your labels in A3 to
A9 and B3:C3 as per your original example, and then put these formula
in the cells stated:

B4: =DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1)+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1 ),2)-5),3)

B5: =DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7)+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7 ),2)-5),3)

B6: =DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1) ),2)-5),3)

B7: =DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1) ),2)-5),3)

B8: =DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1) ),2)-5),3)

B9: =DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1) ),2)-5),3)


Format these cells as dates in the style you like, then format cell C4
as a number with 0dp and put this formula in:

=B4-B$1

and copy this down into C5:C9.

Just change the date in B1 for the table to re-calculate.

Hope this helps.

Pete


On Feb 5, 4:23*pm, martyn wrote:

Sorry the above should have read :



OVERNIGHT * * * 6/2/08 *1
1 WEEK * * * * *12/2/08 * * 7
1 MONTH * * * * 5/3/08 * * *29
2 MONTHS * * * * * * * *7/4/08 * * *62
3 MONTHS * * * * * * * *5/8/08 * * *90
12 MONTHS * * * 5/2/09 * *366

date intervals calculation
 

yes this works great, have you done this one before? Have checked dats and
works for leap years, If there is a simpler formula I dont care as long as
this works

thanks

"Pete_UK" wrote:

I'm sure I have over-complicated this, but here goes:

Put your reference date (eg 5/02/08) in B1, and your labels in A3 to
A9 and B3:C3 as per your original example, and then put these formula
in the cells stated:

B4: =DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1)+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1 ),2)-5),3)

B5: =DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7)+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7 ),2)-5),3)

B6: =DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1) ),2)-5),3)

B7: =DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1) ),2)-5),3)

B8: =DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1) ),2)-5),3)

B9: =DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1) ),2)-5),3)


Format these cells as dates in the style you like, then format cell C4
as a number with 0dp and put this formula in:

=B4-B$1

and copy this down into C5:C9.

Just change the date in B1 for the table to re-calculate.

Hope this helps.

Pete


On Feb 5, 4:23 pm, martyn wrote:

Sorry the above should have read :



OVERNIGHT 6/2/08 1
1 WEEK 12/2/08 7
1 MONTH 5/3/08 29
2 MONTHS 7/4/08 62
3 MONTHS 5/8/08 90
12 MONTHS 5/2/09 366

date intervals calculation
 

Glad to hear that, Martyn - thanks for feeding back.

The differences are quite marked if you use 31st Jan 2008 and 1st Feb
2008 as the reference dates in B1.

Pete

On Feb 6, 8:57*am, martyn wrote:
yes *this works great, have you done this one before? Have checked dats and
works for leap years, If there is a simpler formula I dont care as long as
this works

thanks



"Pete_UK" wrote:
I'm sure I have over-complicated this, but here goes:

Put your reference date (eg 5/02/08) in B1, and your labels in A3 to
A9 and B3:C3 as per your original example, and then put these formula
in the cells stated:

B4: *=DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1)+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1 ),2)-5),3)

B5: *=DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7)+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7 ),2)-5),3)

B6: *=DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1) ),2)-5),3)

B7: *=DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1) ),2)-5),3)

B8: *=DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1) ),2)-5),3)

B9: *=DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1) ),2)-5),3)

Format these cells as dates in the style you like, then format cell C4
as a number with 0dp and put this formula in:

=B4-B$1

and copy this down into C5:C9.

Just change the date in B1 for the table to re-calculate.

Hope this helps.

Pete

On Feb 5, 4:23 pm, martyn wrote:

Sorry the above should have read :

OVERNIGHT * * * 6/2/08 *1
1 WEEK * * * * *12/2/08 * * 7
1 MONTH * * * * 5/3/08 * * *29
2 MONTHS * * * * * * * *7/4/08 * * *62
3 MONTHS * * * * * * * *5/8/08 * * *90
12 MONTHS * * * 5/2/09 * *366

date intervals calculation
 

ah, i didnt check that. Mmm oh dear this must work on every date as using
this in a banking application. Is it just 1st of feb in a leap year. Is there
a solution?

"Pete_UK" wrote:

Glad to hear that, Martyn - thanks for feeding back.

The differences are quite marked if you use 31st Jan 2008 and 1st Feb
2008 as the reference dates in B1.

Pete

On Feb 6, 8:57 am, martyn wrote:
yes this works great, have you done this one before? Have checked dats and
works for leap years, If there is a simpler formula I dont care as long as
this works

thanks



"Pete_UK" wrote:
I'm sure I have over-complicated this, but here goes:

Put your reference date (eg 5/02/08) in B1, and your labels in A3 to
A9 and B3:C3 as per your original example, and then put these formula
in the cells stated:

B4: =DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1)+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1 ),2)-5),3)

B5: =DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7)+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7 ),2)-5),3)

B6: =DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1) ),2)-5),3)

B7: =DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1) ),2)-5),3)

B8: =DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1) ),2)-5),3)

B9: =DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1) ),2)-5),3)

Format these cells as dates in the style you like, then format cell C4
as a number with 0dp and put this formula in:

=B4-B$1

and copy this down into C5:C9.

Just change the date in B1 for the table to re-calculate.

Hope this helps.

Pete

On Feb 5, 4:23 pm, martyn wrote:

Sorry the above should have read :

OVERNIGHT 6/2/08 1
1 WEEK 12/2/08 7
1 MONTH 5/3/08 29
2 MONTHS 7/4/08 62
3 MONTHS 5/8/08 90
12 MONTHS 5/2/09 366

date intervals calculation
 

As I understood your request, you wanted to add on the appropriate
days/months from the reference date and then adjust to the next
working day if the projected date was a weekend. So, these are the
values I get with 1st Feb 2008 as the reference date:

Date Interval Date Days Day
OVERNIGHT 4-Feb-08 3 Mon
1 WEEK 8-Feb-08 7 Fri
1 MONTH 3-Mar-08 31 Mon
2 MONTHS 1-Apr-08 60 Tue
3 MONTHS 1-May-08 90 Thu
12 MONTHS 2-Feb-09 367 Mon

As 1st Feb 2008 is a Friday, then the next day would be a Saturday, so
this becomes Monday three days hence. One week's advance would be a
Friday, whereas one month's advance would be a Saturday, so this
becomes the next Monday. Similarly, two and three months in advance
are both workdays and so need no adjustment, but one year hence would
be a Sunday, so this moves to the next Monday.

I thought this is what you wanted, but please advise if it is not.

Pete




On Feb 6, 10:16*am, martyn wrote:
ah, i didnt check that. Mmm oh dear this must work on every date as using
this in a banking application. Is it just 1st of feb in a leap year. Is there
a solution?



"Pete_UK" wrote:
Glad to hear that, Martyn - thanks for feeding back.

The differences are quite marked if you use 31st Jan 2008 and 1st Feb
2008 as the reference dates in B1.

Pete

On Feb 6, 8:57 am, martyn wrote:
yes *this works great, have you done this one before? Have checked dats and
works for leap years, If there is a simpler formula I dont care as long as
this works

thanks

"Pete_UK" wrote:
I'm sure I have over-complicated this, but here goes:

Put your reference date (eg 5/02/08) in B1, and your labels in A3 to
A9 and B3:C3 as per your original example, and then put these formula
in the cells stated:

B4: *=DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1)+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1 ),2)-5),3)

B5: *=DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7)+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7 ),2)-5),3)

B6: *=DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1) ),2)-5),3)

B7: *=DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1) ),2)-5),3)

B8: *=DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1) ),2)-5),3)

B9: *=DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1) ),2)-5),3)

Format these cells as dates in the style you like, then format cell C4
as a number with 0dp and put this formula in:

=B4-B$1

and copy this down into C5:C9.

Just change the date in B1 for the table to re-calculate.

Hope this helps.

Pete

On Feb 5, 4:23 pm, martyn wrote:

Sorry the above should have read :

OVERNIGHT * * * 6/2/08 *1
1 WEEK * * * * *12/2/08 * * 7
1 MONTH * * * * 5/3/08 * * *29
2 MONTHS * * * * * * * *7/4/08 * * *62
3 MONTHS * * * * * * * *5/8/08 * * *90
12 MONTHS * * * 5/2/09 * *366- Hide quoted text -

- Show quoted text -

date intervals calculation
 

Yes this is perfect thanks, just exactly what I wanted. I misunderstood your
last comment regarding the large differences. I thought you meant you thought
there maybe an error on that date and didnt test it myself, but having
reveiewed it I understand your orignal comments and agree with the solution.
Many thanks

Martyn

"Pete_UK" wrote:

As I understood your request, you wanted to add on the appropriate
days/months from the reference date and then adjust to the next
working day if the projected date was a weekend. So, these are the
values I get with 1st Feb 2008 as the reference date:

Date Interval Date Days Day
OVERNIGHT 4-Feb-08 3 Mon
1 WEEK 8-Feb-08 7 Fri
1 MONTH 3-Mar-08 31 Mon
2 MONTHS 1-Apr-08 60 Tue
3 MONTHS 1-May-08 90 Thu
12 MONTHS 2-Feb-09 367 Mon

As 1st Feb 2008 is a Friday, then the next day would be a Saturday, so
this becomes Monday three days hence. One week's advance would be a
Friday, whereas one month's advance would be a Saturday, so this
becomes the next Monday. Similarly, two and three months in advance
are both workdays and so need no adjustment, but one year hence would
be a Sunday, so this moves to the next Monday.

I thought this is what you wanted, but please advise if it is not.

Pete




On Feb 6, 10:16 am, martyn wrote:
ah, i didnt check that. Mmm oh dear this must work on every date as using
this in a banking application. Is it just 1st of feb in a leap year. Is there
a solution?



"Pete_UK" wrote:
Glad to hear that, Martyn - thanks for feeding back.

The differences are quite marked if you use 31st Jan 2008 and 1st Feb
2008 as the reference dates in B1.

Pete

On Feb 6, 8:57 am, martyn wrote:
yes this works great, have you done this one before? Have checked dats and
works for leap years, If there is a simpler formula I dont care as long as
this works

thanks

"Pete_UK" wrote:
I'm sure I have over-complicated this, but here goes:

Put your reference date (eg 5/02/08) in B1, and your labels in A3 to
A9 and B3:C3 as per your original example, and then put these formula
in the cells stated:

B4: =DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1)+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1 ),2)-5),3)

B5: =DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7)+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7 ),2)-5),3)

B6: =DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1) ),2)-5),3)

B7: =DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1) ),2)-5),3)

B8: =DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1) ),2)-5),3)

B9: =DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1) ),2)-5),3)

Format these cells as dates in the style you like, then format cell C4
as a number with 0dp and put this formula in:

=B4-B$1

and copy this down into C5:C9.

Just change the date in B1 for the table to re-calculate.

Hope this helps.

Pete

On Feb 5, 4:23 pm, martyn wrote:

Sorry the above should have read :

OVERNIGHT 6/2/08 1
1 WEEK 12/2/08 7
1 MONTH 5/3/08 29
2 MONTHS 7/4/08 62
3 MONTHS 5/8/08 90
12 MONTHS 5/2/09 366- Hide quoted text -

- Show quoted text -

date intervals calculation
 

Yes, it does do what you wanted - I was just pointing out the
consequences of that particular date, being a Friday and in a Leap
Year.

Pete

On Feb 6, 12:45*pm, martyn wrote:
Yes this is perfect thanks, just exactly what I wanted. I misunderstood your
last comment regarding the large differences. I thought you meant you thought
there maybe an error on that date and didnt test it myself, but having
reveiewed it I understand your orignal comments and agree with the solution. *
Many thanks

Martyn



"Pete_UK" wrote:
As I understood your request, you wanted to add on the appropriate
days/months from the reference date and then adjust to the next
working day if the projected date was a weekend. So, these are the
values I get with 1st Feb 2008 as the reference date:

Date Interval * * Date * * * * * * *Days * * *Day
OVERNIGHT * * 4-Feb-08 * * * * *3 * * * * Mon
1 WEEK * * * * * 8-Feb-08 * * * * *7 * * * * Fri
1 MONTH * * * * *3-Mar-08 * * * *31 * * * * Mon
2 MONTHS * * * *1-Apr-08 * * * * 60 * * * *Tue
3 MONTHS * * * *1-May-08 * * * *90 * * * *Thu
12 MONTHS * * *2-Feb-09 * * * 367 * * * *Mon

As 1st Feb 2008 is a Friday, then the next day would be a Saturday, so
this becomes Monday three days hence. One week's advance would be a
Friday, whereas one month's advance would be a Saturday, so this
becomes the next Monday. *Similarly, two and three months in advance
are both workdays and so need no adjustment, but one year hence would
be a Sunday, so this moves to the next Monday.

I thought this is what you wanted, but please advise if it is not.

Pete

On Feb 6, 10:16 am, martyn wrote:
ah, i didnt check that. Mmm oh dear this must work on every date as using
this in a banking application. Is it just 1st of feb in a leap year. Is there
a solution?

"Pete_UK" wrote:
Glad to hear that, Martyn - thanks for feeding back.

The differences are quite marked if you use 31st Jan 2008 and 1st Feb
2008 as the reference dates in B1.

Pete

On Feb 6, 8:57 am, martyn wrote:
yes *this works great, have you done this one before? Have checked dats and
works for leap years, If there is a simpler formula I dont care as long as
this works

thanks

"Pete_UK" wrote:
I'm sure I have over-complicated this, but here goes:

Put your reference date (eg 5/02/08) in B1, and your labels in A3 to
A9 and B3:C3 as per your original example, and then put these formula
in the cells stated:

B4: *=DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1)+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1 ),2)-5),3)

B5: *=DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7)+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7 ),2)-5),3)

B6: *=DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1) ),2)-5),3)

B7: *=DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1) ),2)-5),3)

B8: *=DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1) ),2)-5),3)

B9: *=DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1))+MOD(3-
MAX(0,WEEKDAY(DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1) ),2)-5),3)

Format these cells as dates in the style you like, then format cell C4
as a number with 0dp and put this formula in:

=B4-B$1

and copy this down into C5:C9.

Just change the date in B1 for the table to re-calculate.

Hope this helps.

Pete

On Feb 5, 4:23 pm, martyn wrote:

Sorry the above should have read :

OVERNIGHT * * * 6/2/08 *1
1 WEEK * * * * *12/2/08 * * 7
1 MONTH * * * * 5/3/08 * * *29
2 MONTHS * * * * * * * *7/4/08 * * *62
3 MONTHS * * * * * * * *5/8/08 * * *90
12 MONTHS * * * 5/2/09 * *366- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -