Date and Time Functions in Excel
Date and Time Functions in Excel
Excel boasts a vast library of functions, but don’t be overwhelmed! We’ll explore the most essential functions grouped into intuitive categories. Here is the list of Excel Formulas and Functions. We have included Excel functions, Description, Syntax. Explained with Example Formulas.
Date and Time Functions
Manage Dates and Times with Ease: Working with dates and times in Excel can be a breeze with the dedicated Date & Time Functions. Need to know the current date (TODAY) or extract the year (YEAR) from a date? Perhaps you want to calculate the difference between two dates (DATEDIF)? These functions streamline date and time manipulation for efficient calendar calculations and time-based analyses.
- Work with dates and times efficiently.
- Calculate differences between dates (DATEDIF).
- Extract specific components from dates (YEAR, MONTH, DAY).
- Automate tasks based on current date and time (TODAY, NOW).
Function | Description | Syntax and Formula |
DATE | The DATE function is used to create a date value from individual year, month, and day components. | Syntax: DATE(year, month, day) Formula: DATE(2021,5,1) will return the date 5/1/2021. |
DATEDIF | The DATEDIF function is a built-in function in Microsoft Excel that calculates the number of days, months, or years between two dates. | Syntax: DATEDIF(start_date, end_date, unit) Formula: To calculate the number of days between two dates, the syntax would be:DATEDIF(A1,B1,”d”) |
DATEVALUE | The DATEVALUE function in Microsoft Excel converts a date that is stored as text to a serial number that Excel recognizes as a date. | Syntax: DATEVALUE(date_text) Formula: DATEVALUE(“1/1/2020”)This example would return the serial number 43831, which is the number that Excel recognizes as January 1, 2020. |
DAY | SThe DAYS function returns the number of days between two dates. | Syntax: DAYS(end_date, start_date) Formula: DAYS(DATE(2020,10,1),DATE(2020,9,1))This example returns the number of days between October 1st, 2020 and September 1st, 2020, which is 30 days. |
DAYS | The DAYS function returns the number of days between two dates. | Syntax: DAYS(end_date, start_date) Formula: DAYS(A2,A1) where A1 contains the start date and A2 contains the end date. |
DAYS360 | The DAYS360 function is used to calculate the number of days between two dates based on a 360-day year. | Syntax: DAYS360(start_date, end_date, [method]) Formula: DAYS360(A2,B2)This formula will calculate the number of days between the dates in cells A2 and B2. |
EDATE | EDATE: The EDATE function returns a date that is a specified number of months before or after a given date. | Syntax: EDATE(start_date, months) Formula: EDATE(TODAY(), 3)This formula will return a date that is 3 months after the current date. |
EOMONTH | The EOMONTH function returns the last day of the month that is a specified number of months before or after a given date. | Syntax: EOMONTH(start_date, months) Formula: EOMONTH(A2, 3) where A2 contains a date value. This will return the last day of the month 3 months after the date in A2. |
HOUR | The HOUR function returns the hour part of a given time, as a number between 0 and 23. | Syntax: HOUR(serial_number) Formula: HOUR(A2) returns the hour part of the time in cell A2. |
ISOWEEKNUM | The ISOWEEKNUM function returns the ISO week number of a given date. | Syntax: ISOWEEKNUM(date) Formula: ISOWEEKNUM(A2)where A2 contains a date value. |
MINUTE | The MINUTE function in Excel returns the minute component of a given time. | Syntax: MINUTE(serial_number) Formula: MINUTE(A2) where A2 contains the time 10:15 AM. The result would be 15. |
MONTH | The MONTH function in Microsoft Excel is used to return the month number from a given date. | Syntax: MONTH(serial_number) Formula: MONTH(A2)In this example, the function will return the month number of the date in cell A2. |
NETWORKDAYS | NETWORKDAYS Function:The NETWORKDAYS function calculates the number of whole working days between two dates. It excludes weekends and holidays from the calculation. | Syntax: NETWORKDAYS(start_date, end_date, [holidays]) Formula: NETWORKDAYS(A2,A3)This example would calculate the number of working days between the dates in cells A2 and A3. |
NETWORKDAYS.INTL | NETWORKDAYS.INTL is an Excel function that calculates the number of workdays between two dates, excluding weekends and holidays. | Syntax: NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) Formula: NETWORKDAYS.INTL(A1,B1,11,C1:C5)This example calculates the number of workdays between the dates in A1 and B1, with weekends set to Saturday and Sunday (11), and holidays in the range C1:C5. |
NOW | NOW: This function returns the current system date and time. | Syntax: NOW() Formula: NOW()This formula will return the current date and time, for 2/26/2020 4:54:00 PM |
SECOND | The SECOND function returns the second part of a time value. | Syntax: SECOND(time_value) Formula: SECOND(A1) where A1 contains the time value 09:15:30, the result would be 30. |
TIME | The TIME function is used to construct a time value from hour, minute, and second values. | Syntax: TIME(hour, minute, second) Formula: TIME(14,30,45) returns the value 14:30:45. |
TIMEVALUE | The TIMEVALUE function is used to convert a time expressed as text into a serial number that Excel recognizes as time. | Syntax: TIMEVALUE(time_text) Formula: TIMEVALUE(“10:30 AM”) will return 0.4375. |
TODAY | The TODAY function returns the current date. | Syntax: TODAY() Formula: TODAY()This will return the current date in the cell. |
WEEKDAY | The WEEKDAY function in Excel returns a number from 1-7 corresponding to the day of the week for a given date. | Syntax: WEEKDAY(serial_number, [return_type]) Formula: WEEKDAY(A2, 1)Where A2 is a cell containing a date, the WEEKDAY function will return a number from 1-7 corresponding to the day of the week. For example, if A2 contains the date “2/14/2021”, the WEEKDAY function will return 4, corresponding to Wednesday. |
WEEKNUM | The WEEKNUM function returns the week number of a given date in the year. | Syntax: WEEKNUM(serial_number, [return_type]) Formula: WEEKNUM(A1,1)This example will return the week number of the date in cell A1 using the return type of 1 (week starts on Sunday). |
WORKDAY | The Excel WORKDAY function returns a date that is a given number of workdays away from a start date. | Syntax: WORKDAY(start_date, days, [holidays]) Formula: WORKDAY(DATE(2020,1,1), 10)This example returns the date 10 workdays after January 1, 2020. |
WORKDAY.INTL | The WORKDAY.INTL function calculates the date after a specified number of workdays, taking into account weekends and holidays. | Syntax: WORKDAY.INTL(start_date, days, [weekend], [holidays]) Formula: WORKDAY.INTL(DATE(2020, 1, 1), 10, 11, A2:A10)This example returns the date 10 workdays after January 1, 2020, taking into account weekends 11 (Saturday and Sunday) and holidays specified in cells A2:A10. |
YEAR | The YEAR function returns a four-digit year (a number from 1900 to 9999) given a date value. | Syntax: YEAR(serial_number) Formula: YEAR(A1) where A1 is a cell containing a date value. The result would be the four-digit year of the date in A1. |
YEARFRAC | The YEARFRAC function is used to calculate the fraction of the year represented by the number of days between two dates. | Syntax: YEARFRAC(start_date, end_date, [basis]) Formula: YEARFRAC(B2,C2,1)This example will calculate the fraction of the year between the dates in cells B2 and C2, using the US (NASD) 30/360 day count basis. |
You May Like to Browers More
