Whether you use Microsoft Excel for managing monthly bills or tracking time for work, you likely use dates or times. With these functions, you can enter or obtain the dates and times you need.
You might already be familiar with some date and time functions in Excel. So let’s look at essential functions for common tasks as well as more advanced functions you may not know exist.
View the Current Date or Time: TODAY and NOW
Probably the handiest of all date and time functions in Excel are TODAY and NOW. TODAY provides the current date and NOW provides the current date and time.
The syntax for each is the same, with the name of the function and no arguments. Enter one of the following to obtain the date or the date with the time.
=TODAY()
=NOW()
Create a Full Date: DATE
Not all dates in your sheet may be in a single cell. Maybe you have the day, month, and year in separate columns and want to combine them for a complete date. You can use the DATE function in Excel.
The syntax for the function is DATE(year, month, day) with all three arguments required. Enter the year in four digits, the month as a number from 1 to 12, and the day as a number from 1 to 31.
To combine the year, month, and day from our cells A2, B2, and C2 respectively, you would use the following formula:
=DATE(A2,B2,C2)
Get Parts of a Date: DAY, MONTH, YEAR
When using dates in other formulas, you may need to obtain the serial number of a day, month, or year. This is exactly what the DAY, MONTH, and YEAR functions in Excel do. Where the DATE function combines parts of a date, these functions provide parts of one.
The syntax for each function is the same with the name of the function followed by a cell reference or serial number in parentheses. For example, DAY(cell_reference).
To obtain the day number in cell F2, use the following formula. The result will be a number from 1 to 31.
=DAY(F2)
To obtain the month number in cell F2, use the following. The result will be a number from 1 to 12.
=MONTH(F2)
To obtain the year number in cell F2, use the following. The result will be a four-digit number.
=YEAR(F2)
See a Portion of the Day: TIME
The TIME function in Excel provides you with the decimal portion of a day based on hours, minutes, and seconds in your sheet.
The syntax is TIME(hour, minute, second) where all three arguments are required, and the input is a number from 0 to 32767 representing each.
To find the decimal number for 12 hours on the dot with no minutes or seconds, you would use the following formula and replace the cell references with your own.
=TIME(A2,B2,C2)
Note: If the result displays a time rather than a decimal, format the cell as a number by selecting “Number” in the Number drop-down box on the Home tab.
Get Parts of a Time: HOUR, MINUTE, SECOND
Similar to DAY, MONTH, and YEAR, the HOUR, MINUTE, and SECOND functions give you parts of a time entry.
The syntax for each function is the same with the name of the function followed by a cell reference or serial number in parentheses. For example, HOUR(cell_reference).
To obtain the hour in cell F2, use the following formula:
=HOUR(F2)
To obtain the minutes in cell F2, use the following:
=MINUTE(F2)
To obtain the seconds in cell F2, use the following:
=SECOND(F2)
Calculate Days, Months, or Years Between Dates: DATEDIF
You can use the DAYS function to find the number of days between two dates. But the DATEDIF function goes a step further by allowing you to find the number of days, months, or years.
The syntax is DATEDIF(start_date, end_date, type) where all three arguments are required. The type is based on the value you want to receive:
- Years: Enter Y.
- Months: Enter M.
- Days: Enter D.
- Difference in months without years and days: Enter YM.
- Differences in days without years: Enter YD.
To find the number of months between 1/1/2021 and 12/31/2022 in cells H2 and I2, you would use this formula:
=DATEDIF(H2,I2,"M")
To find the number of days between the same start and end dates in the same cells you would use this formula:
=DATEDIF(H2,I2,"D")
Find the Number of Workdays: NETWORKDAYS
Maybe you want to find the number of workdays between two dates. With NETWORKDAYS, you can get this number and optionally reference holidays in a different cell range.
The syntax is NETWORKDAYS(start_date, end_date, holidays) with only the first two arguments required.
To find the number of workdays between 1/1/2022 and 12/31/2022 in cells H2 and I2, use this formula:
=NETWORKDAYS(H2,I2)
To find the number of workdays for those same dates but with holidays listed in the range H5 through H7, use this formula:
=NETWORKDAYS(H2,I2,H5:H7)
Note: Even though we have three holidays listed, they do not all fall on workdays. So, the difference in the above is only one day.
See the Week Number: WEEKNUM
You don’t have to count weeks manually if you use the WEEKNUM function. With it, you can find the week number in a year for any date.
The syntax is WEEKNUM(cell_reference, type) with the first argument required. The type argument can be used for weeks beginning on a particular date. If the argument is omitted, the function assumes Sunday by default.
To find the week number for 4/15/2022 in cell F2, use the following formula:
=WEEKNUM(A2)
To find the same week number for weeks starting on Monday, you would use the following formula with 2 as the type argument:
=WEEKNUM(A2,2)
Visit the Microsoft Support page for the WEEKNUM function for a full list of the 10 available types.
Hopefully these date and time functions and formulas help keep you on track in your Excel sheet.