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”
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.
Excel Function ISERROR Microsoft Excel “ISERROR Function” is a Logical Function and it is used to check if cell contains any “ERROR”. “ISERROR Function” is used as a test to validate if cell contains any…
Print Row Header on Each Page in Excel means that when you print your worksheet, the selected row (often the top row with column headers) will repeat at the top of every printed page. This ensures…
ROWS function is used to get the total count of rows in an array or in cells range in an excel worksheet.
Understand the basic Different between Excel Online vs Excel Desktop. Excel Online is like a simpler version of Excel that you can use in your web browser. It has most of the important features you need, but it doesn’t have advanced things like macros. This article dives into the differences between Excel Online and the excel Desktop version to help you understand what each one is good at and where they fall short.
Watch: How to use MODE & MODEIF Function in Excel? What is MODE Function? In Excel, the “MODE” function is a statistical tool that identifies and returns the most frequently occurring value within a set…