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 -