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

[MrPink1986]

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]

 

[MrPink1986]

Return weekday in formula

Hi - I have the below formula for which i wish to return a weekday date value in my cell. I am using the weekday formula however this does not change the resultant date. I am almost there I think with the formula however if someone could advise on where I am going wrong that would be great. Weekday is Mon to Fri to be clear.

=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)))),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)))))

Also further to this I dont want the returned date to be Dec 25th of Jan 1st if the day is a weekday (Mon-Fri) I want this to move forward 1 day or to the next business day - how could I incorporate this into the formula?

 

[Special-K99]

This handles weekends to Mondays...

This is the same formula in E1

in E1
=IF(D1="Day",A1+C1,IF(D1="Month",EDATE(A1,C1),(TEXT(A1,"dd/mm/")&(YEAR(A1)+C1))+0))

in F1
=E1+LOOKUP(WEEKDAY(E1),{1,2,3,4,5,6,7},{1,0,0,0,0,0,2})

Use F1 as the result
This takes E1 and adds either 0 or 1 or 2 depending on the day to bring Sat+Sun to a Mon

Thinking about the Xmas / New Year Day requirement...

 

[steve the fish]

Re: Return weekday in formula

Im not sure you have said what you want? Are you attempting to return the date itself if its a weekday or the monday after if its a weekend?

 

[Special-K99]

OK, untested but this should handle weekends to Mondays and the Xmas Day / New Years Day change too.
This getting long now...

in F1
=E1+IF(AND(OR(TEXT(E1,"ddmm")="2512",TEXT(E1,"ddmm")="0101"),WEEKDAY(E1)>=2,WEEKDAY(E1)<=6),LOOKUP(WEEKDAY(E1),{1,2,3,4,5,6,7},{0,1,1,1,1,3,0}),LOOKUP(WEEKDAY(E1),{1,2,3,4,5,6,7},{1,0,0,0,0,0,2}))

So take E1 and if the date is 25 Dec or 1st Jan and weekday is from Mon (2) to Fri (6) add an offset of 3 if Fri or 1 for other weekdays.
Otherwise add an offset of 1 if Sun or 2 if Sat

 

[MrPink1986]

Re: Return weekday in formula

Hi - thanks for the reply. Apologies on not been clear. This formula is looking up values I have in two columns and based of today will move the date forward by a number of days, months or years. So this formula returns a date and it is this date which I do not want to be a weekend date and I want it to return the Monday after the date returned in the formula if it is a Sat or Sun.
I hope that makes sense.

 

[Joe4]

I have merged your two threads dealing with the same question together.

 

[MrPink1986]

OK, untested but this should handle weekends to Mondays and the Xmas Day / New Years Day change too.
This getting long now...

in F1
=E1+IF(AND(OR(TEXT(E1,"ddmm")="2512",TEXT(E1,"ddmm")="0101"),WEEKDAY(E1)>=2,WEEKDAY(E1)<=6),LOOKUP(WEEKDAY(E1),{1,2,3,4,5,6,7},{0,1,1,1,1,3,0}),LOOKUP(WEEKDAY(E1),{1,2,3,4,5,6,7},{1,0,0,0,0,0,2}))

So take E1 and if the date is 25 Dec or 1st Jan and weekday is from Mon (2) to Fri (6) add an offset of 3 if Fri or 1 for other weekdays.
Otherwise add an offset of 1 if Sun or 2 if Sat

Perfect - this worked a treat and solved my problem, thanks for your help.