Our Preferred Hosting Provider - Award Wining FileMaker Pro Hosting
Results 1 to 7 of 7
  1. #1
    Gold Member
    Join Date
    Sep 2012
    Posts
    29
    Location
    Northern IL
    FileMaker Version
    FM 11, FM 11 Developer, FM 11 Server, FM 12, FM 12 Developer, FM 12 Server
    OS
    Windows & Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0

    Need Help With Conditional Formatting a field based on Date Range

    The Scarpetta Group- our preferred FMT Forum Consultants
    Hey All!

    I'm working on setting up some conditional formatting on a field that displays due dates. I've figured out how to use conditional formatting to change the text color to orange by this: Self ≥ Get(CurrentDate) - 6. So the date in the field turns orange if the due date is between today and 6 days before the due date (At least I think that's how it's working, Hope!) I also want to:

    Have the due date text in the field turn yellow if the due date in the field being formatted is between 7-14 days before the due date
    and
    Have the text in the due date field turn green if the due date in the field is 15-30 days before the due date.

    I'm sure it's an easy one but I'm still green behind the ears .

    Thanks!
    Warning: This is an Old Thread
    This discussion is older than 420 days. information contained in it may no longer be current

  2. #2
    Good guy doughemi's Avatar
    Join Date
    Apr 2005
    Posts
    890
    Location
    Middleport, NY USA
    FileMaker Version
    FM 9 Developer, FM 10 Developer, FM 11 Server, FM 12 Developer, FM 12 Server
    OS
    Windows & Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0

    Re: Need Help With Conditional Formatting a field based on Date Range

    Use the same calculations, but put the 30 days condition first:

    Self ≥ Get(CurrentDate) - 30 //green
    Self ≥ Get(CurrentDate) - 14 //yellow
    Self ≥ Get(CurrentDate) - 6 //orange

    The conditional result will always reflect the last true condition.
    Give the gift of life. Register as an organ donor today!
    http://www.organdonor.gov/become.asp

  3. #3
    Gold Member
    Join Date
    Sep 2012
    Posts
    29
    Location
    Northern IL
    FileMaker Version
    FM 11, FM 11 Developer, FM 11 Server, FM 12, FM 12 Developer, FM 12 Server
    OS
    Windows & Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0

    Re: Need Help With Conditional Formatting a field based on Date Range

    I'm not sure that your suggestion is working. Not sure where I'm going wrong here...Are the calculations correct for what I'm trying to do?

  4. #4
    Good guy doughemi's Avatar
    Join Date
    Apr 2005
    Posts
    890
    Location
    Middleport, NY USA
    FileMaker Version
    FM 9 Developer, FM 10 Developer, FM 11 Server, FM 12 Developer, FM 12 Server
    OS
    Windows & Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0

    Re: Need Help With Conditional Formatting a field based on Date Range

    It works fine for me. If I enter 10/7/12 in the field, the text is green. If i enter 10/17/12, the text is yellow, and if I enter 10/27/12, the text is orange.

    The three calculations, as shown, should be in the Conditional Formatting dialog for the due date field. Use the Add button to insert the second and third conditions. All 3 check boxes next to the calculations should be checked.
    Give the gift of life. Register as an organ donor today!
    http://www.organdonor.gov/become.asp

  5. #5
    Gold Member Helimanick's Avatar
    Join Date
    Jul 2007
    Posts
    52
    Location
    Quincy, IL USA
    FileMaker Version
    FM 11, FM 11 Developer, FM 11 Server, FM 12, FM 12 Developer
    OS
    Windows & Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0

    Re: Need Help With Conditional Formatting a field based on Date Range

    I assume when you say "Due Date" you mean the date in which something is due. So the "Due Date" would be in the future correct? If this is the case then you just need to invert the calculations, I took the liberty of adding the calculations for anything that is greater than 30 days green and Due that day or before Red:

    Self ≥ Get(CurrentDate) + 30 //Green Greater than 30 days till due
    Self ≤ Get(CurrentDate) + 30 //Green 30 to 15 days till due
    Self ≤ Get(CurrentDate) + 14 //Yellow 14 to 7 days till due
    Self ≤ Get(CurrentDate) + 6 //Orange 6 to 1 days till due
    Self ≤ Get(CurrentDate) //Red due date is today or before meaning it is late

    You can modify these as you see fit I just think that it should be red if today is the due date or if it is late.

  6. #6
    Good guy doughemi's Avatar
    Join Date
    Apr 2005
    Posts
    890
    Location
    Middleport, NY USA
    FileMaker Version
    FM 9 Developer, FM 10 Developer, FM 11 Server, FM 12 Developer, FM 12 Server
    OS
    Windows & Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0

    Re: Need Help With Conditional Formatting a field based on Date Range

    I think you're right, Nick. I got crossed up deciding which "due date" was really "current date".
    Give the gift of life. Register as an organ donor today!
    http://www.organdonor.gov/become.asp

  7. #7
    Gold Member
    Join Date
    Sep 2012
    Posts
    29
    Location
    Northern IL
    FileMaker Version
    FM 11, FM 11 Developer, FM 11 Server, FM 12, FM 12 Developer, FM 12 Server
    OS
    Windows & Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0

    Re: Need Help With Conditional Formatting a field based on Date Range

    Thank you both, Doug and Nick, very much for your help!

    Nick, I'm going to get on implementing your suggestion and see where it takes me but it looks like exactly what I'm asking about!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Formatting Based on Summary Field
    By joannadarling in forum FileMaker Pro 10
    Replies: 3
    Last Post: 05-02-2013, 03:26 PM
  2. conditional formatting based on layout name
    By jcpython in forum FileMaker Pro 11
    Replies: 2
    Last Post: 03-17-2012, 09:26 AM
  3. conditional formatting based on date
    By robfoster in forum FileMaker Pro 11
    Replies: 3
    Last Post: 11-03-2011, 02:19 PM
  4. Conditional Formatting Based on Time
    By 3PointJ in forum FileMaker Pro 10
    Replies: 3
    Last Post: 04-16-2010, 05:47 AM
  5. Conditional Formatting based on view mode?
    By preet in forum FileMaker Pro 9
    Replies: 7
    Last Post: 06-26-2008, 11:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Welcome to the Original FileMaker Forum
With designated forums for almost every FileMaker topic...FileMaker Today is a free-to-join community where you can boost your FileMaker expertise, build better solutions and solve your technical challenges. If you're building FileMaker solutions, this is the place for you.
Follow FMT
We Are FMT
FMT is home of The World's leading FileMaker News Site, Worldwide Developer Directory, FileMaker Connect Community and so much more.