Date formula that includes the year that a given day/month will occur??

[TGarrett]

Hi all,

For the record, I know, the obvious answer is "every year." but seriously, I have a question. I am trying to write a formula the give me a date that a given report will be due. The reports each have a set annual due date based on the employee's position. An example would be that an intern's performance report is due March 31st every year while a junior manager's is due every June 30th. Part of my tracking spreadsheet has a column for when the report is due next. I have a column that has a drop down list for the employee positions and plan to use a simple set of "IF" qualifiers to determine the next due date of the report and have that entered into the given cell. The only trouble I am having is getting it to figure out the right year for that date. I hope I am somewhat clear in my description. Any help would be appreciated. Thanks.

Tom

 

[Tyler Hilton]

Maybe use the EDATE formula:

=EDATE(A1,12)

Assuming your date is in cell A1, this will give you the exact date 12 months from that date. So for example, if 5/1/2016 was in cell A1 this formula would result in 5/1/2017. Not sure if this is what you're looking for, but you could put conditional IF statements around the number of months. Hope this helps.

Best,

Tyler Hilton

 

[TGarrett]

Tyler,

Thanks for taking a stab at it but I don't think that will work for my situation. I'll try to break it down a little more...

A1 has the employee name, B1 has the employee's position, C1 has the status of the report (draft, in coordination, completed, etc...) and D1 has the date that the report is due to the boss. Let's say that "Bill" is an intern (all intern reports are due the 31st of March each year), the current date is January 3, 2016, and his report is still being drafted by his supervisor. Given these variables, the date in D1 should be calculated as March 31, 2016. Fast forward to April 1st. The report still isn't completed (C1 is anything but "Completed") so the date in D1 should still say that it is due March 31, 2016. Now, on April 5, 2016 the report is completed and the status in C1 is changed to "Completed". The date in D1 needs to roll over a year and now read March 31, 2017.

This really seemed like it would be a piece of cake in my head. That is until a sat down in front of the keyboard and asked how the heck am I going to do that? LOL.

Tom

 

[Claymationator]

Give this a try.

=IF(AND(TODAY()>=DATE(YEAR(TODAY()),3,31),C1<>"Completed"),DATE(YEAR(TODAY()),3,31),EDATE(DATE(YEAR(TODAY()),3,31),12))

 

[Tyler Hilton]

Very nice Claymationator! This allows for flexibility on the number of months between due dates as well.

 

[TGarrett]

Thanks Claymationator!! That got me over the hump and was just what I needed. Thanks again.