Enter date based on another date

[MPFraser7]

The title doesn't say much so let me explain further.

The value in A2 is manual input and it could be any date in the calendar, from 1999 to 2017. In column B, I need to enter a formula that returns which ever comes first, November 6th or the day and month of A2 + 1 year. Refer to table for an example of desired results

Date1	Date2
09/07/2014	06/11/2014
07/11/2014	09/07/2015
10/07/2015	06/11/2015
07/11/2015	09/07/2016

<tbody>
</tbody>

 

[Joe4]

Based on your description, I do not understand how you arrived at the dates in the Date2 column for the second and fourth records.

Let's look at your second one. If your date is the 7th of November in year 2014, since it is after the 6th of November, according to your instructions, don't we want to add 1 year to the date, returning the 6th of November in the year 2015?

If so, then formula should look something like:

=IF(TEXT(A2,"mmdd")+0<=1106,DATE(YEAR(A2),11,6),EDATE(A2,12))

 

[barry houdini]

I'm assuming you are referring back to A2 for all rows? If so try this formula in B2 copied down

=MIN(DATE(YEAR(A2+1,MONTH(A$2),DAY(A$2)),DATE(YEAR(A2+55),11,6))

 

[Joe4]

I'm assuming you are referring back to A2 for all rows?

Ah yes, that makes sense.
I thought it meant add one year to whatever date was in column A.

 

[MPFraser7]

Here's the full scenario: An employee gets a salary increase on his anniversary date (date that he joined) as well as on November 7th, so an employee gets two increases in a year. I am trying to build a tool that would calculate the number of worked days at every increment. The date in A2 would be manually inputted by the employee and the rest would be automatically calculated using an excel formula. In the case above, the employee joined on July 7th 2014. Starting on that date, he would be paid at his base salary until his first increase, November 7th 2014. From July 7th 2014 to November 6th 2014 he was being paid base salary, and now starting on November 7th 2014 he's being paid base+1. His next increment would then be on his anniversary date, which is July 10th of 2015, so his base+1 stops on July 9th 2015 and his base+2 starts on July 10th.

I tried both formulas provided but none worked. I hope this provides more context as to how I'm getting my numbers. Thanks in advance for your help.

 

[Joe4]

I think one of things that was confusing was the label "Date1" and "Date2". They are really Start/End dates for a certain period.

If they started on July 9th, wouldn't there anniversary be on July 9th, and not July 10th?

 

[MPFraser7]

Yes you're right, I mislabelled the columns. It really is start/end dates. It would make more sense if it was July 9th and not July 10th.

 

[Joe4]

OK, to get it to look like your original grid, enter this formula in B2 and copy down column B:

=IF(TEXT(A2,"mmdd")+0<=1106,DATE(YEAR(A2),11,6),DATE(YEAR(A2)+1,MONTH($A$2),DAY($A$2)))

Then, in column A3, enter this formula and copy down copy A:

=B2+1

If we want to adjust the anniversary date back a day, just change the column B formulas like this:

=IF(TEXT(A2,"mmdd")+0<=1106,DATE(YEAR(A2),11,6),DATE(YEAR(A2)+1,MONTH($A$2),DAY($A$2)[B][COLOR=#ff0000]-1[/COLOR][/B]))

 

[barry houdini]

Ah, OK, now I understand...........

Joe, your solution works OK if the A2 date is before 7th November, but if it's 12/12, for example, I don't think it works, because B2 date then needs to be 6 November for the following year.

I think this works in all circumstances

A3 should remain =B2+1 copied down but now use this formula in B2

=DATE(YEAR(A2+55),11,6)

and this one in B3 copied down

=MIN(EDATE(A2,12+(TEXT(A2,"ddmm")="0711")*12)-1,DATE(YEAR(B2+56),11,6))

Note that taking it to it's logical conclusion, if somebody joins on November 6th he has only one day at the base salary then goes up to the next level the day after he starts........or he starts on 7th November and only has one raise per year?

 

[MPFraser7]

I can't get barry houdini's formula to work for some reason, so I am using Joe4's. It doesn't work if the A2 date is after Nov 7th but I can live with that. Thanks guys! I'm learning a lot with using dates in excel formulas.