Add weeks to a date

Discuss the spreadsheet application

Add weeks to a date

Postby richjsmith » Mon Apr 14, 2008 2:45 pm

Hello,
I am trying to add a certain number of weeks to a date
eg. 14/04/08 + 52 which should equal 14/04/09
however I am unsure on how to do this, I am trying some weeknum_add to get the week number and then im adding that to the amount of weeks but how do I then take that value and turn it back into a date?

Thanks
Richard
richjsmith
 
Posts: 5
Joined: Mon Apr 14, 2008 2:42 pm

Re: Add weeks to a date

Postby Hagar Delest » Mon Apr 14, 2008 2:53 pm

There are functions to handle week number but then you'll have to recreate the date and I don' really now how you can do that (you need to have a number for the day).

There is a similar thread but not dealing with weeks: Function for irregular date progression.

Can't you use values like 7xn for n weeks with special values for full months and years? Can you give more details or attach here a file sample showing what's the final purpose?
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: Add weeks to a date

Postby acknak » Mon Apr 14, 2008 6:05 pm

Calc stores all dates internally as just a number of days from a particular starting date, so you can always simply add a number of days to a particular date. To find the date one week ahead, just add 7 to the date. To jump 52 weeks ahead, just add 52*7.

That calculation usually fails for other intervals, but the common calendar always preserves weeks exactly: there's never a week that doesn't have seven days*, whereas months and years have a variable number of days. In other words, if you add any number of weeks to today, it will always be a Monday.

* Except for the week when the calendar was changed ;-)
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Add weeks to a date

Postby Hagar Delest » Mon Apr 14, 2008 8:47 pm

But the problem is to have "14/04/08 + 52 which should equal 14/04/09". That's a "custom" calculation.
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: Add weeks to a date

Postby acknak » Mon Apr 14, 2008 9:41 pm

OIC.

Well, maybe the poster can tell us more about what form the input date is. In any case, I think you're right: the approach is to simply add a number of 7-day intervals to the date.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Add weeks to a date

Postby Villeroy » Mon Apr 14, 2008 9:54 pm

14/04/08 + 52 which should equal 14/04/09

52 weeks from today is
Code: Select all   Expand viewCollapse view
=TODAY()+52*7

returning 39916 (days since day zero 1899-12-30), which is equivalent to 13/04/08 when you format this number as date.
If you want to add/substract days, months or years to a given date (day number) then you should use DATE(#year;#month;#day). Each of the 3 numeric arguments can be positive or negative integer or even zero.
For instance
=DATE(2008;3;0) returns the last day of February 2008 (zeroth of March)
=DATE(YEAR(A1)+1;MONTH(A1);DAY(A1)) adds one year to the date (day number) in A1.
=DATE(YEAR(A1);MONTH(A1)+1;0) returns the last day of the month from the date (day number) in A1.
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: Add weeks to a date

Postby Hagar Delest » Mon Apr 14, 2008 10:04 pm

Right (that's what the other thread says). But OP wants to do it based on a week basis. So we need his explanation for his final purpose.
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


Return to Calc

Who is online

Users browsing this forum: No registered users and 15 guests