Page 1 of 1

Add weeks to a date

PostPosted: Mon Apr 14, 2008 2:45 pm
by richjsmith
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

Re: Add weeks to a date

PostPosted: Mon Apr 14, 2008 2:53 pm
by Hagar Delest
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?

Re: Add weeks to a date

PostPosted: Mon Apr 14, 2008 6:05 pm
by acknak
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 ;-)

Re: Add weeks to a date

PostPosted: Mon Apr 14, 2008 8:47 pm
by Hagar Delest
But the problem is to have "14/04/08 + 52 which should equal 14/04/09". That's a "custom" calculation.

Re: Add weeks to a date

PostPosted: Mon Apr 14, 2008 9:41 pm
by acknak
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.

Re: Add weeks to a date

PostPosted: Mon Apr 14, 2008 9:54 pm
by Villeroy
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.

Re: Add weeks to a date

PostPosted: Mon Apr 14, 2008 10:04 pm
by Hagar Delest
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.