Need Help Conditional Formatting a Text Box by Calendar Month

[Grave]

Hi,

I have a database that tracks inspection due dates on industrial equipment. What I want to do is conditonally format the text boxes, so that any inspection that comes due on the next calendar month is highlighted green. For example:

Today is 21-Aug-2012. If a piece of equipment has an inspection due in September, I want the text box displaying the inspection date to be highlighted green.




Any help would be appreciated, thanks!

[June7]

So the criteria would be if the due date is any day in the next month? So if today is August 1 only the due dates in September will display green? Don't want the Aug 2 to 31 due dates to display green?

Month(Date()) + 1 = Month([DueDate])

[Grave]

June, that would be correct. I already have the current month (for our example, this month is August) conditionally formatted to highlight yellow, by using this formula: Month([DueDate])=Month(Date())


I just couldn't figure out how to add a month to it. It's a little different than Excel (which I'm more familiar with). But, your formula did the trick. Thank you!

[Grave]

Ok, I just noticed something. Using June7's formula above, any inspection date that is due in September (regardless of year) is now being highlighted in green. For example: Inspection dates for equipment due in September 2012 and September 2013 are now being highlighted green. All I need is for the next calendar month (September 2012).

I know I need to input a year into the formula somewhere, but how?

[June7]

Try:

Format([DueDate],"myyyy")=Format(Date(),"myyyy")

Format([DueDate],"myyyy")=Format(DateAdd("m",1,Date()),"myyyy")

[Grave]

Yes, that did the trick! Now next year's dates are back to the default formatting. Thanks again!