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?
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
- Moderator
- Posts: 32664
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
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.
=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.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
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
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
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.
What about 31st May? etc...
I'd stick with 30, 60, 180 days or similar.
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.
What happens if MONTH($A$1)>6?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.
Re: Function for irregular date progression
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.What happens if MONTH($A$1)>6
=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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Function for irregular date progression
I see, thanks Villeroy (and sorry, Hagar). I did something silly when I tried it before (I just got errors).Villeroy wrote: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.What happens if MONTH($A$1)>6
=DATE(2009;2;29) => 2009-03-01
=DATE(2009;-2;29) => 2008-10-29
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
- Moderator
- Posts: 32664
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
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.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- Hagar Delest
- Moderator
- Posts: 32664
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
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
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
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)
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice