Discussions

Expand all | Collapse all

Problem using DateTime field in formula for color coding

  • 1.  Problem using DateTime field in formula for color coding

    Posted 25 days ago
    I'm trying to make a formula to control the colors on a report but I'm having trouble getting it to work.

    Here is my code:

    If([Due Date]>1+Today() "Reviewed", "Orange", [Due Date]>2+Today(), "Yellow", [Due Date]<Now(), "#ffcccb")

    I'm getting errors that '>' cannot be used with Date or DateTime fields and I haven't had success using ToDate to convert it.  Any ideas how I might get this to work?

    ------------------------------
    Jeff Peterson
    ------------------------------


  • 2.  RE: Problem using DateTime field in formula for color coding

    Posted 25 days ago

    try this:


    If([Due Date]>Today()+Days(1), "Reviewed", "Orange", [Due Date]>Today()+Days(2), "Yellow", [Due Date]<Now(), "#ffcccb")



    ------------------------------
    Amber Polston
    ------------------------------



  • 3.  RE: Problem using DateTime field in formula for color coding

    Posted 25 days ago
    I'm getting an error:  The operator '>' can't be applied on types datetime date


    ------------------------------
    Jeff Peterson
    ------------------------------



  • 4.  RE: Problem using DateTime field in formula for color coding

    Posted 25 days ago
    can you say bin worrds what the fomrula is suposed to do. The begining part here does not make sense.

    If([Due Date]>1+Today() "Reviewed", "Orange" ........


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Problem using DateTime field in formula for color coding

    Posted 25 days ago
    Yes, I just realized that the 'reviewed' doesn't belong here.  This is meant to color rows on a report based on a field called [Due Date]. 

    If([Due Date]>1+Today()  "Orange", [Due Date]>2+Today(), "Yellow", [Due Date]<Now(), "#ffcccb")


    ------------------------------
    Jeff Peterson
    ------------------------------



  • 6.  RE: Problem using DateTime field in formula for color coding

    Posted 25 days ago
    Ambers modified formula should work

    If([Due Date]>Today()+Days(1),  "Orange", [Due Date]>Today()+Days(2), "Yellow", [Due Date]<Now(), "#ffcccb")

    If the [Due date] field is in fact a date/ time field type, then

    If(
    ToDate([Due Date]) > Today()+Days(1),  "Orange",
    ToDate([Due Date]) > Today()+Days(2), "Yellow",
    ToDate([Due Date])  < Now(), "#ffcccb")


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Problem using DateTime field in formula for color coding

    Posted 25 days ago
    Edited by Jeff Peterson 25 days ago
    It keeps throwing the same error for the '>' symbol:  The operator '>' can't be applied on types datetime, date

    However,  I changed the Now() to Today and it seems to be working now.

    ------------------------------
    Jeff Peterson
    ------------------------------



  • 8.  RE: Problem using DateTime field in formula for color coding

    Posted 25 days ago
    What field type is your due date field?
    Can you post the formula you are using?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 9.  RE: Problem using DateTime field in formula for color coding

    Posted 25 days ago
    It's a Datetime type field.

    Here's the current formula:

    If(
    ToDate([Due Date]) > Today()+Days(1), "Orange",
    ToDate([Due Date]) > Today()+Days(2), "Yellow",
    ToDate([Due Date]) < Today(), "#ffcccb")

    ------------------------------
    Jeff Peterson
    ------------------------------



  • 10.  RE: Problem using DateTime field in formula for color coding

    Posted 25 days ago
    Can you post the error message you get when you try to save the formula?  Does the formula save OK, if so nevermind the yellow warning icon.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------