Function for irregular date progression

[Monetix]

Hi,
I need to have a function in calc that does the following: Take a date an give the corresponding dates in 1 day, 1 week, 2 weeks, 1 month, 2 month, 6 month

For example:
1th January --> 2nd January, 8th January, 1th February, 1th March, 1th July

Is there any way I can do this with a function or would I need to record a macro or something like that?

[Hagar Delest]

I guess the results have to be in different cells. In that case, you should use the DATE() function with the YEAR(), MONTH() and DAY() functions. The formulas would look like that (assuming that initial date is in A1):
=DATE(YEAR($A$1);MONTH($A$1)+6;DAY($A$1)) for the date + 6 months.

NB: I'm using the French version at work, I hope it's similar for the English.

Thanks to add '[Solved]' in your first post title (edit button) if your issue has been fixed.

[Villeroy]

A1:A7
2008-01-01
2008-01-02
2008-01-08
2008-02-01
2008-03-01
2008-07-01
2009-01-01
A8: =DATE(YEAR(A1)+1;MONTH(A1);DAY(A1)))
copy down A8

[huw]

1 day, 2 days, 2 weeks are easy - just add 1, 2, 7 14 respectively.

But... what date is one month after 31st January? Is it 28th February? (or 29th in a leap year?), or is it 3rd March? (or 2nd in a leap year?)

And then what is a month after 3rd February? Is it also 3rd March? That means either a month after 1st February (1st March) would come before a month after 31st January (3rd March), or you have 28th-31st January all mapping to 28th February.

What about 31st May? etc...

I'd stick with 30, 60, 180 days or similar.

I guess the results have to be in different cells. In that case, you should use the DATE() function with the YEAR(), MONTH() and DAY() functions. The formulas would look like that (assuming that initial date is in A1):
=DATE(YEAR($A$1);MONTH($A$1)+6;DAY($A$1)) for the date + 6 months.

What happens if MONTH($A$1)>6?

[Villeroy]

What happens if MONTH($A$1)>6

Just give a try. DATE can calculate all kinds of date offsets from start of Gregorian Calendar (1582-10-15) until year 9956 by using all kind of offset values, including negative ones.
=DATE(2009;2;29) => 2009-03-01
=DATE(2009;-2;29) => 2008-10-29

[huw]

What happens if MONTH($A$1)>6

Just give a try. DATE can calculate all kinds of date offsets from start of Gregorian Calendar (1582-10-15) until year 9956 by using all kind of offset values, including negative ones.
=DATE(2009;2;29) => 2009-03-01
=DATE(2009;-2;29) => 2008-10-29

I see, thanks Villeroy (and sorry, Hagar). I did something silly when I tried it before (I just got errors).

But still, I don't see the resulting dates (back-tracking & repetition) as meaningful:

Code: Select all

Date	+1 month
28/01/2007	28/02/2007
29/01/2007	01/03/2007
30/01/2007	02/03/2007
31/01/2007	03/03/2007
01/02/2007	01/03/2007
02/02/2007	02/03/2007
03/02/2007	03/03/2007
04/02/2007	04/03/2007

[Hagar Delest]

That's the corner case of each end of month (with less than 31 days). It comes from the fact that dates are based on numbers. So that's a limitation of the tool. If it's not enough for the OP needs, then, the formula needs to be more complicated to introduce alternatives and handle these situations.

[Villeroy]

My solution starts with 7 constant values, assuming that the following dates are (7 cells up)+1 year. This won't change the days and months, unless there is a leap day in the start sequence: 2008-02-29 -> 2009-03-01 -> [remains 1st March]

[Hagar Delest]

Hi Villeroy, you focused on the Year+1 but the OP needs more than this one (and does he really need the year+1 one ?). Or am I missing something?

Take a date an give the corresponding dates in 1 day, 1 week, 2 weeks, 1 month, 2 month, 6 month

For example:
1th January --> 2nd January, 8th January, 1th February, 1th March, 1th July

[Villeroy]

Another proposal for any irregular sequence:
A1: 2008-01-01 (start date)
B1: Days (label)
C1: Months (label)
D1: Years (label)

Take the date from previous row and add the time units as specified in this row:
A2 =DATE(YEAR($A1)+$D2;MONTH($A1)+$C2;DAY($A1)+$B2)

Take the date in A1 and add the time units as specified in this row:
A2 =DATE(YEAR($A$1)+$D2;MONTH($A$1)+$C2;DAY($A$1)+$B2)