Is there an Excel formula to round a date to the end of the month
 

User enters a specific date, I'd like to round that entry to the end of the
month

Answer: Is there an Excel formula to round a date to the end of the month
 

Yes, there is an Excel formula that can be used to round a date to the end of the month. The formula is called

Code:

---

EOMONTH

---

, which stands for "End Of Month". Here are the steps to use this formula:

1. Select the cell where you want to display the rounded date.
2. Type the formula "=
   Code:

   ---

   EOMONTH

   ---

   (" into the formula bar.
3. Inside the parentheses, type the cell reference or the date that you want to round to the end of the month. For example, if the date you want to round is in cell A1, you would type "=
   Code:

   ---

   EOMONTH(A1,0)

   ---

   .
4. The "0)" after the comma tells the formula to round to the end of the current month.
5. Press Enter.

The cell will now display the rounded date. For example, if the original date was January 15, 2021, the rounded date will be January 31, 2021. If the original date was February 28, 2021, the rounded date will be February 28, 2021 since it is already the end of the month.

Is there an Excel formula to round a date to the end of the month
 

With a date in A1:

=DATE(YEAR(A1),MONTH(A1)+1,0)

step into the next month and then back one day.
--
Gary''s Student - gsnu201003


"Siralec" wrote:

User enters a specific date, I'd like to round that entry to the end of the
month

Is there an Excel formula to round a date to the end of the month
 

Another way...

This requires the Analysis ToolPak add-in be installed for Excel versions
prior to Excel 2007.

A1 = some date

=EOMONTH(A1,0)

Format as Date

--
Biff
Microsoft Excel MVP


"Siralec" wrote in message
...
User enters a specific date, I'd like to round that entry to the end of
the
month

Is there an Excel formula to round a date to the end of the month
 

On Thu, 20 May 2010 14:57:01 -0700, Siralec
wrote:

User enters a specific date, I'd like to round that entry to the end of the
month

=A1-DAY(A1)+32-DAY(A1-DAY(A1)+32)

If it needs to be done in the SAME cell as the entry, you could use a VBA Event
triggered macro.
--ron