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

ON	1 Day
TN	2 Day
SP	2 Day
SN	2 Day
1W	7 Day
2W	14 Day
3W	21 Day
1M	1 Month
2M	2 Month
3M	3 Month
4M	4 Month
5M	5 Month
6M	6 Month
7M	7 Month
8M	8 Month
9M	9 Month
10M	10 Month
11M	11 Month
1Y	1 Year
15M	15 Month
18M	18 Month
21M	21 Month
2Y	2 Year
3Y	3 Year
4Y	4 Year
5Y	5 Year
6Y	6 Year
7Y	7 Year
8Y	8 Year
9Y	9 Year
10Y	10 Year
15Y	15 Year
20Y	20 Year
25Y	25 Year
30Y	30 Year

<colgroup><col span="2"></colgroup><tbody>
</tbody>

 

[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.