Microsoft KB Archive/45084

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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



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:


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

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

More Information:


  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.”


“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.