Post reply

Formatting fill colour on a cell dependent on the date of another cell

  • April 2, 2015 at 8:43 am

    #296675

    Hi all

    I would to format the fill colour of a cell based on the date held in another field - to produce an aged debtor colour range in essence - e.g. if date <= today - 30, then green, if date <= today - 60 and date > today - 30, then amber, etc etc. (that may not make sense but I hope you get the gist). Tried all sorts but although I can use this in the actual query for the report DATEDIFF(DD, STARTDATE, GETDATE()) <= 30, the expression just doesn't like it. Any ideas please? It surely must be possible.

    Look forward to hearing from someone soon!

    Many thanks,

    Jules

  • April 13, 2015 at 1:37 pm

    #1790621

    for referencing the value from one cell in another cell, have a look at ReportItems:

    https://msdn.microsoft.com/en-us/library/dd255285%28v=sql.105%29.aspx

    Your final expression might be something like DATEDIFF(DD, ReportItems!Textbox1.Value, GETDATE()) <= 30.

  • April 17, 2015 at 5:41 am

    #1791638

    Hi Jules,

    If the question posted by you is still unanswered, below is what you can do for cell background color.

    =SWITCH(

    DATEDIFF("m",Fields!Date.Value,Now)=0,"Green",

    DATEDIFF("m",Fields!Date.Value,Now)=1,"Orange",

    DATEDIFF("m",Fields!Date.Value,Now)=2,"Red")

    Though above is self explanatory, adding below note would do no harm 🙂

    In Fields!Date.value I have Data for three months - current(Apr), current-1, (Mar), current -2, (Feb). I am comparing this Date with current date (referred as NOW or TODAY). if its current month - Green, diff is only 1 month then Orange and if 2 months delayed, a Red would be suitable.

    Hope this helps.

  • April 17, 2015 at 6:03 am

    #1791644

    Thank you both for your suggestions - I'm off to have a play now.

    Best,

    Jules

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply