## Function for irregular date progression

### Function for irregular date progression

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?
Monetix

Posts: 3
Joined: Tue Mar 25, 2008 5:04 pm

### Re: Function for irregular date progression

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.
LO 6.4.6 on Xubuntu 20.04 and Windows 10.

Hagar Delest
Moderator

Posts: 29066
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

### Re: Function for irregular date progression

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28825
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Function for irregular date progression

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.

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

Hagar de l'Est wrote: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?
huw
Volunteer

Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

### Re: Function for irregular date progression

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28825
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Function for irregular date progression

Villeroy wrote:
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   Expand viewCollapse view
`Date   +1 month28/01/2007   28/02/200729/01/2007   01/03/200730/01/2007   02/03/200731/01/2007   03/03/200701/02/2007   01/03/200702/02/2007   02/03/200703/02/2007   03/03/200704/02/2007   04/03/2007`
huw
Volunteer

Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

### Re: Function for irregular date progression

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.
LO 6.4.6 on Xubuntu 20.04 and Windows 10.

Hagar Delest
Moderator

Posts: 29066
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

### Re: Function for irregular date progression

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]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28825
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Function for irregular date progression

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?

Monetix wrote: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
LO 6.4.6 on Xubuntu 20.04 and Windows 10.

Hagar Delest
Moderator

Posts: 29066
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

### Re: Function for irregular date progression

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)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28825
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany