Microsoft Excel Tutorial | Date Functions & Example Formulas

Microsoft Excel components for formulas and functions

It is essential to know that Excel is part of Microsoft Office with the Ribbon toolbar and an online help database.

• Ribbon: toolbar located at the top of all Office programs
• Spreadsheet: a page in which boxes (called "cells") are created through the intersection of columns and rows in a grid. Each cell can hold one value (numerical or text) or a formula that automatically calculates a combination of other designated cells.
• Column: vertical lines of the spreadsheet, lettered to aid in the navigation of the sheet and the creation of formulas to calculate values. In one spreadsheet, columns begin at the letter A and continue to XFD.
• Row: horizontal lines of the spreadsheet, numbered to aid in the navigation of the sheet and the creation of formulas to calculate values. In one spreadsheet, rows start at number 1 and continue to 1,048,576.
• Cell: boxes created by the columns and rows intersecting in the spreadsheet form a grid (just like graph paper); every cell has a designation of the column letter and row number, with the column always listed first.

In this image, you see the letters of the Columns (top, left to right, A – E) and the numbers of the Rows (far left, top to bottom, 1 – 10). Form cells at the intersection of columns and rows. In the example, the cell highlighted is cell (B, 2), located at the intersection of column B and row 2.

---

---

Creating Formulas in Microsoft Excel

There are 65,490 possible unique cell formats and cell styles! Therefore, to maximize Excel, you need to know these underlying terms and definitions:

• Function: a predefined formula using designated cells and values to complete a specific calculation; functions are listed on the Formula tab on the Ribbon.
• Formula: an expression that operates on values in a range of cells and returns a result (which can be an error); used to perform calculations such as addition, subtraction, multiplication, and division; ALWAYS start any function or formula with a "=," all formulas contain any or all of these four components:

From Microsoft Support

Parts of a formula

1. Functions: The PI() function returns the value of pi: 3.142...
2. References: A2 returns the value in cell A2.
3. Constants: Numbers or text values entered directly into a formula, such as 2.
4. Operators: The ^ (caret) operator raises a number to a power, and the * (asterisk) operator multiplies numbers.

Microsoft provides date and time functions documentation with detailed descriptions, examples, and usage notes.

What is a Date Formula in Excel

Date formulas are very specific in Excel because the formatting for dates is unlike that of text or numerical data. For example, there can be two or three values in a date: month/year or day/month/year. Additionally, in a date value, which number is the day, which number is the month, and which number is the year, is communicated by the numbers being formatted in very specific ways.

For example, a date can be listed with a value of 02/23 (meaning month/year). But if you entered 223, the same value and sequence of numbers, it would not be calculated or communicated as a date.

• Some functions allow specifying a date in quotes like "1/12/2000", but not all functions do (and this date is ambiguous anyway). Specify dates by using cell references or by using the DATE function.
  • For example, DATE(2000, 1, 12)
• Some functions' default behavior may not be ideal for your circumstances.
  • For example, YEARFRAC defaults to calculating a number of years based on a 360-day year. An additional argument needs to be specified to accurately calculate based on the number of actual days in between dates. Do not assume how functions work; read the documentation!
• Some functions that calculate the time passed between dates ask for a start date first and an end date second. Others require the arguments in reverse order.
  • Look at the function hints that appear to make sure you are structuring your formula correctly.

How to write Date Functions and Data Formulas in Excel

Use the list below to supercharge your use of Date functionality within Excel.

Function	Formula
Find the number of days between the current date and a due date to see how many days overdue something is	=DAYS(TODAY(), Due_Date)
Find the number of workdays (Monday through Friday) remaining before a project completion date, not including a column of specified holidays/nonworking days	=NETWORKDAYS(TODAY(), End_Date, [Holidays])
Find the number of workdays (nonstandard) remaining before a date, not including a column of specified holidays/nonworking days. Many restaurants, for example, are closed only on Mondays, which is represented by a 1 followed by six 0s	=NETWORKDAYS.INTL(TODAY(), End_Date, "1000000", [Holidays])
Find the total value of a column only for dates in the current year.	=SUM(FILTER([Data], YEAR([Date]) = YEAR(TODAY())
Find the number of years that an employee has worked since their hire date. The 1 is specified in order to count actual days (as opposed to 360-day years)	=YEARFRAC(Hire Date, TODAY(), 1)

How are Date Functions and Formulas used in Excel

Date and time functions allow the user to translate recognizable dates and times to and from how Excel stores them—as decimal numbers—more easily calculate with them.

Common uses for a data formula and data function in Excel

Most date and time functions are combined into formulas that extract, convert, and evaluate time-based data. For example:

• How much time has passed since a prior date?
• How many workdays remain until the deadline?
• How does data within the current timeframe compare to data within another timeframe?

How to use tools or plugins to make creating formulas in Excel easier

There are multiple tools and documentation to help you use the date function.

Insert Function command

The Insert Function command (fx button next to the formula bar) in Excel is very helpful, especially for people less comfortable with writing formulas from memory. It is a feature that:

• allows you to search by category or keyword for a function,
• provides an entry box and an explanation for each argument, and
• generates a correctly structured function from the inputs.

Microsoft Excel documentation

Use Microsoft's official documentation is needed to know the enumeration that represents different options. For specific or complex applications, find help in the Excel Tech Community or Answers community, two groups of experienced users.

Artificial Insurance tool

Excelformulabot is an AI tool to generate formulas from natural language descriptions available as an add-in from the Microsoft store for paid M365 subscribers.

ChatGPT from OpenAI has successfully demonstrated this ability, not to mention the ability to answer nearly any question about Excel.

How to use different types of Date Functions in Excel

Function	Function Name / Description	Syntax
DATE	Returns the serial number of a particular date	DATE(year, month, day)
DATEDIF	Calculates the number of days, months, or years between two dates. This function is useful in formulas where you need to calculate an age.	DATEDIF(start_date, end_date, unit)
DATEVALUE	Converts a date in the form of text to a serial number	DATEVALUE(date_text)
DAY	Converts a serial number to a day of the month	DAY(serial_number)
DAYS	Returns the number of days between two dates	DAYS(end_date, start_date)
DAYS360	Calculates the number of days between two dates based on a 360-day year	DAYS(start_date, end_date, [method])
EDATE	Returns the serial number of the date that is the indicated number of months before or after the start date	EDATE(start_date, months)
EOMONTH	Returns the serial number of the last day of the month before or after a specified number of months	EOMONTH(start_date, months)
HOUR	Converts a serial number to an hour	HOUR(serial_number)
ISOWEEKNUM	Returns the number of the ISO week number of the year for a given date	ISOWEEKNUM(date)
MINUTE	Converts a serial number to a minute	MINUTE(serial_number)
MONTH	Converts a serial number to a month	MONTH(serial_number)
NETWORKDAYS	Returns the number of whole workdays between two dates	NETWORKDAYS(start_date, end_date, [holidays])
NETWORKDAYS.INTL	Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days	NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
NOW	Returns the serial number of the current date and time	NOW()
SECOND	Converts a serial number to a second	SECOND(serial_number)
TIME	Returns the serial number of a particular time	TIME(hour, minute, second)
TIMEVALUE	Converts a time in the form of text to a serial number	TIMEVALUE(time_text)
TODAY	Returns the serial number of today's date	TODAY()
WEEKDAY	Converts a serial number to a day of the week	WEEKDAY(serial_number, [return_type])
WEEKNUM	Converts a serial number to a number representing where the week falls numerically with a year	WEEKNUM(serial_number,[return_type])
WORKDAY	Returns the serial number of the date before or after a specified number of workdays	WORKDAY(start_date, days, [holidays])
WORKDAY.INTL	Returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days	WORKDAY.INTL(start_date, days, [weekend], [holidays])
YEAR	Converts a serial number to a year	YEAR(serial_number)
YEARFRAC	Returns the year fraction representing the number of whole days between start_date and end_date	YEARFRAC(start_date, end_date, [basis])

 

Excel Tips for Date Functions and Formulas

Read Microsoft's official documentation when you use a function for the first time.

• Dynamic Date Functions: Use dynamic date functions rather than hard-coding dates so Excel can automatically apply them to other timeframes.

• For example DAYS("12/31/2023", TODAY()) only works to find the number of remaining days in the year for 2023. DAYS(DATE(YEAR(TODAY()), 12, 31), TODAY()) extracts the year value of the current date to be able to calculate the number of days between the current date and the end of the same year, regardless of the year.

• Whole numbers and dates:Though the date and time functions prevent us from working with these numbers directly, it is essential to know that Excel stores them using whole numbers to represent days and decimals to represent times. The number 1 represents January 1, 1900, and each sequential number represents the following day. Decimals represent the percentage of the day for a specific time, starting at midnight.

• For example, 45000 represents 3/15/2023 12:00:00 AM. 45000.75 represents 3/15/2023 6:00:00 PM.

• Built-in functions: Use the built-in functions to convert between measurements of time than doing the math "by hand."

• For example, in order to find the number of seconds between two times, =("1/1/2023 16:05:36" - "1/1/2023 16:02:11") * 24 * 60 * 60 is far more error-prone than =SECOND(TIMEVALUE("1/1/2023 16:05:36") - TIMEVALUE("1/1/2023 16:02:11"))

• Format dates correctly:Use built-in or custom date/time formats. If the wrong number format is specified, the serial number will be displayed instead of a formatted date.

• For example, March 15, 2023, in the Currency format would display as $45000.75. Excel will still be able to work with the dates correctly, but they will not be readable to a human user.

How to use common date functions in Excel

Date formulas can be combined with other Excel functions to perform more complex data operations on date and time. For example, you can use the IF function to test whether a date is before or after a specific date or the SUMIF function to add values based on dates.

• TODAY: This function returns the current date.
• DATE: This function creates a date based on year, month, and day values that you specify.
• DAY, MONTH, and YEAR: These functions extract the day, month, or year from a date value.
• DATEDIF: This function returns the difference between two dates in various units such as days, months, or years.
• EOMONTH: This function returns the last day of the month for a given date.
• NETWORKDAYS: This function returns the number of working days between two dates.

Summary

Date functions and formulas in Excel are used to manipulate and analyze dates and times. They allow you to perform various operations on dates and times, such as adding or subtracting days, finding the difference between two dates, and more. In addition, these functions help organize and analyze data that includes dates and times. Overall, date functions and formulas are essential tools for working with date and time data in Excel and can help you save time and excel date formulas improve the accuracy of your work. To learn more about Microsoft Excel, take one of our Excel training courses.