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.
||The year of an Excel date.
||The month of an Excel date.
||The day of the month from an Excel date.
||Returns the current date. Note, this function is volatile.
||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.
||The hour of an Excel defined time value.
||The minute of an Excel defined time value.
||The second of an Excel defined time value.
||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.
||An Excel date based on year, month and day.
||Excel time based on hour, minute and second.
||Excel date based on a text string.
||Excel time based on a text string.
These functions perform calculations to Excel defined date and time values.
||Returns the number of years, month and days between two dates.
||Returns a date based on a start date and a given number of months.
||The last date in a given month based on a start date and a number that represents the number of months.
||Returns the number of workdays between two dates.
||The date based on a start date and a given number of workdays.
||The fraction of a year based on a start and end date and then number of days between.
How to use the WEEKNUM function
The WEEKNUM function calculates a given date's week number based on a return_type parameter that determines which day the week […]
How to use the EDATE function
The EDATE function returns a date determined by a start date and a number representing how many months. Use EDATE […]
How to use the DAYS function
The DATE function calculates the number of days between two dates. Formula in cell D3: =DAYS(B3,C3) Excel Function Syntax DAYS(end_date, […]
How to use the NOW function
The NOW function returns the current date and time. Formula in cell B3: =NOW() Excel Function Syntax NOW() Arguments The […]
How to use the TODAY function
The TODAY function returns the Excel date (serial number) of the current date. Warning! This function is volatile meaning it […]
How to use the DATEDIF function
The DATEDIF function returns the number of days, months, or years between two dates. The DATEDIF function exists in order to […]
How to use the HOUR function
Returns an integer representing the hour of an Excel time value. The returning number is ranging from 0 (12:00 A.M.) […]
How to use the TIME function
The TIME function returns a decimal value between 0 (zero) representing 12:00:00 AM and 0.99988426 representing 11:59:59 P.M. Formula in cell […]
How to use the WEEKDAY function
Converts a date to a weekday number from 1 to 7. Formula in cell D3: =WEEKDAY(B3) Excel Function Syntax WEEKDAY(serial_number,[return_type]) […]
How to use the DAY function
The DAY function extracts the day as a number from an Excel date. Formula in cell C3: =DAY(B3) Excel Function […]
How to use the MONTH function
Extracts the month as a number from an Excel date. Formula in cell C3: =MONTH(B3) Excel Function Syntax MONTH(serial_number) Arguments […]
How to use the YEAR function
The YEAR function converts a date to a number representing the year in the date. The number is between 1900 […]