Our Preferred 5 Star Hosting Provider - Award Winning FileMaker Pro Hosting
Results 1 to 5 of 5
  1. #1
    Gold Member
    Join Date
    Aug 2009
    Posts
    39
    Location
    Torrance, CA
    FileMaker Pro Software
    FILEMAKER PRO 11 ADVANCED
    OS
    Mac
    Skill Level
    Intermediate
    Last Post
    11-21-2017
    Local Date
    11-25-2017
    Local Time
    01:30 AM
    Last Active
    11-24-2017

    Handling date searches in leap years

    Looking for some tips on how to handle leap year dates. I have a file that needs to search for monthly data (i.e. 1/1...1/31, etc.). This all works fine, except for leap years, where 2/1...2/28 leaves out the 29th. Can anyone suggest some solutions to this?

    TIA

  2. #2
    FileMaker Mentor
    Join Date
    Aug 2015
    Posts
    111
    Location
    Broomall, PA USA
    FileMaker Pro Software
    FM 16 Advanced
    OS
    Windows
    Skill Level
    Intermediate
    Last Post
    11-18-2017
    Local Date
    11-25-2017
    Local Time
    04:30 AM
    Last Active
    11-24-2017
    I don't quite know exactly how you are doing these searches, but you can use a variation of this calculation:

    Date(Month(TABLE::dateField);1;Year(TABLE::dateFie ld)) & "..." & Date(Month(TABLE::dateField)+1;0;Year(TABLE::dateF ield))

  3. #3
    Gold Member
    Join Date
    Aug 2009
    Posts
    39
    Location
    Torrance, CA
    FileMaker Pro Software
    FILEMAKER PRO 11 ADVANCED
    OS
    Mac
    Skill Level
    Intermediate
    Last Post
    11-21-2017
    Local Date
    11-25-2017
    Local Time
    01:30 AM
    Last Active
    11-24-2017
    Thanks, but I think you are too far ahead of me. The search I am using is simply using a first day, last day criteria. Like this: Find (1/1/17...1/31/17). This finds all the data for the period defined, but when I get to a leap year I have a problem. I guess I could put in some kind of field that detects when the year ends in 00, 04, 08 ,...96, and somehow change the February search to 2/1/20...2/29/20. But that seems awfully clunky and prone to problems. I'm not greatly experienced with FMP, so a less complicated version than the one you suggest is what I'm after.

  4. #4
    Good guy doughemi's Avatar
    Join Date
    Apr 2005
    Posts
    1,280
    Location
    Middleport, NY USA
    FileMaker Pro Software
    FMPA 14
    OS
    Windows & Mac
    Skill Level
    Intermediate
    Last Post
    11-07-2017
    Local Date
    11-25-2017
    Local Time
    04:30 AM
    Last Active
    11-25-2017
    Let FileMaker do the heavy lifting, rather than kludging something together that may not work in all cases.

    Steve's calculation takes advantage of the fact that FM considers the 0th day of the month to actually be the last day of the previous month. (But you can't manually enter 3/0/2020). The attached example shows a way to script the find so that the user doesn't have to worry about it (or understand FM's search range convention).

    Create a global g.searchMonth field, and a global g.searchYear field. Add a Search button that runs the following script

    Code:
    Enter Find Mode []
    Set Field [jabert::date[]; Let(
    [~startDate = Date(jabert::g.searchMonth; 1; jabert::g.searchYear);
    ~endDate= Date(jabert::g.searchMonth + 1; 0; jabert::g.searchYear)
    ];
    ~startDate & "..." & ~endDate
    )]
    #
    Perform Find []
    Sorry, you won't be able to use the sample. I'll make a .fp7 version and add it later this evening.
    Attached Files Attached Files
    Last edited by doughemi; 10-25-2017 at 06:16 PM.
    Give the gift of life. Register as an organ donor today!
    http://www.organdonor.gov/becomingdonor

  5. #5
    FREE Member
    Join Date
    Jul 2012
    Posts
    25
    Location
    Bournemouth, Dorset, United Kingdom
    FileMaker Pro Software
    FileMaker Pro Advanced 12-16
    OS
    Windows & Mac
    Skill Level
    Pro
    Last Post
    10-26-2017
    Local Date
    11-25-2017
    Local Time
    03:30 AM
    Last Active
    11-23-2017
    If you want to seach a date range that is actually the entire month, you do not need to enter it the way you are doing ("01/01/2017...31/01/2017" (uk date format), you can just enter "01/2017" and that does the entire month. Likewise if it is February, then enter "02/2016" will find all dates in Feb 2016 (Which would include 29th Feb 2016).

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. beginning date + number of years = end date
    By Caronte in forum Calculations and Define Fields
    Replies: 4
    Last Post: 04-27-2011, 07:38 AM
  2. How to Subtract Years from a Date
    By meowy in forum FileMaker Pro Versions 5 & 6
    Replies: 1
    Last Post: 05-28-2009, 11:37 AM
  3. Date Calculation and Handling Leap Year
    By iankh in forum Calculations and Define Fields
    Replies: 3
    Last Post: 04-08-2007, 08:06 AM
  4. Calculating a Date plus 40 Years
    By Bones in forum Calculations and Define Fields
    Replies: 1
    Last Post: 03-24-2005, 12:14 PM
  5. Help with multiple date searches
    By KASEI in forum Find, Sort and Search
    Replies: 6
    Last Post: 11-10-2004, 12:24 PM

Tags for this Thread

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 apps & solutions and interact with over 42,000+ FileMaker Developers from all over the world. 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.