How to subtract dates, months, and years in Excel - Calculate the distance between two dates in Excel
In the process of processing data in Excel you need to subtract days, months and years in Excel. You do not know how to calculate and use what function? So invite you and TipsMake.com to learn how to calculate the distance between two dates in Excel, except for days, months and years in Excel below.
How to subtract dates, months, and years in Excel - Calculate the distance between two dates in Excel Picture 1
Here TipsMake.com has shared with you how to subtract days, months and years in Excel, please follow along.
Calculate the distance between two dates in Excel
Subtract the direct date
Assuming that in cell A1 is the start date, B1 is the end date, then you can subtract the date directly by entering the formula = B1-A1 the result will be the number of days. Because in Microsoft Excel stores every day as a unique serial number starting with 1 representing 1 January 1900.
How to subtract dates, months, and years in Excel - Calculate the distance between two dates in Excel Picture 2
Subtract dates using the DATEDIF function
Another way to help you subtract dates is to use the DATEDIF function with the syntax:
= DATEDIF (start date, end date, "d")
Note: the end date must be greater than the start date otherwise the function will error.
How to subtract dates, months, and years in Excel - Calculate the distance between two dates in Excel Picture 3
Subtract dates using the DATE function
Using the DATE function is a bit more complicated, the formula for the DATE function is as follows:
= DATE (year1, month1, day 1) -DATE (year, month, day)
Example subtracting date: May 22, 2019 for May 13, 2019.
How to subtract dates, months, and years in Excel - Calculate the distance between two dates in Excel Picture 4
Subtract the date from the current date
To subtract dates from the current date to the date in cell A1 , you use TODAY () instead of the current date, and you can also subtract directly with the formula:
= TODAY () - A1
Note: you need to format the cell containing the formula = TODAY () - A1 is Number .
How to subtract dates, months, and years in Excel - Calculate the distance between two dates in Excel Picture 5
Or you can use the DATEDIF function as follows:
= DATEDIF (A1, TODAY (), "d")
How to subtract dates, months, and years in Excel - Calculate the distance between two dates in Excel Picture 6
Distances are in weeks
Calculate the distance in weeks, you use the DATEDIF function with the following syntax:
= DATEDIF (start date, end date, "d") / 7
How to subtract dates, months, and years in Excel - Calculate the distance between two dates in Excel Picture 7
The distance in months
Calculate the distance in months using the DATEDIF function with the following syntax:
= DATEDIF (start date, end date, "m")
For example:
How to subtract dates, months, and years in Excel - Calculate the distance between two dates in Excel Picture 8
The distance in years
Use the DATEDIF function to calculate the distance in years with the syntax:
= DATEDIF (start date, end date, "y")
For example:
How to subtract dates, months, and years in Excel - Calculate the distance between two dates in Excel Picture 9
So, here TipsMake.com shared with you how to calculate the distance between two dates in Excel, calculate the distance in weeks, months and years. Hope you will understand and apply the calculation accordingly. Good luck!
You should read it
- How to format dates in Excel
- DATEDIF function - The function calculates the number of days, months, years between 2 dates in Excel
- Wings calculates the number of days, the difference between two dates in Excel
- How to sort dates in ascending and descending ways in Excel
- Instructions on how to fix reverse dates in Excel
- How to calculate time in Excel: Add and subtract time
- Format dates in Excel
- Share how to fix dates being converted to numbers in Excel
- How to convert numbers to dates in Excel
- Instructions on how to insert the current date in Excel
- EDATE function - Add and subtract months to a specified date in Excel
- How to fix reverse date errors in Excel
May be interested
Instructions for cutting strings, concatenating strings in Excel
How to get data from one sheet to another depends on the condition
Kutools for Excel Download, install and use instructions for new users
How to copy sheet from Excel file to another Excel file
Show Sheet Tab in Excel when hidden
How to cut and split images in PDF files simply and easily