Flagging event due dates

Greetings. My first post here.
I am a commercial pilot in Alaska. I am attempting to create a spreadsheet to track training/checkride due dates for our group of pilots. Here is how I would like to go about this. First, let me give you a bit of information about how the FAA tracks these events. In simplistic terms, if an event takes place on any day of a month, it comes due in any day of the sixth (or 12th month) following the month it was accomplished in. Still with me? I would like to enter the day the event took place in a cell (Let's say 4 July 2006). This event would come due on any day in Jan 2007. I would like for that cell containing the accomplished date to turn red on the first day of January 2007, and remain red for the entire month. I can use conditional formatting to make it turn red beginning on any number of days after the event; however, I would like to have Excel be able to calculate the beginning date of the month in which the event took place and add the appropriate amout of time (6 months or 12 months) so that the cell turns red on the first day of the due month.
Does this sound feasible? Is conditional formatting possible in this situation?
What would be a better approach to pursue?
Thanks,
Jerry in Anchorage

Hi,

With the assumption the cell to be formatted is cell B1 ...
Conditional Formatting
Formula Is

Please Login or Register  to view this content.

Format Patterns Red


HTH
Carim

I don't follow. I'm not sure how to apply what you provided me into my formula.
Here is what I came up with to flag a date between 150 & 180 days after an event had occurred.

=((B2+180)-TODAY())<30 With a red format.

This does work; however, it is too specific. I would like for the entire 6th month to be flagged.

I don't understand how to incorporate your code into my "stuff". Not trying to be obstinate, I really don't understand.
Computers are tougher than aircraft.

Thanks,
Jerry in Anchorage

I didn't see how you pass in whether it is 6 months or 12 months from the date.

That aside, when you're doing the conditional format, you can use the following formula: (I'll assume the date you are considering is in B2 as you used in your example and you want 6 months from now.)

=AND(DATE(YEAR(B2),MONTH(B2)+6,1)<=TODAY(),TODAY()<DATE(YEAR(B2),MONTH(B2)+7,1))

If you have the # of months in another cell (like C2):
=AND(DATE(YEAR(B2),MONTH(B2)+C2,1)<=TODAY(),TODAY()<DATE(YEAR(B2),MONTH(B2)+C2+1,1))

Basically, this is just saying that you want (1) The first day of the month 6 months from the date (B2) to be LESS THAN OR EQUAL to today's date, AND (2) Today's date to be LESS THAN the first day of the month 7 months from the date (B2).

For some reason, to me the formula makes more sense then my explanation of it, but hopefully one of them helps you.

Scott

Thanks.
You were right, I didn't specify whether it was 6 or 12 months. I figured I'd just use that 6 month example. I'd write another formula for the ones needing to be 12 months.
I believe I can figure it out, now. I certainly appreciate both of the replies. You folks sure respond quickly.

Thanks again,
Jerry in Anchorage.

Hi Jerry,

If I may the FAA regulation bit (i.e. 6 or 12 month) should appear as a calculation in the cell itself ...
whereas the formatting of the cell is to be handled on its own by conditional formating ...

Hope this is clear for you ...
All the best
Carim

By Jove, it works!

Many thanks to both of you who offered to help me. I had a dickens of a time till I noticed a space in the formula offered by Scott. Removed the space, & entered it into my sheet, added a splash of color & I'm a happy camper.

Thanks again, guys.
Jerry in Anchorage

You can perhaps do this more easily using the DATEDIF function.

For a date in B2 and for your 6 month example

=DATEDIF(B2-DAY(B2)+1,TODAY(),"m")=6