Date Formula

[Rportz]

A	B	C
1	Friday, April 15, 2022	Monday, April 17, 2023
2

Enter date in B1; Returns date in C1:

Date to be returned, in C1, should be 12 months from date in B1, minus one day; however, if date in C1 falls on a Weekend, then the return date (c1) will be next business day.

*In the example above, the return date in C1 should show Monday, April 17, 2023.

 

[Fluff]

Why should it return the 17th when 14th April 2023 is a Friday?

 

[Rportz]

Why should it return the 17th when 14th April 2023 is a Friday?

When I enter 4/15/2022, 12 months minus one day returns Sunday, April 16, 2023. Since the 16th is a Sunday, I need it to return the next business day which is Monday, April 17, 2023.

 

[Fluff]

How can 1 day less than a year be the 16th rather than the 14th?

 

[Rportz]

When I enter 4/15/2022, 12 months minus one day returns Sunday, April 16, 2023. Since the 16th is a Sunday, I need it to return the next business day which is Monday, April 17, 2023.

 

[Rportz]

F15+(WEEKDAY(F15)=7)*2+(WEEKDAY(F15)=1)*1-1

This is the formula I'm using in the green cell. The green cell is returning Sunday instead of the next business day.

 

[Fluff]

That formula cannot return a date 1 year in the future. Assuming the blue cell is F15

 

[Rportz]

That formula cannot return a date 1 year in the future. Assuming the blue cell is F15

B15 is Blue Cell
C15 is the "12"
F15 is the date to the right that is not highlighted and has the following formula =EDATE(B15,C15)

 

[Fluff]

Ok, that makes more sense, but it still doesn't answer my question.
You said

Date to be returned, in C1, should be 12 months from date in B1, minus one day

2 months less one day from 15th April 22 is 14th April 23 which is a Friday.
So is your question wrong?

 

[Rportz]

Ok, that makes more sense, but it still doesn't answer my question.
You said

2 months less one day from 15th April 22 is 14th April 23 which is a Friday.
So is your question wrong?

I'm sorry. I need the green cell to return a date twelve months from the date entered in the blue cell, minus 1 day. If that date ends up landing on a weekend, then it needs to return the next business date.

Does this make sense?