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

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

"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

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

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

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