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 19th Sep 2019 and output will be 19th 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 19th Sep 2019 and output will be 19th 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 19th Sep 2019 and output will be 19th April 2019 (i.e. Sep Month (i.e. 9th Month) -5 Months= April (i.e. 4th 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 19th Sep 2019 and output will be 19th Feb 2020 (i.e. Sep Month (i.e. 9th Month) +5 Months= Feb (i.e. 2th 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 19th Sep 2019 and output will be 14th Sep 2019 (i.e. 19th Sep -5 Days= 14th 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 19th Sep 2019 and output will be 24th Sep 2019 (i.e. 19th Sep +5 Days= 24th 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”
Generate Random Numbers in Excel Using RAND 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…
INDEX function is used to get the value from a cell range or table, function returns the value from a table where row and column intersect with each other.
CONCATENATE function is used for combining two or more Microsoft Excel strings into one. The output of the function returns as a combined string in new cell.
You must have faced a condition when your data cells contain extra spaces, leading spaces or trailing spaces and you wanted to remove these extra spaces to standardize the data. So here you may use…
“NETWORKDAYS” function is very helpful feature in the Microsoft excel to calculate the working days from a particular period excluding “Saturday and Sundays”. NETWORKDAYS function subtract the Start Day from the End Date provided.
Microsoft Excel “TODAY” function is used to get the current Date. It is very useful function and can be used in many ways. “TODAY Function” does not have any argument that makes this easy to apply and implement.