MS Excel: Excel Dates And Times

Added On: May 14, 2023

Last Update: Aug 17, 2023

Datedif function of Excel is used to calculate the day, month, and year between two dates or you can also use this function to calculate the age of any person or anything.

Excel Date and Time formulas are used to decrease effort and increase productivity and save time also.

Before starting work with Excel dates and times, it’s necessary to understand the way in which Excel stores the Times and Dates. Excel stores Date and Time in a simple number but due to the formatting of the Excel worksheet this will be displayed as date, time, or date & time.

Date Formula In Excel

Excel Dates are stored in the Excel worksheet as positive integers only. An example is given below:

1	=	January 1, 1900
2	=	January 2, 1900
42736	=	January 1, 2017

Therefore, when opening an Excel worksheet, the integer values 1, 2, 42736 are displayed as shown in the image below (depending on the formatting of the cell of the worksheet):

Select a cell in the Excel worksheet.
Right-click and click on the option "Format Cells".

From the left list menu select "Date" and from the right section select the suitable type of date.
Now, click on the "OK" button to save the format of data on the selected cell.
Enter any integer value to the cell and press "Enter" on the keyboard.

Times Formula In Excel

Excel Time is stored in the Excel worksheet as decimal values between 0 and 1 (represents the proportion of the time). An example is given below:

0	=	00.0:00 hrs
0.25	=	06:00:00 hrs
0.5	=	12:00:00 noon

Therefore, when opening an Excel worksheet, the decimal values 0, 0.25, and 0.5 are displayed as shown in the image below (depending on the formatting of a cell of the worksheet):

Select a cell in the Excel worksheet.
Right-click and click on the option "Format Cells".

From the left list menu select "Time" and from the right section select the suitable type of Time.
Now, click on the "OK" button to save the format of Time on the selected cell.
Enter any decimal value to the cell and press "Enter" on the keyboard.

Dates & Times Formula In Excel

Excel Dates & Times are stored in Excel worksheet as decimal values, and comprised of Integer (represents the proportion of the day) and fraction between 0 and 1 (represents the proportion of the Time). An example is given below:

1.5	=	00.0:00 hrs on January 1, 1900
2.25	=	06:00:00 hrs on January 2, 1900
42736.5	=	12:00:00 noon on January 1, 2017

Therefore, when opening an Excel worksheet, the values 1.5, 2.25, and 42736.5 are displayed as shown in the image below (depending on the formatting of a cell of the worksheet):

Select a cell in the Excel worksheet.
Right-click and click on the option "Format Cells".

From the left list menu select "Custom" and from the right section select the suitable type of the Dates & Times.
Now, click on the "OK" button to save the format of Date & Time on the selected cell.
Enter any fraction value to the cell and press "Enter" on the keyboard.

Adding and Subtracting Dates and Times in Excel

Excel stores the Dates and Times as numbers so, these can be added, subtract and compare between dates and times in the Excel worksheet. This is as simple as that you do add or subtract any other numbers.

Examples of Add, Subtract, or comparison between Dates and Times are given below:

Example 1: Calculate the Number of Days Between Two Dates

Enter two dates in cell A1 and A2 (20/08/17 and 31/08/17).
Select an empty cell.
Enter this formula "=A2-A1"
This formula will calculate the difference between the dates i.e. number of days.
Answer will be 11/01/00 0:00.

Example 2: Calculate the Difference Between Two Times

Enter two Times in cell A1 and A2 (13:00 and 17:15).
Select an empty cell.
Enter this formula "=A2-A1"
This formula will calculate the difference between the Times i.e. number of hours and minutes.
Answer will be 4:15.

Note: To display the required result, cell B1 should be formatted with the "hh:mm" time format.

Example 3: Calculate the Difference Between Two Dates and Times

Enter two Times in cell A1 and A2 (30/08/17 17:00 and 31/08/17 9:00).
Select an empty cell.
Enter this formula "=A2-A1"
This formula will calculate the number of hours and minutes between the two dates & times of the Excel worksheet.
Answer will be 16:00:00.

Note: To display the required result, cell B1 should be formatted with the "hh:mm" time format.

Example 4: Add Time With A Date & Time

Enter two Times in cell A1 and A2 (08/30/2017 17:00 and 23:00).
Select an empty cell.
Enter this formula "=A2+A1"
This formula will calculate the date and time after 23 hours from the date of cell A1.
Answer will be 31/08/17 16:00.

Note: To display the required result, cell B1 should be formatted with the "dd/mm/yy hh:mm" date & time format.

Formatting Dates and Times in Excel

Excel result in above examples may not the date & time are the required one formatting. This may throw an error that the formula isn’t working, but this is not true.

Excel cell can be made to show the correct result by formatting the type into a General, Date, Time or Date & Time this depends on the requirement.

Simplest way to modify the default formatting of one or more cells is to just:

Select the cell of the Excel worksheet to be formatted.
Select the required type from the drop-down list in the home tab.

If there is no option for your required type then simply click on More Number Formats from the drop-down list. Now, a dialogue box will open of "Format cells", here you will have additional features or you can also define your own custom format.