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
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”
FIND function is used to find the position of text, or character in an available string.
TEXT function is used to change the formatting or appearances of the text. There are various types of formatting available.
Excel Function COUNTIF COUNTIF Excel Function is also one of the most used function in excel. This helps the user to calculate the number of counts based on single logic given by the user. You…
INT function is used to round down the numeric value to nearest integer. INT Function has one required argument i.e. number.
SUMPRODUCT function performs multiplication of numbers within arrays and then sum the values SUMPRODUCT function has array1, 2.. arguments.
MIN function is used to get the smallest number in range or list of values.MIN function has one required i.e. number1 and optional argument i.e. [number2]