Rounding dates in Excel 2000

[christal]

I am in need of some help in rounding dates. For example: Cell A1 has 02/25/03 and cell B1 has 90 (days). Cell C1 should be 06/01/03 because 90 days added to 02/25/03 is 05/26/03, yet it rounds to the nearest month (06/01/03). Am I making sense??? Help! )

Thanks Christal

[Aladin Akyurek]

On 2002-04-08 07:14, christal wrote:
I am in need of some help in rounding dates. For example: Cell A1 has 02/25/03 and cell B1 has 90 (days). Cell C1 should be 06/01/03 because 90 days added to 02/25/03 is 05/26/03, yet it rounds to the nearest month (06/01/03). Am I making sense??? Help! [img]/board/images/smiles/icon_eek.gif[/img])

Thanks Christal

If A1 houses 02/25/03 (a date formatted entry) and B1 90,

=A1+B1

gives 05/26/03 (26-May-03) in C1.

[Mark W.]

=EOMONTH(A1+B1,IF(DAY(A1+B1)>=15,0,-1))+1

Note: The EMONTH worksheet function is supplied by the Analysis ToolPak add-in.

[ This Message was edited by: Mark W. on 2002-04-08 07:31 ]

[christal]

Can you tell me what to use in the tool pak in order for this calculation to work??

Thanks!

[Mark W.]

On 2002-04-08 08:15, christal wrote:
Can you tell me what to use in the tool pak in order for this calculation to work??

Thanks!

See the Excel Help topic for "Install and use the Analysis ToolPak".

[Peo08]

On 2002-04-08 07:14, christal wrote:
I am in need of some help in rounding dates. For example: Cell A1 has 02/25/03 and cell B1 has 90 (days). Cell C1 should be 06/01/03 because 90 days added to 02/25/03 is 05/26/03, yet it rounds to the nearest month (06/01/03). Am I making sense??? Help! )

Thanks Christal

Your question is somewhat unclear, do you want to "round" to the nearest month or not?
You cannot really round dates the way you round other numbers..

Here's a non ATP answer that will return the same as the answer you received from Mark

=DATE(YEAR(A1+B1),MONTH(A1+B1)+IF(DAY(A1+B1)>=15,1,0),1)

otherwise see Aladin's answer, if you add 90 days to 02/25/03 the result is 05/26/03, not 06/01/03..


Regards,

Peo Sjoblom