Microsoft KB Archive/45084

How to Find the Last Day in the Previous Month with Excel PSS ID Number: Q45084 Article last modified on 02-26-1993 PSS database name: W_eXceL

2.x 3.00

WINDOWS

Summary:

Given a date, the last day in the previous month can be calculated in Microsoft Excel by using one of two formulas. The two formulas are the following:

=DATE(YEAR(cell_ref),MONTH(cell_ref),0)

=DATEVALUE(TEXT(MONTH(cell_ref),“##”)&“/1/”& TEXT(YEAR(cell_ref),“####”))-1

where “cell_ref” represents the cell that contains the date.

More Information:

Example

 * 1) Enter “November 5, 1991” (without quotation marks) into cell A1 in a worksheet.
 * 2) Enter one of the two formulas from above into cell A2.
 * 3) From the Format menu, choose Number to change the serial number 33542 into a suitable date format.

The result will be the date of the last day of October, 1991, which is “October 31, 1991.”

Reference(s):

“Microsoft Excel Function Reference,” version 3.0, pages 44-46, 156, 234-235, 251-252

Additional reference words: 2.01 2.10 2.21 2.20 3.00 3.0 2.2 2.1d 2.1c 2.1 time formula calculate calculation

Copyright Microsoft Corporation 1993.