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
We have got many instances where we needed to generate a random database or values. Rand function is very useful for the users who creates random database for various types of working and analysis. Rand…
Microsoft Excel “DAY, MONTH, YEAR Functions” are date related functions helps to extract the Day, Month or Year from a Date.
In an “IF function” there will be two output i.e. TRUE or FALSE since either the statement will be “TRUE” or “FALSE”. If the statement is matching or correct, then output will be “TRUE” or if the statement is not matching or not correct then the output will be “FALSE
LOWER function is used for changing the format of any text or string to LOWER case.
SEARCH function is used to find “position of character or text” in an available cell and this function is NOT case sensitive.