Date & Time Functions in Excel

In Excel, dates are in US format where the month comes first before the day. For example, 7/28/2050. You can add a date in any cell by using “/” or “-” character. Excel comes with useful date and time functions:

Updated Date and Time

Use the NOW function to get updated date and time whenever you open your worksheet.

Formula: Result: Updated Date Only

To get the updated current date only whenever you open your worksheet, use TODAY function.

Formulas: Results: Current Date

You may also use DATE function to combine three different values (of date, month and year) to form a date.

Formulas:

Results:

Year

YEAR function is used to extract 4-digit year number from a given Excel’s format date.

To get a fractional value representing fractional years between two dates, YEARFRAC function is used.

Formula: Day

DAY function is used to extract a day number (1 to 31) from a date given.

Formula:

Results:

Hour

HOUR function is used to extract an hour component between  0 (12:00 A.M.) to 23 (11:00 P.M.).

Results: MONTH function

Use the MONTH function to get a number of month from 1 to 12.

Number of working days between two dates

NETWORKDAYS Function returns with a number of working days between two dates after excluding weekends and holidays.

Formula: TIMEVALUE function

The TIMEVALUE function returns with an accurate text value in the decimal number of the time, for example, the formulas, “=TIMEVALUE (9:45 AM)” will return with the decimal time value of “0.40625”. 