Function for irregular date progression

Discuss the spreadsheet application

Function for irregular date progression

Postby Monetix » Wed Mar 26, 2008 12:43 pm

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

Postby Hagar Delest » Wed Mar 26, 2008 1:03 pm

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.
User avatar
Hagar Delest
Moderator
 
Posts: 29066
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Function for irregular date progression

Postby Villeroy » Wed Mar 26, 2008 1:07 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 28825
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Function for irregular date progression

Postby huw » Wed Mar 26, 2008 1:49 pm

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.



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

Postby Villeroy » Wed Mar 26, 2008 2:11 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 28825
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Function for irregular date progression

Postby huw » Wed Mar 26, 2008 3:11 pm

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 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
huw
Volunteer
 
Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

Re: Function for irregular date progression

Postby Hagar Delest » Wed Mar 26, 2008 3:29 pm

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.
User avatar
Hagar Delest
Moderator
 
Posts: 29066
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Function for irregular date progression

Postby Villeroy » Wed Mar 26, 2008 3:42 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 28825
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Function for irregular date progression

Postby Hagar Delest » Wed Mar 26, 2008 3:58 pm

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.
User avatar
Hagar Delest
Moderator
 
Posts: 29066
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Function for irregular date progression

Postby Villeroy » Wed Mar 26, 2008 4:25 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 28825
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 18 guests