Dates function also be used to subtract the Year, Month and Days from the existing dates. Sometimes we need to subtract specific period from the date. In case you are working on large database, this become tedious job for calculating date difference
Here we discussed the various scenarios where we can subtract the period from the dates. These are the simple formulas which will help you to perform these calculations with just few steps
We can subtract any number of years from the existing date and as part of example we will be following below Syntax to subtract the “5” years from the date
Syntax: =DATE(YEAR(A2)-5,MONTH(A2),DAY(A2))
In the above example, we are subtracting 5 years from 19^{th} Sep 2019 and output will be 19^{th} Sep 2014 (i.e. Year 2019 (-) 5 Years= 2014)
Adding Years in Dates: Below Syntax will be used to add “5” Years in the existing date
Syntax: =DATE(YEAR(A2)+5,MONTH(A2),DAY(A2))
In the above example, we are adding 5 years in 19^{th} Sep 2019 and output will be 19^{th} Sep 2024 (i.e. Year 2019 + 5 Years= 2024)
Below Syntax will be used to subtract “5” months from the date
Syntax: =DATE(YEAR(A2),MONTH(A2)-5,DAY(A2))
In the above example, we are subtracting 5 months from 19^{th} Sep 2019 and output will be 19^{th} April 2019 (i.e. Sep Month (i.e. 9^{th} Month) -5 Months= April (i.e. 4^{th} Month))
Below Syntax will be used to add “5” months in the date
Syntax =DATE(YEAR(A2),MONTH(A2)+5,DAY(A2))
In the above example, we are adding 5 months in 19^{th} Sep 2019 and output will be 19^{th} Feb 2020 (i.e. Sep Month (i.e. 9^{th} Month) +5 Months= Feb (i.e. 2^{th} Feb of next year))
Below Syntax will be used to subtract “5” days from the date
Syntax: =DATE(YEAR(A2),MONTH(A2),DAY(A2)-5)
In the above example, we are subtracting 5 days from 19^{th} Sep 2019 and output will be 14^{th} Sep 2019 (i.e. 19^{th} Sep -5 Days= 14^{th} Sep)
Below Syntax will be used to add “5” days in the date
Syntax =DATE(YEAR(A2),MONTH(A2),DAY(A2)+5)
In the above example, we are adding 5 days in 19^{th} Sep 2019 and output will be 24^{th} Sep 2019 (i.e. 19^{th} Sep +5 Days= 24^{th} Sep)
Calculating days between two different dates are most common function that is used in Microsoft excel. I saw many users tries various complex method to calculate the difference but this is really a simple math calculations
We just need to place an excel formula i.e. “= Latest Date – Oldest Date”
Please comment your questions or feedback related to this article.
Wanna learn more similar tricks. Please subscribe us or follow us from the left Social Media icon
REPT function is used to repeat the text or cell reference to multiple times
MATCH function performs lookup for a value in a range and returns its position sequence number as output. It has two required and one optional arguments
Microsoft Excel “DAY, MONTH, YEAR Functions” are date related functions helps to extract the Day, Month or Year from a Date.
ROW function is used to get the row reference number of the excel worksheet. ROW Function has only one argument i.e. reference,