Increment a date by a number of days, months or years

MrPink1986

Active Member
Joined
May 1, 2012
Messages
252
Hi,

I have a date which I will need to increment the date by a day, month or year from the actual date. This is based of a list of fields below and is different per the input. Also each resultant date has to be a business day i.e not a weekend day or a bank holiday. I will forgo the Bank Holiday requirement now.

I am using the formula - =DATE(YEAR($A$1),MONTH($A$1),DAY($A$1)) and hard coding the increment value in each row based of the value in B4 below.
Also once we get past SP in column A4 the formula changes to reference the date created by using the value at the SP value.

So if we put the results in to column E the formula will change to =DATE(YEAR($E$6),MONTH($E$6),DAY($E$6))
The date also needs to be in the format text(xx,"mm/dd/yyyy")

Any suggestions on how to create this in Excel of VIA a VBA solution macro would be appreciated

Inputs
A1
=today()

A4 B4
[TABLE="width: 188"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]ON[/TD]
[TD]1 Day[/TD]
[/TR]
[TR]
[TD]TN[/TD]
[TD]2 Day[/TD]
[/TR]
[TR]
[TD]SP[/TD]
[TD]2 Day[/TD]
[/TR]
[TR]
[TD]SN[/TD]
[TD]2 Day[/TD]
[/TR]
[TR]
[TD]1W[/TD]
[TD]7 Day[/TD]
[/TR]
[TR]
[TD]2W[/TD]
[TD]14 Day[/TD]
[/TR]
[TR]
[TD]3W[/TD]
[TD]21 Day[/TD]
[/TR]
[TR]
[TD]1M[/TD]
[TD]1 Month[/TD]
[/TR]
[TR]
[TD]2M[/TD]
[TD]2 Month[/TD]
[/TR]
[TR]
[TD]3M[/TD]
[TD]3 Month[/TD]
[/TR]
[TR]
[TD]4M[/TD]
[TD]4 Month[/TD]
[/TR]
[TR]
[TD]5M[/TD]
[TD]5 Month[/TD]
[/TR]
[TR]
[TD]6M[/TD]
[TD]6 Month[/TD]
[/TR]
[TR]
[TD]7M[/TD]
[TD]7 Month[/TD]
[/TR]
[TR]
[TD]8M[/TD]
[TD]8 Month[/TD]
[/TR]
[TR]
[TD]9M[/TD]
[TD]9 Month[/TD]
[/TR]
[TR]
[TD]10M[/TD]
[TD]10 Month[/TD]
[/TR]
[TR]
[TD]11M[/TD]
[TD]11 Month[/TD]
[/TR]
[TR]
[TD]1Y[/TD]
[TD]1 Year[/TD]
[/TR]
[TR]
[TD]15M[/TD]
[TD]15 Month[/TD]
[/TR]
[TR]
[TD]18M[/TD]
[TD]18 Month[/TD]
[/TR]
[TR]
[TD]21M[/TD]
[TD]21 Month[/TD]
[/TR]
[TR]
[TD]2Y[/TD]
[TD]2 Year[/TD]
[/TR]
[TR]
[TD]3Y[/TD]
[TD]3 Year[/TD]
[/TR]
[TR]
[TD]4Y[/TD]
[TD]4 Year[/TD]
[/TR]
[TR]
[TD]5Y[/TD]
[TD]5 Year[/TD]
[/TR]
[TR]
[TD]6Y[/TD]
[TD]6 Year[/TD]
[/TR]
[TR]
[TD]7Y[/TD]
[TD]7 Year[/TD]
[/TR]
[TR]
[TD]8Y[/TD]
[TD]8 Year[/TD]
[/TR]
[TR]
[TD]9Y[/TD]
[TD]9 Year[/TD]
[/TR]
[TR]
[TD]10Y[/TD]
[TD]10 Year[/TD]
[/TR]
[TR]
[TD]15Y[/TD]
[TD]15 Year[/TD]
[/TR]
[TR]
[TD]20Y[/TD]
[TD]20 Year[/TD]
[/TR]
[TR]
[TD]25Y[/TD]
[TD]25 Year[/TD]
[/TR]
[TR]
[TD]30Y[/TD]
[TD]30 Year[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
1. Make your table into three columns. Make column B the number to add, so column C contains Day Month or Year

2. In B1 put your code ON, TN, SP etc

3. In C1 =VLOOKUP(B1,A$4:C$1000,2,0)
In D1 =VLOOKUP(B1,A$4:C$1000,3,0)

4. In E1
=IF(D1="Day",A1+C1,IF(D1="Month",EDATE(A1,C1),(TEXT(A1,"dd/mm/")&(YEAR(A1)+C1))+0))
 
Last edited:
Upvote 0
I made a small example to parse the number and unit of measure from a cell and then to add to the input date appropriately... In my example, A4 has the value "30 Year"...

Getting Number Value (B4):

Code:
=LEFT(A4,SEARCH(" ",A4)-1)

Getting Unit Of Measure (C4):

Code:
=RIGHT(A4,LEN(A4)-SEARCH(" ",A4))

Adjusting Input Date (D4):

Code:
=IF(C4="Day",DATE(YEAR(A1),MONTH(A1),DAY(A1)+B4),IF(C4="Month",DATE(YEAR(A1),MONTH(A1)+B4,DAY(A1)),DATE(YEAR(A1)+B4,MONTH(A1),DAY(A1))))

f6AtIrA.png
 
Upvote 0
Hi - thanks for the response - I dont follow fully - where does your first formula go?

[TABLE="width: 268"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD="align: right"]30/05/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ON[/TD]
[TD]1 Day[/TD]
[TD]=RIGHT(A4,LEN(A4)-SEARCH(" ",A4))[/TD]
[TD]=IF(C4="Day",DATE(YEAR(A1),MONTH(A1),DAY(A1)+B4),IF(C4="Month",DATE(YEAR(A1),MONTH(A1)+B4,DAY(A1)),DATE(YEAR(A1)+B4,MONTH(A1),DAY(A1))))[/TD]
[/TR]
[TR]
[TD]TN[/TD]
[TD]2 Day[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SP[/TD]
[TD]2 Day[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SN[/TD]
[TD]2 Day[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1W[/TD]
[TD]7 Day[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2W[/TD]
[TD]14 Day[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3W[/TD]
[TD]21 Day[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1M[/TD]
[TD]1 Month[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2M[/TD]
[TD]2 Month[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3M[/TD]
[TD]3 Month[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4M[/TD]
[TD]4 Month[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5M[/TD]
[TD]5 Month[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6M[/TD]
[TD]6 Month[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7M[/TD]
[TD]7 Month[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8M[/TD]
[TD]8 Month[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9M[/TD]
[TD]9 Month[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi - thanks for the response - I dont fully follow your proposed idea. My table needs to be split into three Column B number of days, months or years, column C is day, month or year? What goes into column A?
 
Upvote 0
Hi - thanks for the response - I dont fully follow your proposed idea. My table needs to be split into three Column B number of days, months or years, column C is day, month or year? What goes into column A?


Column A4 onwards contains ON (and other codes)
Column B4 onwards contains numbers (1,2, 2, 2, etc)
Column C4 onwards contains Day, Month, Year

The table is now split into three columns.
A1 contains your code (ON, SP etc)

In B1 put your code ON, TN, SP etc

In C1 =VLOOKUP(B1,A$4:C$1000,2,0)
In D1 =VLOOKUP(B1,A$4:C$1000,3,0)

In E1
=IF(D1="Day",A1+C1,IF(D1="Month",EDATE(A1,C1),(TEXT(A1,"dd/mm/")&(YEAR(A1)+C1))+0))
 
Upvote 0
Perfect that made it simpler to understand thanks for the reply. I know have my dates populated however I know have another parameter I need to include. I only want the date returned to be a weekday (Mon - Fri) so if it lands on a Sat or Sun, it will move forward to the next weekday i.e that Monday. Any ideas on that one? I am aware of the =weekday formula though including it in your formula has not worked however.
 
Upvote 0
I have tried this formula -
Code:
=IF(WEEKDAY(IF(C7="Day",DATE(YEAR($D$6),MONTH($D$6),DAY($D$6)+B7),IF(C7="Month",DATE(YEAR($D$6),MONTH($D$6)+B7,DAY($D$6)),DATE(YEAR($D$6)+B7,MONTH($D$6),DAY($D$6)))))=2,IF(C7="Day",DATE(YEAR($D$6),MONTH($D$6),DAY($D$6)+B7),IF(C7="Month",DATE(YEAR($D$6),MONTH($D$6)+B7,DAY($D$6)),DATE(YEAR($D$6)+B7,MONTH($D$6),DAY($D$6))))-3,IF(C7="Day",DATE(YEAR($D$6),MONTH($D$6),DAY($D$6)+B7),IF(C7="Month",DATE(YEAR($D$6),MONTH($D$6)+B7,DAY($D$6)),DATE(YEAR($D$6)+B7,MONTH($D$6),DAY($D$6))))-1)

This however subtracts 1 day from all my resultant dates which is not what I want - I want this to be only effective if the resultant date is a weekend day - Sat or Sun...
 
Upvote 0
Finally - further to this requirement if the resultant date falls on either Christmas day 25th Dec or New years Day 1st Jan and these are a weekday I need the resultant date to be the next business day. So if Xmas day is a Monday the date should go to the 26th however if Xmas day is a Friday the date should go to the 28th
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top