Microsoft Excel Date Functions Round Up

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.

Date Function

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:

Example Workbook can be found at: MicroSoft OneDrive

This site uses Akismet to reduce spam. Learn how your comment data is processed.