Date and time functions
The functions in this category allow you to work with Excel defined date and time values. Excel stores dates as integers, 1 is 1/1/1900 and 2 is 1/2/1900. This explains why Excel can't work with dates earlier than 1/1/1900.
Today is 9/17/2018 and the Excel date is represented by 43360. The weekday is Monday and the week starts with Sunday in North America thus Monday is the second weekday in the week, demonstrated in cell C9.
Excel converts a date automatically, try and type a date in a cell and then select the cell, press CTRL + 1 to open the "Format cells" dialog box. Now change the format to "General" and press OK button. You can now see the integer in the cell that represents the date value.
| Date functions | Returns |
| YEAR function | The year of an Excel date. |
| MONTH function | The month of an Excel date. |
| DAY function | The day of the month from an Excel date. |
| TODAY function | Returns the current date. Note, this function is volatile. |
| WEEKDAY function | Returns the weekday of an Excel date. |
Excel stores time as a decimal from 0 (zero) to 1. 0 is 12.00 a.m and 0.5 is 12.00 p.m. Each hour is 1/24 or approx. 0.041666667.
| Time functions | Returns |
| HOUR function | The hour of an Excel defined time value. |
| MINUTE function | The minute of an Excel defined time value. |
| SECOND function | The second of an Excel defined time value. |
| NOW function | Returns the current time. Note, this function is volatile. |
Excel also allows you to store date and time combined. For example, 1.5 is 1/1/1900 12.00 p.m and 2.5 is 1/2/1900 12.00 p.m. The following functions lets you create an Excel defined date and time value.
| Functions | Returns |
| DATE function | An Excel date based on year, month and day. |
| TIME function | Excel time based on hour, minute and second. |
| DATEVALUE function | Excel date based on a text string. |
| TIMEVALUE function | Excel time based on a text string. |
These functions perform calculations to Excel defined date and time values.
| Functions | Returns |
| DATEDIF function | Returns the number of years, month and days between two dates. |
| EDATE function | Returns a date based on a start date and a given number of months. |
| EOMONTH function | The last date in a given month based on a start date and a number that represents the number of months. |
| NETWORKDAYS function | Returns the number of workdays between two dates. |
| WORKDAY function | The date based on a start date and a given number of workdays. |
| YEARFRAC function | The fraction of a year based on a start and end date and then number of days between. |
How to use the WEEKNUM function
What is the WEEKNUM function? The WEEKNUM function calculates a given date's week number based on a return_type parameter that […]
How to use the NETWORKDAYS.INTL function
What is the NETWORKDAYS.INTL function? The NETWORKDAYS.INTL function calculate the number of working days between two dates, excluding weekends. It […]
How to use the WORKDAY function
What is the WORKDAY function? The WORKDAY function returns a date based on a start date and a given number […]
How to use the YEARFRAC function
What is the YEARFRAC function? The YEARFRAC function returns the fraction of the year based on the number of whole […]
How to use the EOMONTH function
What is the EOMONTH function? The EOMONTH function returns an Excel date for the last day of a given month […]
What is the EDATE function? The EDATE function returns a date determined by a start date and a number representing […]
What is the DAYS function? The DAYS function calculates an integer that represents the number of days between two dates. […]
What is the NOW function? The NOW function returns the current date and time. It is a volatile function. Table […]
How to use the TIMEVALUE function
What is the TIMEVALUE function? The TIMEVALUE function returns a decimal number representing an Excel time value, based on a […]
What is the TODAY function? The TODAY function returns the Excel date (serial number) of the current date. Note, this […]
How to use the DATEVALUE function
What is the DATEVALUE function? The DATEVALUE function returns an Excel date value (serial number) based on a date stored […]
How to use the DATEDIF function
What is the DATEDIF function? The DATEDIF function returns the number of days, or months, or years between two dates. […]
How to use the MINUTE function
What is the MINUTE function? The MINUTE function returns a whole number that represents the minute based on an Excel […]
How to use the SECOND function
What is the SECOND function? The SECOND function returns an integer representing the second based on an Excel time value. […]
What is the HOUR function? The HOUR function returns an integer representing the hour of an Excel time value. The […]
How to use the NETWORKDAYS function
The NETWORKDAYS function returns the number of working days between two dates, excluding weekends. It also allows you to ignore […]
What is the TIME function? The TIME function returns a decimal value between 0 (zero) representing 12:00:00 AM and 0.99988426 […]
How to use the WEEKDAY function
What is the WEEKDAY function? The WEEKDAY function converts a date to a number from 1 to 7 corresponding to […]
What is the DAY function? The DAY function extracts the day as a number from an Excel date. Table of […]
What is the MONTH function? The MONTH function returns a number representing the month from an Excel date. 1 - […]
What is the YEAR function? The YEAR function converts a date to a number representing the year in the date. […]
What is the DATE function? The DATE function returns a number that acts as a date in the Excel environment. […]