[Solved] Formula to increment month of date field

Discuss the spreadsheet application

[Solved] Formula to increment month of date field

Postby daverpr » Thu Mar 12, 2009 6:54 pm

My problem is determining the formula for incrementing month only of a date field; i.e. 3/20/2009 to 4/20/2009 based on a condition =IF(). Any suggestions?
Last edited by daverpr on Fri Mar 13, 2009 2:45 am, edited 2 times in total.
OOo 3.0.X on Ms Windows XP
daverpr
 
Posts: 6
Joined: Thu Mar 12, 2009 6:36 pm

Re: Formula to increment month of date field

Postby acknak » Thu Mar 12, 2009 7:40 pm

I think the answer is "it depends". What is the result if you increment the month for the date 2009-03-31? It's not "2009-04-31", so I think you'll need to be a little more precise about what you want, and maybe explain what you want it for.
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17385
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Formula to increment month of date field

Postby daverpr » Thu Mar 12, 2009 8:02 pm

In developing a worksheet have a cell representing NEXT DUE DATE that is always the 20th day of the month. A1= NEXT DUE DATE. When Today()=NEXT DUE DATE, attempting to increment it one month and replace the NEXT DUE DATE with the incremented value. Was using the conditional format =IF(TODAY()=A1;???;A1). I've not been successful in obtaining the correct result. Hope this helps. New to OpenOffice so any advise is appreciated.
OOo 3.0.X on Ms Windows XP
daverpr
 
Posts: 6
Joined: Thu Mar 12, 2009 6:36 pm

Re: Formula to increment month of date field

Postby Villeroy » Thu Mar 12, 2009 8:18 pm

Create a calculated date-series with start value in A1 and constant day 20:
A2: =DATE(YEAR(A1);MONTH(A1)+1;20)
copy down A2

Get next 20th after A1:
=DATE(YEAR(A1);MONTH(A1)+(DAY(A1)>=20);20) [add 1 to the month if the day in A2 >=20]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17241
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formula to increment month of date field

Postby daverpr » Thu Mar 12, 2009 9:37 pm

I tried:
A1= DATE(2009;3;20)
A2=DATE(YEAR(A1);MONTH(A1)+1;20) Resulted error 501
A3=DATE(YEAR(A1);MONTH(A1)+(DAY(A1)>=20);20) Resulted error 501

Used fx DATE to create date series in A1.
Don't understand error results.
Any suggestions what I should try next?
OOo 3.0.X on Ms Windows XP
daverpr
 
Posts: 6
Joined: Thu Mar 12, 2009 6:36 pm

Re: Formula to increment month of date field

Postby TheGurkha » Thu Mar 12, 2009 11:39 pm

Using Villeroy's formula, I made this example sheet.

Month Increment Test Sheet.ods
(9.67 KiB) Downloaded 790 times
Ubuntu 13.04 Raring ringtail, LibO 4.0.2.2 (Build ID 400m0 (Build:2))
Gurkha Welfare Trust
User avatar
TheGurkha
Moderator
 
Posts: 6426
Joined: Thu Mar 13, 2008 12:13 pm
Location: North Wales, UK.

Re: Formula to increment month of date field

Postby daverpr » Fri Mar 13, 2009 2:34 am

Thank you Villeroy & Gurhka. Reviewing your test sheet set off a lightbulb-I could easily see what Villeroy meant by copy down A2. Thank you both for helping me understand.
I created my own test sheet & got the same results.
OOo 3.0.X on Ms Windows XP
daverpr
 
Posts: 6
Joined: Thu Mar 12, 2009 6:36 pm

Re: [Solved] Formula to increment month of date field

Postby Interpotential » Thu Oct 01, 2009 7:41 pm

A much easier way is the EDATE function which you can feed a date and a number of months after that to return a new date on the same day of the month, just in a different month
OpenOffice 3.01 on Ubuntu 9.04
Interpotential
 
Posts: 1
Joined: Thu Oct 01, 2009 7:38 pm

Re: [Solved] Formula to increment month of date field

Postby Villeroy » Thu Oct 01, 2009 8:09 pm

Interpotential wrote:A much easier way is the EDATE function which you can feed a date and a number of months after that to return a new date on the same day of the month, just in a different month

Yes, EDATE is a little bit easier, but not compatible with Excel. It's not part of the commonly used set of functions. Both, Excel and Calc have EDATE in their "analysis tools".
DATE(y;m;d) is more flexible since it works as "universal date calculator" with any combination of positive or negative numbers for years, months or days. For instance DATE(YEAR(A1);MONTH(A1);0) returns the last day of the preceeding month and it's fairly easy to combine DATE with WEEKDAY to get the first/last Monday...Sunday of some month.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17241
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Formula to increment month of date field

Postby acknak » Thu Oct 01, 2009 8:25 pm

Villeroy wrote:... it's fairly easy to combine DATE with WEEKDAY to get the first/last Monday...Sunday of some month.

I'm feeling especially lazy today; how does that work again?
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17385
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Solved] Formula to increment month of date field

Postby Villeroy » Thu Oct 01, 2009 8:40 pm

acknak wrote:
Villeroy wrote:... it's fairly easy to combine DATE with WEEKDAY to get the first/last Monday...Sunday of some month.

I'm feeling especially lazy today; how does that work again?

It works with mod(weekday;7) and the start of a month.
Any first weekday for any month of any year:
=DATE(myYear;myMonth;1)+MOD(myWeekDay-WEEKDAY(DATE(myYear;myMonth;1));7)
myWeekDay=1 -> Sunday
myWeekDay=7 -> Saturday

Last weekday:
=DATE(myYear;myMonth+1;0)-MOD(WEEKDAY(DATE(myYear;myMonth+1;0))-myWeekDay;7)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17241
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Formula to increment month of date field

Postby acknak » Thu Oct 01, 2009 9:11 pm

Of course :oops:

I've even used that myself and completely forgotten about it until I saw your example.

Thanks!
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17385
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3


Return to Calc

Who is online

Users browsing this forum: No registered users and 23 guests