Microsoft Excel stores dates and times in the form of serial numbers, which allows dates to be added and subtracted accurately. Below are various examples and useful formulas for adding and subtracting data in Excel.
Add or subtract data in Microsoft Excel
Microsoft Excel stores dates as serial numbers, which allows you to add/subtract dates and use dates in formulas and calculations.
The baseline for serializing data in Microsoft Excel begins with the “01. January 1900” which is assigned the serial number 1 in the Microsoft Excel program.
Dates later than the base date (January 1, 1900) are assigned sequential serial numbers based on the number of days from the original date.
For example, the serial number for “31. March 2018” is 43190 because that date is 43190 days after the base date of January 1, 1900.
Likewise, times are stored in decimal form in Excel – 0.5 represents noon, 0.25 represents 6am and 0.75 represents 6pm in Excel.
How to enter data in Excel
Before looking at the examples of adding and subtracting data in Excel, you might want to know the correct way to enter data in Excel.
To enter dates in Excel, you can enter January 1, 2018, January 1, 2018, or January 1, 2018. When you enter this format into an Excel cell, the program can easily detect that you are trying to enter a date and automatically apply the date format to the cell.
Whenever Excel recognizes that you’ve entered a date, it automatically aligns the entry to the right, as opposed to left-aligned text values.
If Excel doesn’t recognize what you’re typing as a date, and you see that the typing is left-aligned (like normal text), you can try typing the date again in one of the date formats above and it should correct the typing.
Subtract data in Microsoft Excel
To illustrate subtracting dates in Excel, let’s assume you have the “Start Dates” in column A and the “End Dates” in column B.
Now to calculate the number of days between the two dates, enter =B2-A2 in column C and press Enter on your computer keyboard.
After entering the formula in the first column, you can drag the formula down to all the remaining cells.
Add days to dates in Microsoft Excel
In this case, let’s assume that in column A you have the “start dates” and in column B you have the “number of days” to complete the task.
Now you can calculate the end dates by typing =A3+B3 in column C and press Enter on your computer keyboard.
Add weeks to date in Excel
Let’s assume you have the “Start Dates” in Column A and the “Number of Weeks” to complete the tasks in Column B. To get to the end dates, you can tap =A3+B3*7 and press Enter on the button.
Add months to date in Excel
You can use the EDATE function in Microsoft Excel to add months to specific dates. The syntax of the EDATE function is EDATE (start date, number of months).
As you can see in the image above, you can use the EDATE function to reference cells that contain the start date and the number of months required to complete the tasks.
Add years to date in Excel
In this case, let’s assume that you have the “Starting Dates” in column A and the “Number of Years” to complete the task in column B. To calculate the end dates, enter =EDATE(A3,12*B3) and press Enter on your computer keyboard.
Useful date and time functions in Microsoft Excel
The following are some of the most common date and time functions used in reports.
- TODAY() : Returns today’s date, updated automatically
- Ctrl +; Inserts today’s date as a static value that will not be updated on the next day
- Ctrl+Shift+; Inserts the current time
To enter the current date and time, press Ctrl+; Press the spacebar and then press Ctrl+Shift+;