How To Add Days, Months & Years to Dates and Time in Excel

Today, I am going to equip you with a few formulas that will enable you to add Dates and Times in your Excel worksheet.

These are particularly useful for financial analysts because it helps them play around with and manipulate time periods in their financial models. For example, while projecting cash flows, you may want to project them monthly, quarterly, semi-annually, or annually. All you have to do then is use these formulas and add 1, 3, 5, or 11 to your dates, and you are set.

With that foundation, let’s jump in.

Add Days, Months--& Years to Dates In Excel

Add Days to a Date in Excel

We all know that Excel stores dates as serial numbers. There is no magical formula that Excel uses to assign serial numbers to dates, though. The serial numbers are assigned such that January 1, 1900 is considered as 1, and thereafter the numbers increment with each day. Based on the same logic, Dec 25, 2001, corresponds to 37,250 because it is 37,249 days after January 1, 1900.

Just for this reason, when you want to add a certain number of days to a date in Excel, you can just put in the number of days you want to add and you will have your output date.

We will use the following formula (which is actually just plain addition) to add 31 days to a date:

=A2 + 31

Add_days_to-dates-in-excel

Excel will add 31 days to 12/25/2001 (or 37250 as a serial number) and arrive at the serial number 37281, i.e. the serial number for 01/25/2002.

Add Months to a Date in Excel

To add months to a given date in your worksheet, we will use the EDATE function.

Syntax for the EDATE function:

=EDATE(start_date, number_of_months)

Arguments:

start_date: This is a required argument where you will refer to a cell containing the date.
number_of_months: This is a required argument where you may enter a number or refer to a cell containing the number of months you want to add to the date.

In our example, we will try to add 2 months to a date and for that, we will use the formula:

=EDATE(A2, 2)

Add_months_to-dates-in-excel

Instead of writing ‘2’ in the number of months argument, we could just as well refer to a cell containing the number 2. Also, ensure that the output cell is formatted to display a date.

Add Years to a Date in Excel

We will use the DATE function for adding years to a date in Excel. If you are not familiar with the DATE function, go check out our tutorial on the DATE function. Along with the DATE function, we will also use the YEAR, MONTH, and DAY functions.

Alright, so this is the formula we are going to use to add 5 years to a given date:

=DATE(YEAR(A2) + 5,MONTH(A2),DAY(A2))

Add_years_to-dates-in-excel

Notice the +5 tucked away in the first argument? That is the number of years we want to add to our date. Alternatively, you could also reference a cell containing the number ‘5.’

The YEAR, MONTH, and DAY functions have a small role to play here. All they do is derive a specific component of a date. All these functions are nested into the DATE function. So, upon deriving their respective values, they will supply those values to DATE function as an argument.

In our case, here is what each function will derive:

  • YEAR(A2): 2001
  • MONTH(A2): 12
  • DAY(A2): 25

So, our DATE function at this point looks like this:

=DATE(2001 + 5, 12, 25)

Looks pretty simple now, right?

The DATE function will then just add 5 to 2001 and give us the final output as 12/25/2006.

Add Working Days to a Date in Excel

Now, onto some interesting formulas. Let’s say we want to add days to a given date. But, we would like Excel to skip over the weekends and holidays.

To accomplish this, we will use the WORKDAY function in Excel. This function will skip over weekends (Saturday and Sunday) by default, but you will need to supply a list of holidays (if any) as an argument in the WORKDAY function.

Syntax for the WORKDAY function:

=WORKDAY(start_date, add_days, holidays)

Arguments:

start_date: This is a required argument where you will refer to a cell containing the date.
sdd_days: This is a required argument where you may enter a number or refer to a cell containing the number of days you want to add to the date.
holidays: This is an optional argument where you may supply a list of holidays by referencing a range of cells that contain the list of holidays.

The formula we will use in our example is:

=WORKDAY(A2, 60, D2:D4)

Add_working_days_to-dates-in-excel-004

What we are doing in this example is trying to compute the date that falls 60 working days after 12/25/2001, provided that there is 1 holiday in December 2001, and 2 in January 2002.

Notice that we supplied the list of holidays separately, while the weekends were factored in by the formula by default.

The return we get is 03/21/2002, which is the date that falls 60 working days after 12/25/2001.

The formula also works if you want to compute working days in reverse and arrive at a date that falls a certain number of days before 12/25/2001. For example, entering -60 instead of 60 in the add_days argument will return 10/02/2001.

Add Hours, Minutes, or Seconds to a DateTime in Excel

We are now ready to take on some larger formulas. Let’s try to play around with some DateTimes and see how we can manipulate them.

Add Hours to a DateTime in Excel

In this example, we will use 3 functions to build a formula for adding 30 hours to a DateTime. The functions that we are going to use are – TIME, MOD, and INT.

The formula would be:

=TIME(MOD(30, 24), 0, 0) + A2 + INT(30/24)

Add_Hours-to-Datetime-in-excel

Okay, let’s disassemble this formula one function at a time.

We will start with the simple ones first. Cell A2 is just the starting date that we want to add the hours to.

Refer to our example #1 and see how all it takes to add days to a date in Excel is a ‘+’ and the number of days. That is what the INT function is doing here. If the hours exceed 24, it will add a day to the date so our final output is adjusted accordingly. This means that if you are adding less than 24 hours, this component will not have any effect on the formula because it will return 0.

With the TIME function, we are instructing Excel to add a certain number of hours to the time in cell A2. The second and third arguments are ‘0’ because we do not wish to add any minutes or seconds to the time just yet.

The first argument is where we enter the number of hours, but we will use a MOD function to compute the number of hours to be added.

MOD function gives us the remainder from a certain division. In our case, we divide 30 by 24, which gives us 6. If you had slept through your Class 12 Maths class, just think of it like this (if you know how modulus works, feel free to skip to the next part):

  • When the number of hours to be added is below 24, the MOD function returns the same number (if the number were 15, MOD would return 15).
  • When the number of hours to be added is 24, the MOD function returns 0.
  • When the number of hours to be added (X) is more than 24 but less than 48, the MOD function returns (X – 24).
  • The MOD function will return 0 again if you add 48 hours, and beyond 48 hours, it will continue to deliver an outcome like so: (X – 48). At 48, 72, 96 hours (i.e., after every 24 hours), the MOD function will return a 0, and beyond those numbers, the MOD function will return: (X – relevant multiple of 24).

If you find it difficult to wrap your head around, just read the first 3 points, and that will suffice.

Okay, so we have come a long way in dismantling this formula. Let’s take stock of where we stand:

=TIME(6, 0, 0) + A2 + 1

We know the MOD function will return a ‘6,’ and we know the INT function will return a ‘1.’

Essentially, we have now asked Excel to pick up the DateTime in cell A2, add 1 day to it (via the INT function), and add 6 hours (via the TIME and MOD function).

The final output, of course, is 12/26/2001 6:00:00 AM.

Add Minutes to a DateTime in Excel

Let's now try to add 2500 minutes to a DateTime in Excel. A lot of what we discussed in the previous example remains unchanged here. So, we will first look at what needs to be done differently.

Point of difference #1: The most basic difference in our next formula will be the denominators used. In the previous example, we used 24 since we wanted to convert days to hours. This time around, we will use 1440 as our denominator to convert days into minutes since there are 1440 minutes in a day.

Point of difference #2: The second difference is that instead of 1, we will use 2 INT functions in the next formula.

Point of difference #3: We will also use 2 MOD functions instead of 1.

Why the difference?

Let’s circle back to the “why” after setting up our example.

Here is the formula we will use:

=TIME(INT(MOD(2500, 1440)/60), MOD(2500, 60), 0) + A2 + INT(2500/1440)

Add_Minutes-to-Datetime-in-excel

Let’s nail the easy ones first. We know cell A2 is the date we want to add minutes to. The INT function at the tail of the formula returns 1 (because we need to add 1 day) since the number of minutes we are adding exceeds 1440 but does not exceed 2880.

Alright, so the complexity is tucked inside the 3 arguments of the TIME function. Since we now know what the TIME function does, let’s jump onto the role these arguments play in there.

The first argument has, in total, 3 operations and 2 functions.

The first operation is the computation of the MOD function. We know the pattern that the output of the MOD function follows, right? So, the MOD function will give us 1060 (or 2500 – 1440).

The second operation is the division of the MOD function’s output by 60. So, why is there a 60 in the formula? Well, when the MOD function gives us 1060, it is trying to tell us that you need to add these many minutes to your DateTime to get the final output. To convert it to hours, we divide it by 60, which gives us ~ 17.67 hours.

Perfect. What if I tell you I will be 3.5 hours late today? You will interpret it as 3 hours and 30 minutes, right?

That is precisely what the INT function is there for in the first argument. Any time you see hours being written with a decimal, it is interpreted as minutes. But remember, we cannot have minutes in the hours' argument of the TIME function, so we must remove it by eliminating the value after the decimal using the INT function. Finally, the hours' argument has the value ‘17.’

The second argument has just one operation and one function.

In the second argument, we need the number of minutes that need to be added to our final output. To do this, we will use another MOD function.

No other complexities, though (or maybe give it 10 seconds). Just the good ol’ MOD function with dividend as 2500 and the divisor as 60. Right?

Wait, why 60?

In the previous argument, we used the INT function to remove the minutes component from the return. So, we are going to transfer it to this argument because this is where minutes belong.

If this is difficult to understand, let me reverse engineer it for you. Remember how in the previous argument, we used the INT function to remove .67 after the 17 hours? Those 0.67 hours are what we are trying to add here. 0.67 (or 2/3rd) hours is 40 minutes.

Guess what MOD (2500, 60) gives us?

40!

The third argument is 0, at least for now.

This is what our formula looks like at this point:

=TIME(17, 40, 0) + A2 + 1

Looks pretty simple now, right? Essentially, the formula will add 1 day, 17 hours, and 40 minutes to 12/25/2001 12:00:00 AM and gives us our final output as 12/26/2001 5:40:00 PM.

Add Seconds to a DateTime in Excel

Now, let's take a step further and try to add 90000 seconds to the given date. The formula remains the same across all three scenarios for adding hours, minutes, or seconds—with just some logical tweaking involved.

We will use the same formula we used previously, with slight modification, like so:

=TIME(INT(MOD(90000, 86400)/3600), INT(MOD(90000, 3600)), MOD(90000, 60)) + A2 + INT(90000/86400)

Add_Seconds-to-Datetime-in-excel

I know the formula looks as big as the Everest, but let’s take it one step at a time.

Our objective is to add 90,000 seconds to the DateTime given in cell A2. The logic for the formula remains the same, except for the arguments in the TIME function.

The hours argument

As always, we want this argument to end up with a value that is in terms of hours.

The MOD function in the hours argument will return 3600 (90,000 – 86400). In other words, we must add 3600 seconds to the time component of our DateTime to get our final output. However, to convert 3600 seconds to hours, we must divide it by the number of seconds in an hour, like so:

$$3600 seconds \over [(3600 seconds)/(1 hour)]$$

OR

$$3600 seconds \times [(1 hour)/(3600 seconds)]$$

This gives us 1 hour in the hours argument.

The minutes argument

Recollect how we tackled the second argument in the previous example where we had eliminated 0.67 hours from the first argument since this value actually represents minutes and should go in the second argument?

Well, this time around, our hours argument has returned just 1 hour—no decimals. This means we need to add nothing in the minutes argument.

If you check, the MOD function in the second argument will return a 0. If this baffles you, scroll back up and read the four pointers where we discussed how to compute the output for a MOD function. Basically, since 90,000 is perfectly divisible by 3600, it returns 0.

The seconds argument

Again, since we have just 1 full hour in the first argument and have no seconds component, the MOD function here will return a 0.

Add Hours, Minutes, or Seconds to a Time in Excel

After the extensive examples we have discussed thus far, this may feel like child’s play. But, let’s quickly run through some examples.

Add_Hours-Minutes-Seconds-To-Time-in-excel

Add Hours to a Time in Excel

By now, you may be a TIME function ninja. So, see if you can figure out the formula before you look at it.

The formula we will use here:

=TIME(1, 0, 0) + A2

All we are doing here is instructing Excel that we want to add 1 hour (via the TIME function) to the Time in cell A2.

Add Minutes to a Time in Excel

We will use the same formula, but set the minutes argument to a certain number instead of 0 to add that many minutes, like so:

=TIME(1, 20, 0) + A2

This time around, we are adding 1 hour and 20 minutes to the TIME in cell A2.

Add Seconds to a Time in Excel

Again, no change:

=TIME(1, 20, 45) + A2

All we did differently this time is that we included the seconds argument, so Excel will add 1 hour, 20 minutes, and 45 seconds to the Time in cell A2.

That marks the end of our methods to add Dates, Time, and DateTime in Excel. Go ahead and use these formulas in your spreadsheet at work or just for fun. By the time you champion these, we will come back with another set of formulas for you to master.

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.