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
- Enter “November 5, 1991” (without quotation marks) into cell A1 in a worksheet.
- Enter one of the two formulas from above into cell A2.
- 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.
