date intervals calculation

[martyn]

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]

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

 

[martyn]

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

Sorry the above should have read :

 

[Pete_UK]

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

 

[martyn]

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]

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

 

[martyn]

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]

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

 

[martyn]

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]

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