How to Add or Subtract Date and Time in Microsoft Excel?

You may be knowing how easy it is to add or subtract numbers in Excel. Did you ever wonder how to add or subtract date and time in Microsoft Excel? Excel stores date and time as a serial numbers and uses in calculations. So if you understand how Excel treats date and time, it is easy for you to understand using them in calculations. In this article let us discuss how to use date and time functions and how to add or subtract date and time in Microsoft Excel.

Serialization of Date and Time Information in Excel

Excel uses two date systems – one is starting from 01 Jan 1900 and other is starting from 01 Jan 1904. Latest Excel versions, both on Windows and Mac, by default uses 1900 date system. We will explain all the functions with 1900 date system in this article. The process of calculations remains same even if you use 1904 date systems while the internal serialization of dates in Excel may change.

In 1900 date system, Excel starts the date from 01.01.1900 (01 Jan 1900) and assigns the serialization as 1. So the date 01.10.1900 (10 Jan 1900) will be converted to number 10 in Excel and 03.31.2018 (31 Mar 2018) will be serialized as 43190. Similarly time information is saved as decimal internally. For example, 0.5 indicates midday, 0.25 is 6AM and 0.75 is 6PM in Excel.

Now let us do a math for 31-Mar-2018 9:45AM. Open an Excel sheet and enter 31-Mar-2018 in one cell. Right click and choose “Format Cells…”. Under “Number” tab, select the date format (without time) for this cell. Enter 31-Mar-2018 09:45:00 AM in another cell and format this cell as date showing time. Now copy the two cells and paste in another row. Select both cells, right click and format them as plain numbers. You can choose no decimal for the first cell and 5 decimals for the second cell. It should look like below indicating 31-Mar-18 9:45 AM = 43190.40625 in Excel.

You can simply enter 43190.40625 in a cell and change the format to date with time. Excel will automatically convert the number into a date format with time as 31-Mar-18 9:45 AM. Remember, the number without any decimals is counted as 00:00:00 in time format. Only decimal is counted as time in Excel.

Calculations of Date and Time in Excel

Let us add some days to an existing date. For example, adding 10 days to 1/1/2018 should produce 1/11/2018. Enter 1/1/2018 in cell B3 and enter =10+B3 in cell B4 and press enter. You will see the result as 1/11/2018.

Just adding two dates may result in absolute addition in Excel which does not make sense. For example, when you add 1/31/18 and 3/1/18 will result in 4/2/36. But you can subtract two dates and show the difference of days in number format. For example, 3/1/18 – 1/31/18 will show the result as 29 days.

Similar to dates, you can add or subtract two cells in time format. Ensure you are always subtracting lower value from higher value and the format of the cells are in time. For example, 18:00 – 15:00 should result in 3:00.

Instead of working with cell reference, you can use direct date or time within double quotes for calculation like below:

=”12/24/2018″-“12/05/2018” (you will get 19 as result)
=”20:00″-“C9” (you will see 18:45 as result as cell C9 has the value 1:15)

Using NOW() and TODAY() for Calculations

You can also use two functions for date and time calculations. Now() is used to add or subtract time from the current time and Today() is used to add or subtract date from current date. Let us show some examples:

Ensure the cell is formatted as time format before using NOW() function and use the time for addition or subtraction within double quotes.

=NOW() will return the current time

=NOW()-“02:00” will subtract two hours from current time

=NOW()+”03:00″ will add three hours to current time

Ensure the cell is formatted as date format before using TODAY() function:

=TODAY() will return today’s date

=TODAY()-3 will subtract three days from today

=TODAY()+5 will add five days from today

Basic Date Functions

Microsoft Excel has the following date functions – TODAY(), YEAR(), MONTH(), DAY() and WEEKDAY(). The use case is shown as below. Enter =TODAY() function and press enter key on the cell C4. You can use all other functions with reference to the cell C4.

The WEEKDAY() function has an additional parameter return type. This is required to determine the first working day of the week. When you enter =WEEKDAY(C4,2) in the above example, you will get 1 as answer. This is because 2 indicates the week starts from Monday through Sunday. Hence, Monday on the reference cell is returned as 1 indicating it is first working day.

There is also DAYS() function that allows you to calculate the number of days between any two given dates. The syntax is =DAYS(end_date,start_date).

Basic Time Functions

Similar to date functions, Excel also supports different time functions – NOW(), HOUR(), MINUTE() and SECOND().

DATE and TIME Functions

DATE function is simply used to collect year, month and date from different reference cells and show it in a date format. Similarly TIME is function to concatenate hour, minutes and seconds from difference reference cells.

Using DATEDIF Function for Date Subtraction in Excel

Besides all those formal date and time functions, Excel also supports an informal function called DATEDIF. As per Microsoft support document, this function still works to support the older workbooks migrated to Excel from Lotus 1-2-3. But it can produce incorrect results in different situations, so not recommended to use.

DATEDIF function helps to calculate the number of years, months or dates between any given two dates. The syntax is as follows:

=DATEDIF(start date, end date,unit)

The unit value can be one of the followings:

• Y – number of complete years between the start and end dates.
• M – number of complete months
• D – number of days
• MD – number of days between the start and end dates, ignoring years and months. This value tend to produce incorrect results.
• YM – number of months between start and end dates, ignoring days and years.
• YD – number of days between two given dates, ignoring years.

Examples of Date Subtraction Using DATEDIF:

Note that DATEDIF being a supportive function for migration purposes, you will not see the formula help text appearing when entering the function in a cell. If you received an Excel workbook showing numbers in date column, then you can convert to proper dates using cell formatting.