Microsoft KB Archive/45084: Difference between revisions

From BetaArchive Wiki
(importing KB archive)
 
m (X010 moved page Microsoft KB Archive/Q45084 to Microsoft KB Archive/45084 without leaving a redirect: Text replacement - "Microsoft KB Archive/Q" to "Microsoft KB Archive/")
 
(One intermediate revision by the same user not shown)
Line 11: Line 11:
=DATE(YEAR(cell_ref),MONTH(cell_ref),0)
=DATE(YEAR(cell_ref),MONTH(cell_ref),0)


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


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

Latest revision as of 18:23, 12 August 2020

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.