List of EXCEL Date Functions

List of EXCEL Date Functions

1. TODAY function

This function returns the current date. This function is useful when you want the current date displays on your worksheet. The returned value will be updated each time the worksheet is recalculated or when you open the workbook.

Syntax

=TODAY ()

The function has no arguments.

Example

=TODAY () this function returns current date

=TODAY () + 2: Returns current date plus 2 days

= DAY (TODAY ()): Returns the current day of the month.

=MONTH (TODAY ()): Returns the current month of the year.

Note: If the current dates are not displayed when you use the TODAY function, change the workbook settings. Go to File tab, click options and then in the Formulas category under calculation option choose Automatic.

 

2. Excel DATE function

DATE function returns a serial number of date based on the year, month and day values that you specify.

Syntax

=DATE (year, month, day)

Example

When you write =DATE(2018, 3, 20), Excel returns a corresponding serial number to 20/3/2018.

=DATE (YEAR (TODAY()), MONTH (TODAY ()), 1): This function returns the first day of the current year and month.

=DATE (2018,3,20) – 5: This subtracts 5 days from March 20, 2018.

3. EXCEL DATEVALUE function

The DATEVALUE(date_text) converts a date in text format to a serial number recognized by Excel as a date.

The function can understand a lot of date formats and references to cells that contain text dates. The text dates are converted to date format.

Syntax

=DATEVALUE(DAY,MONTH,YEAR)

Examples

=DATEVALUE(“20-March-2018”): Returns March 20, 2018

=DATEVALUE(“March 25, 2018”): Returns March 25, 2018

=TODAY()-DATEVALUE(“1/1/2018”) It returns the number of days between the current date and 1/1/2018. Simply subtract the newer date and the older date

4. Excel NOW function

The now function returns the current date and time.

Syntax

=NOW ()

The function has no arguments

5. Excel subtract dates

You can subtract one date from another to know how many dates are between the dates.

This can be done by subtracting the dates directly or using the DATEDIF function

Subtracting directly

In this example, use the new date to subtract the old date (B2-A2)

6. Using DATEDIF function

DATEDIF function is used to subtract dates. The function returns an error if the old date is greater than the new date.

 

 

 

 

 

 

Syntax

=DATEDIF (old date, new date, “d”)

7. Subtract date from the current date

To subtract dates using the current date, use TODAY ().

Syntax

=TODAY ()-OLD DATE

Or

=DATEDIF (OLD DATE, TODAY(), “d”)