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”
SUMIF function is used to get the “total sum” for number of times the criteria across range is met. SUMIF Function has two required arguments.
REPT function is used to repeat the text or cell reference to multiple times
MID function is used for extracting the mid characters from the available string. The output of the function returns the extracted characters in new cell.
LEN function is used for counting number of characters in available string. The output of the function returns the count in new cell.
SUM Function in Excel Excel is a mathematical spreadsheet where you can perform multiple calculations with the help of Excel Formulas. These are automated formulas which refreshes automatically once you refresh your data in a…
WEEKDAY function applies to a Date and returns the output for Day of the week. The output of the function varies from 0 to 7