Dates play a crucial role in Excel, whether you’re tracking deadlines or planning schedules. In this video, I’ll show you five tips that will make working with dates simple and efficient. You’ll learn…
- How to calculate the first and last date of the current month or a month that is X months from a given date
- How to find a date that is X months from a given date
- How to easily display day and month names
Calculate Last Day of a Month
The EOMONTH function returns the date that is the last day of a month a certain number of months away from a given date. Does that make sense? Here’s an example…
Today (9th February 2025) I took out a loan to buy a car. I need to repay the loan over 18 payments. The payment is taken from my bank account on the last day of each month, starting this month, and I’d like to know what date the first payment and the last payments are taken.
B1 contains 9th February 2025. To calculate the date of the first payment, into B2 I enter: =EOMONTH(B1,0)
This puts into B2 the date that is the last day of February 2025. The second argument, 0 in this case, specifies that you want the last day of the current month based on the date in B1.
To calculate the date of the last payment, into B3 I enter: =EOMONTH(B1,17)
This will give me the date that is the last day of the month 17 months after the date in B1. It’s not 18 because I had already made a payment at the end of the first month.
Calculate First Day of a Month
You can also use EOMONTH to calculate the first day of the month a given number of months from a date.
This time the first payment will be on the first day of the month following the loan agreement date, so in this example, 1st March 2025.
The formula in B2 will be =EOMONTH(B1,0)+1
This calculates the last day of the month that is in B1 and adds 1 to it
The 1 represents the number of days to be added
To calculate the date of the last payment, into B3 I enter:
=EOMONTH(B1,17)+1
This will give me the date that is one day after the last day of the month that is 17 months after the date in B1.
Calculate the Date Exactly X Months from a Given Date
Excel’s EDATE function lets you calculate what the date will be X months from a given date.
Sticking with the loan example, I’ve taken out a loan over 18 months, starting on 9th February 2025. Repayments are made not on the last day of the month but on the anniversary of the loan agreement and I’d like to know what date the loan ends. Into B2 I type: =EDATE(B1,18)
Display the Day Name of a Given Date
In B1 I have 9th February 2025. Into B2 I type =TEXT(B1,”dddd”)
This converts the date in B1 into text and displays the full day name (i.e. Sunday). To display the short form ie Sun, Mon, Tue etc, use ddd
Display the Month Name of a Given Date
Again in B1 I have 9th February 2025. Into B2 I type =TEXT(B1,”mmmm”)
This converts the date in B1 into text and displays the full month name (i.e. February. To display the short form ie Jan, Feb etc, use mmm
Download a copy of the file(s) used in this video: https://share.zight.com/d5uQylGv