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.
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.
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.
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.
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 .
Or you can use the DATEDIF function as follows:
= DATEDIF (A1, TODAY (), "d")
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
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:
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:
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!