Adobe Apple AWS CertNexus Check Point Cisco Citrix CMMC CompTIA Dell Training EC-Council F5 Networks Google IBM ISACA ISC2 ITIL Lean Six Sigma Oracle Palo Alto Python PMI Red Hat Salesforce SAP SHRM Tableau VMware Microsoft 365 AI Azure Dynamics Office Power Platform PowerShell Security SharePoint SQL Server Teams Windows Client Windows Server
Agile / Scrum AI / Machine Learning Business Analysis Cloud Cybersecurity Data & Analytics DevOps Human Resources IT Service Management Leadership & Pro Dev Networking Programming Project Management Service Desk Virtualization
AWS Agile / Scrum Business Analysis CertNexus Cisco Citrix CompTIA EC-Council Google ITIL Microsoft Azure Microsoft 365 Microsoft Dynamics 365 Microsoft Power Platform Microsoft Security PMI Red Hat Tableau View All Certifications
Microsoft Excel Tutorial | Date Functions & Example Formulas Taylor Karl / Wednesday, April 19, 2023 / Categories: Resources, Microsoft Office, Training Trends 5888 0 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. Submit your email below to download our free 49-page eBook, Top 20 Office Productivity Tips 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 Functions: The PI() function returns the value of pi: 3.142... References: A2 returns the value in cell A2. Constants: Numbers or text values entered directly into a formula, such as 2. 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. Print Tags Excel Excel Hacks Excel Tips Related articles Text Functions & Formula Examples | Microsoft Excel Tutorial How to Delete a Row in Excel with a Keyboard Shortcut 30 Most Useful Keyboard Shortcuts in Microsoft Excel How to Protect Cells in Microsoft Excel How to Create Drop Down Lists in Microsoft Excel