psycoperl
Active Member
- Joined
- Oct 23, 2007
- Messages
- 339
- Office Version
- 365
- 2016
- Platform
- Windows
- MacOS
- Web
I am working on a tracking spreadsheet to manage my time and leave. Since I get leave increments credited to my profile at the end of the month I was trying to figure out how to add one month to the date in the cell above. I saw the instructions on Microsoft's website suggesting the use of the EDATE function. However, it does not return the last day of the month. So if I go from August 31 it returns September 30 which is the correct value. However in the next instance it would return October 30 instead of October 31. It is even more drastic when going from February which only has 28 or 29 days, it now constantly returns either the 28 or 29th of the month.
What formula can I use to return the LAST day of each month?
What formula can I use to return the LAST day of each month?
Leave_Use_Plan_FY2021.xlsx | ||||
---|---|---|---|---|
AB | AC | |||
1 | SICK LEAVE | Leave Type | ||
2 | Monday, August 31, 2020 | SL | ||
3 | Wednesday, September 30, 2020 | SL | ||
4 | Friday, October 30, 2020 | SL | ||
5 | Monday, November 30, 2020 | SL | ||
6 | Wednesday, December 30, 2020 | SL | ||
7 | Saturday, January 30, 2021 | SL | ||
8 | Sunday, February 28, 2021 | SL | ||
9 | Sunday, March 28, 2021 | SL | ||
10 | Wednesday, April 28, 2021 | SL | ||
11 | Friday, May 28, 2021 | SL | ||
12 | Monday, June 28, 2021 | SL | ||
13 | Wednesday, July 28, 2021 | SL | ||
14 | Saturday, August 28, 2021 | SL | ||
15 | Tuesday, September 28, 2021 | SL | ||
16 | Thursday, October 28, 2021 | SL | ||
17 | Sunday, November 28, 2021 | SL | ||
18 | Tuesday, December 28, 2021 | SL | ||
19 | Friday, January 28, 2022 | SL | ||
20 | Monday, February 28, 2022 | SL | ||
Sheet1 |