Excel Date & Time Functions Part 3
Extracting date information and calculating % of year complete.
This is the third of a multi-part series on using Dates & Times in Excel.
Want to get the most out of this lesson? Download the sample data so that you can follow along.
Extracting parts of a date is perhaps one of the most common questions I get. It’s easy to understand why - breaking data down into chunks like years, days, etc. has many advantages. For example, a manufacturing facility that wants to know all of the equipment it produced in a single month. If we know the manufacturing date we can easily extract the month from the data. Then filter accordingly. Another popular question is figuring out how much of the year has passed or is remaining.
In todays lesson I will be diving into both of these questions in detail.
Extracting date information
To extract date information Excel has a 6 functions that are pretty much plug and play. They are:
YEAR(): This function extracts the year from a given date.
MONTH(): This function extracts the month from a given date.
DAY(): This function extracts the day of the month from a given date.
WEEKDAY(): This function extracts the day of the week from a given date. It starts with Sunday which is 1.
WEEKNUM(): This function extracts the week number from a given date.
EOMONTH(): This function returns the date of the last day of the month of a given date.
Lets look at a simple example. In this case we are looking at the date 02/04/2018. Using the functions above we can extract several bits of information:
The last 3 functions do have a return_type argument.
For WEEKDAY() the additional syntax allows you to define the day the week starts with:
For WEEKNUM() the additional syntax allow you to define which day starts the week you are looking for:
For EOMONTH() the user can define the last day of the month for any month forward or backward from the present month. So for example - we are showing the last day of the month for February. If we wanted January we would add -1. If we wanted April we would be in 2.
Figuring out percentage of the year remaining & completed
I have used this particular function when creating dashboards for clients. They often want to see how they are performing against and annual or semi annual bench mark. In order to have proper reference they need to know how much of the year has been completed. For example if they are 25% to their annual goal but only 10% of the year has been completed they know they are ahead.
Excel has made this relatively straightforward. The function you will use for this is YEARFRAC(). The syntax for this function is:
YEARFRAC(start_date, end_date, [basis])
Where:
start_date and end_date are the two dates between which you want to know the fraction of the year.
[basis] is an optional argument that defines the day count basis to use. If omitted, it defaults to 0. The possible values for the [basis] argument are:
0 or omitted: US (NASD) 30/360: This option assumes a year of 360 days and all months have 30 days. The US (NASD) 30/360 day count convention is widely used in the financial markets, particularly for bonds and other interest-related calculations.
1: Actual/actual: This option calculates the actual number of days between the two dates, divided by the actual number of days in the year (which could be 365 or 366 depending on whether the period spans a leap year).
2: Actual/360: This option calculates the actual number of days between the two dates, but assumes a year of 360 days.
3: Actual/365: This option calculates the actual number of days between the two dates, but assumes a year of 365 days.
4: European 30/360: This is similar to the 30/360 day count convention, but with some adjustments to how the end of the month is handled. This method also assumes a 360-day year and 30-day months, but applies different rules at the end of a month. In the European version, if either date falls on the 31st of the month, that date will be treated as if it was the 30th.
Looking at two simple examples, we will calculate the percentage of year completed and the percentage of the year remaining.
Thanks for reading the 3rd lesson in this multi part series. I hope you are able to start using these Excel skills immediately.
In the meantime, make sure you practice using these functions! This is easy to do - just download the sample data set included with this lesson and get to work. Apply what you have learned right away and it will stay with you.
I hope you enjoyed this lesson. There are a few other ways I can help you master Excel:
Do you have any burning questions about Excel that you want answered? Send your question to hello@learnexcel.live. I will address it in a future newsletter.
Do you or your organization need a little spreadsheet help? Book a 1 on 1 with me here.
Want to learn more Excel Shortcuts? I have created something special (and free) for you: The Best Damn Excel Shortcut Sheet
Til next time ✌️
-Ajay
The Excel Ninja 🥷
PS - If you like these lessons please share with a friend or two...