List of EXCEL Date Functions

1. TODAY function

This function returns the current date. This function is useful when you want the current date displayed 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 the File tab, then click Options

In the Formulas category under the calculation option, choose Automatic.

 

2. Excel DATE function

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

Syntax

=DATE (year, month, day)

Example

When you write =DATE(2022, 2, 20), Excel returns a corresponding serial number to 2/20/22

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

=DATE (2022,2,15) – 5: This subtracts 5 days from 15th February 2022.

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("15-March-2022"): Returns 44635 which is a code that represents March 15, 2022

=DATEVALUE("March 15, 2022"): Returns 44635 which is a code that represents March 15, 2022

=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")