Date and Time Functions in Microsoft Excel

Date Function

Date function is useful when you want to combine three values into one cell to form a date.

The DATE function returns the sequential serial number that represents a particular date.

Syntax: DATE(year,month,day)

The DATE function syntax has the following arguments:

• Year    Required. The value of the year argument can include one to four digits. Excel interprets the year argument according to the date system your computer is using. By default, Microsoft Excel for Windows uses the 1900 date system, which means the first date is January 1, 1900.

Note: Use four digits for the year argument to prevent unwanted results. For example, “07” could mean “1907” or “2007.” Four-digit years prevent confusion.

• If year is between 0 (zero) and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE (108,1,2) returns January 2, 2008 (1900+108).
• If year is between 1900 and 9999 (inclusive), Excel uses that value as the year. For example, DATE (2008,1,2) returns January 2, 2008.
• If year is less than 0 or is 10000 or greater, Excel returns the #NUM! error value.

• Month    Required. A positive or negative integer representing the month of the year from 1 to 12 (January to December).
  • If month is greater than 12, month adds that number of months to the first month in the year specified. For example, DATE(2008,14,2) returns the serial number representing February 2, 2009.
  • If month is less than 1, month subtracts the magnitude of that number of months, plus 1, from the first month in the year specified. For example, DATE(2008,-3,2) returns the serial number representing September 2, 2007.
• Day    Required. A positive or negative integer representing the day of the month from 1 to 31.
  • If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. For example, DATE(2008,1,35) returns the serial number representing February 4, 2008.
  • If day is less than 1, day subtracts the magnitude that number of days, plus one, from the first day of the month specified. For example, DATE(2008,1,-15) returns the serial number representing December 16, 2007.

Let us implement the date function.

By applying this formula:

We get our desired output

Change the Date Format

According to your location and country you live in, you may want to change the date format that best suits you or your organization.

To change the date format:

• Right click on the cell you want to format

• Now click on Format cells 

Under the Date category choose your locale and calendar type.

• Now under the Type category, choose your desired date format. And click on OK.

We would like to have the format which specifies 14 March 2012

Here we are with our desired date format.

Calculate a date based on another date

You can use the DATE function to create a date that is based on another cell’s date. For example, you can use the YEAR, MONTH, and DAY functions to create an anniversary date that’s based on another cell. Let’s say an employee’s first day at work is 10/1/2016; the DATE function can be used to establish his fifth-year anniversary date:

• The DATE function creates a date.

=DATE(YEAR(D2)+5,MONTH(D2),DAY(D2))

• The YEAR function looks at cell D2 and extracts “2007”.
• Then, “+5” adds 5 years, and establishes “2012” as the anniversary year in cell E2.
• The MONTH function extracts the “12” from D2. This establishes “12” as the month in cell E2.
• The DAY function extracts “23” from D2. This establishes “23” as the day in cell E2.

Let’s learn by an example:

We will implement the below formula to find the 5-year anniversary date from the joining date.

=DATE(YEAR(D2)+5,MONTH(D2),DAY(D2))

Output:

Note: To increase or decrease a date by a certain number of days, simply add or subtract the number of days to the value or cell reference containing the date.

Inserting Static or Dynamic Date or Time in Excel cell

A static value in a worksheet is one that doesn’t change when the worksheet is recalculated or opened. When you press a key combination such as Ctrl+; to insert the current date in a cell, Excel “takes a snapshot” of the current date and then inserts the date in the cell. Because that cell’s value doesn’t change, it’s considered static.

1. On a worksheet, select the cell into which you want to insert the current date or time.
2. Do one of the following:
   • To insert the current date, press Ctrl+; (semi-colon).
   • To insert the current time, press Ctrl+Shift+; (semi-colon).
   • To insert the current date and time, press Ctrl+; (semi-colon), then press Space, and then press Ctrl+Shift+; (semi-colon).

Insert a date or time whose value is updated

A date or time that updates when the worksheet is recalculated or the workbook is opened is considered “dynamic” instead of static. In a worksheet, the most common way to return a dynamic date or time in a cell is by using a worksheet function.

To insert the current date or time so that it is updatable, use the TODAY and NOW functions, as shown in the following example. 

For example:

Formula	Description (Result)
=TODAY()	Current date (varies)
=NOW()	Current date and time (varies)

Try doing the below example and you’ll get your result.

Also, you can use the TIME function to insert time in Excel.

Use the following formula according to your needs:

=TIME(HOUR(A6)+2,MINUTE(A6)+10,SECOND(A6)+34)

Output:

Now the result is not quite we expected. But you can always format the time using the format cells option by right clicking on the cell.

• Right Click on the Result Cell
• Click on Format cells
• Select the appropriate format

In our case we selected HH:MM:SS and then click on OK

Now you’ve got your desired output.

Extracting Hours, Minutes and Seconds in Excel

IF you want to extract hours, minutes and seconds from a cell which contains time information you can use these functions.

Syntax:

=HOUR(CELL_NO)

=MINUTE(CELL_NO)

=SECOND(CELL_NO)

Let’s implement them to learn what they actually do.

We’ll apply these 3 formulas to extract time information from the A1 cell.

Let’s see the Output:

Yay! We have successfully extracted the required information. You can use these functions according to your work needs.

Sources:

https://support.microsoft.com/en-us/office/date-and-time-functions-reference-fd1b5961-c1ae-4677-be58-074152f97b81?ui=en-us&rs=en-us&ad=us

https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349

https://support.microsoft.com/en-us/office/insert-the-current-date-and-time-in-a-cell-b5663451-10b0-40ab-9e71-6b0ce5768138