What day is it? What day will it be 10 months from now? How many days until the end of next month? Microsoft Excel has a wide variety of date functions. This article will focus on several of the most useful. Hopefully you’ll find a few to add to your function repertoire.
DATEVALUE
Excel stores dates in a serial number format. Serial number 1 represents January 1, 1900 and each day after that increments the serial number by one. In other words January 2nd 1900 is 2 and serial number 44074 is August 31, 2020. Most of the date functions require this serial number. If you want to work with a date in a common format such as 08/31/2020 DATEVALUE is your go to.
Usage is: = DATEVALUE(“date”)
Example: =DATEVALUE(“08/31/2020”) will result in its serial 44074
Note: For the examples in this article, we’ll use DATEVALUE in place of a date serial
EOMONTH
EOMONTH results in the last day of a given number of months. I wrote an entire post on it here already showing a use case in accrual accounting. You can read that post here.
Excel EOMONTH Function Leads to Consistent Expense Accruals
Usage is: =EOMONTH([DateSerial], [number of months])
Example: =EOMONTH(DATEVALUE(“08/05/2020”),0) results in 08/31/2020
EDATE
EDATE results in a date that is the specified number of months before or after the given start date. Use negative numbers to give prior months and positive values for future ones.
Usage is:=EDATE([start date], [number of months])
Example: if we want the following month =EDATE(TODAY(),1) or if we want the day one month prior =EDATE(Today(),-1)
TODAY
TODAY() results in today’s date, it can be used as a parameter in formulas that need a date and we’ll use it in many of the following examples.
Usage: =TODAY()
Example: to get today’s date =TODAY()
DAYS
DAYS results in the number of days between two dates, excluding the end date. Unlike other date functions you can provide a date in a text format as a parameter. Although it won’t hurt any if you use DATEVALUE, a serial date or even TODAY()
Usage: =DAYS([end date],[start date])
Example: to get the number of days between August 1st and August 10th =DAYS(“8/10/20”,”8/1/20”)
WORKDAY.INTL
WORKDAY.INTL results in the date that’s the number of weekdays away from the given start date. This version of the WORKDAY function allows finer control over weekends and holidays than the vanilla WORKDAY function. The weekend day combinations are listed in a table that can be found on Microsoft’s support page (links in the Learn More section at bottom). If the weekends parameter is omitted, the default weekend is Saturday & Sunday.
Usage: =WORKDAY.INTL([start date], [number of days, [weekends], [holidays])
Example: To get a day 5 workdays prior to August 1, 2020 where Wednesday is the only day considered a weekend: =WORKDAY.INTL(DATEVALUE(“08/01/2020”),-5,14)
YEARFRAC
YEARFRAC results In the fractional portion of a year that has(will) elapse between two dates
Usage: =YEARFRAC([start date], [End date])
Example:To see what portion of the year passes between January 1st and June 30th (i.e. 0.50) =YEARFRAC(DATEVALUE(“01/01/2020”),DATEVALUE(“06/30/20”))
WEEKDAY
WEEKDAY Results in a number which translates in the day of the week of the provided date.
A Result of 1 is Monday and 7 is Sunday the other numbers between Correspond to each successive day
Usage: =WEEKDAY([date])
Example: to find which day of the week (i.e. 7 which is Sunday) =WEEKDAY(DATEVALUE(“08/01/20”))
Extract functions
The next three are what I call the extract functions because they allow you to extract or single out a part of the given date.
DAY
DAY Results in the day portion of a given date. This formula is useful for extracting the day data when needed.
Usage:=DAY([date])
Example: to get the 1 out of August 1, 2020 =DAY(DATEVALUE(“08/01/2020”))
MONTH
Month results in the month portion of a given date. This formula is useful for extracting the month data when needed.
Usage: =MONTH([date])
Example: to get 8 (for August) out of August 1, 2020 =MONTH(DATEVALUE(“08/01/2020”))
YEAR
YEAR results in the year from a given date. This formula is useful but you need to extract the year from a given date.
Usage: =YEAR([date])
Example: to get 2020 out of August 1, 2020 =YEAR(DATEVALUE(“08/01/2020”))
Want to learn how to automate your financial workpapers? Do you want to get started with VBA?
My book, Beginning Microsoft Excel VBA Programming for Accountants has many examples like this to teach you to use Excel to maximize your productivity! It’s available on Amazon, Apple iBooks and other eBook retailers!
Learn More:
Function pages on Microsoft Support:
- DAYS Function
- DATEVALUE Function
- EOMONTH Function
- EDATE Function
- TODAY Function
- WORKDAY.INTL Function
- YEARFRAC Function
- WEEKDAY Function
- DAY Function
- MONTH Function
- YEAR Function
Example Workbook can be found at: MicroSoft OneDrive