1. I want to calculate the date that is 30 months from a date in my
spreadsheet (format: dd/mm/yy). How to do?

2. I want to calculate the date and time that is 1000 hours from a
date in my spreadsheet (format: dd/mm/yy hh:ss). How to do?

Wim

for part one depends on how to recieve results

what if the date would be feb 30th?
what result would you want?

"Wim" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 1. I want to calculate the date that is 30 months from a date in my
> spreadsheet (format: dd/mm/yy). How to do?
>
> 2. I want to calculate the date and time that is 1000 hours from a
> date in my spreadsheet (format: dd/mm/yy hh:ss). How to do?
>
> Wim
>

#1. 10 months or 30 months?

=date(year(a1),month(a1)+10,day(a1))

You may have trouble with end of the months dates like MDBJ wrote.

#2. =a1+(1000/24)
format as date/time


Wim wrote:
>
> 1. I want to calculate the date that is 30 months from a date in my
> spreadsheet (format: dd/mm/yy). How to do?
>
> 2. I want to calculate the date and time that is 1000 hours from a
> date in my spreadsheet (format: dd/mm/yy hh:ss). How to do?
>
> Wim

--

Dave Peterson

You could also use the EDATE function.

=EDATE(A1,30)

If A1 was either 8/29/2005,8/30/2005 or 8/31/2005 then it would return
2/29/2008. It would not step up into the next month until your date
was 9/1/2005. I think that solves the issue of the end date being
returned as 2/30.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=522618

I use this function a lot, but one extra note about it;
you have to activate the Analysis ToolPak under
Tools/Add-Ins to use the function. (At least in
version 2002 and before.)
kcc

"SteveG" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> You could also use the EDATE function.
>
> =EDATE(A1,30)
>
> If A1 was either 8/29/2005,8/30/2005 or 8/31/2005 then it would return
> 2/29/2008. It would not step up into the next month until your date
> was 9/1/2005. I think that solves the issue of the end date being
> returned as 2/30.
>
> HTH
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile:
> http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=522618
>

The "=date(year(a1),month(a1)+10,day(a1))" and "=a1+(1000/24)" formulas
from Dave did the trick for me. Thanks a lot to all of you.