Conditional formatting RRS feed

  • Question

  • Hi I need assistance in formatting a report with conditional formatting

    1. I have a yearly report with cells filled with dates.
    2. What I need to do is- if date in cell is > end of month, change background color to yellow else no change
    3. As I create these reports for each year eg 2016, 2017, etc.I need the year field to be applicable to any year eg 2016, 2017 , 2018
    4. I Tried value> #31/3/2017#  - but it can only apply for year 2017  I need a variable for year field so that it is automatically picked up.
    Can you, as an expert, suggest what I can do?
    Monday, February 26, 2018 11:10 AM

All replies

  • Try using the DateSerial function:

    Value >=  DateSerial ([MyYearField], [MyMonthField] + 1, 1)

    Take a look at the description of the function here, to see how you can apply it to your specific case, possibly with some of the 'special values' mentioned:

    DateSerial Function

    Miriam Bizup Access MVP

    Monday, February 26, 2018 11:32 AM
  • Hi,

       I am not actually sure with your ask. Not able to understand your scenario. 

      Are you asking for 

      IF any date is actually more that the end date of current month, the back ground colour will change,

       For Eg, 

       There are dates like, 1st Jan ,2018

                                     2nd Feb, 2018

                                    3rd March, 2018

    So as per logic, since 3rd march is more than end date of current month (i.e. 28th Feb,2018) the back ground colour of the text box of 3rd March,2018 changes. IS this your ask?? 


    One simple thing I can share is, to find the end date of current month or any other date field, it is ,

    =DateSerial(Year(Now()), Month(Now()), "1").AddMonths(1).AddDays(-1)

    Please put some more descriptive example of the scenario so that I can help you with that.

    Monday, February 26, 2018 11:42 AM