Appearances are deceptive! That is especially true when it comes to the data that is in Date format in MS Excel. Though a date may appear in the DD-MM-YYYY format or fancies any other format for that matter, deep underneath MS Excel assigns a value to this date. To put it in simple terms, dates are just continuous serial numbers with 1 representing the 1st of January 1900.
In the above image, it is evident that when tried to look into the numerical equivalent for the 1st January 1900 in the format dropdown it shows the number ‘1’. Having established this conundrum let us now move on to finding out the different ways of adding days in MS Excel.
Adding Days in Excel:
Listed below are 5 examples to demonstrate the different ways to add the days. One can choose either of these based on whichever best suits the requirements at hand.
- Using Operand
- Using DATE Formula
- The Accelerator Way
- Using EOMONTH Formula
- Using TODAY Formula
Example 1 – Adding Days in Excel Using Operand
Beneath the skin, it is just a number so a simple addition operand shall serve the purpose of adding the days. Let us try adding 20 days to the 1st of January 1900. It could be done by starting with an equals (=) sign & clicking the cell which contains the date. Then include a plus (+) sign followed by the number 20 as shown below.
Hit ENTER & the new date 20 days after the selected date shall appear.
Example 2 – Adding Days in Excel Using the DATE Formula
Another way of constructing a date is using the DATE formula in which references would be given to each of the cells containing the value of the day, month & year – the basic constituents of the date. Have a look at the following example to get a fair idea.
Syntax for DATE formula is,
=DATE( year, month, date)
So, to add the days for any given date in this formula one can put a plus sign (+) following the cell referred to in the date portion of the formula.
Hit ENTER & the new date shall appear.
Example 3 – Adding Days in Excel Using The Accelerator Way
Using the infamous accelerator let’s add 20 days to the date in the below image.
Copy the count of days to be added using CTRL+C & move the active cell to J7 which has the date as shown below.
Hit the following keys in the same sequence for the Paste Values Dialog box to appear.
ALT – E – S – V – D
Hit ENTER & the new date shall appear!
Example 4 – Adding Days in Excel Using EOMONTH Formula
EOMONTH formula is used to get the last date of a month whose syntax is as follows.
=EOMONTH( start_date, months)
Where,
- start_date – reference date to which end of the month is to be determined
- months – count of months after which end of the month is to be determined
If one wants to add a few days to the result of the EOMONTH, here’s what is needed to be done.
Example 5 – Adding Days in Excel Using TODAY Formula
MS Excel has this fascinating feature of displaying today’s date when the following syntax is put into use.
=TODAY()
What if one wants to add a certain number of days to the current date? Just add a plus (+) sign followed by the required number at the end of the formula.
Conclusion:
Now that we have reached the end of this article, here’s another that details on how to add months to a date in MS Excel. There are numerous other articles too in QuickExcel that can come in handy for those who are in looking to level up their skills in MS Excel. Adios!