Date/time increment specifier

[adimar]

Date + 1 increments the date.
How do you increment date at year, month, hour, min, or second?

Ex: Is there anything more compact for “Date + 1 year†than DATE(YEAR(Date)
+ 1, MONTH(Date), DAY(Date))?

Thank you.

 

[Fred Smith]

No.

Regards,
Fred,

 

[Tyro]

To increment or decrement the number of months in date, you can use the
EDATE function. This requires the Analysis Toolpak to be installed in
versions of Excel prior to 2007. So, if A1 has the date Jan 15, 2008 and
you use =EDATE(A1,1) you'll get Feb 15, 2008. If you use =EDATE(A1,-12),
you'll get Jan 15, 2007.
In the case of leap year, if the date in A1 is Feb 29, 2008 then
=EDATE(A1,12) produces Feb 28, 2009.
As for times, you can increment the time by 1 hour by using =A1+1/24. To
increment by 1 minute, =A1+1/(24*60). To increment by 1 second,
=A1+1/(24*60*60). But if you increment the time this way, you'll have to
adjust the days if time goes beyond 24 hours.
There is a lot of date/time info at www.cpearson.com

Tyro

 

[adimar]

Thank you

No.

Regards,
Fred,

 

[Tyro]

Correction:
You may have to adjust the date if time goes beyond 24 hours; it may for the
most part correctly adjust the date when you do the add.
I personally think it's best to play with times by using Excel functions.

Tyro