9 Replies Latest reply on Aug 17, 2013 9:50 PM by JimmyMadison

    Working with dates


      Hi everyone, I have a solution I 'am working on which deals with calculating number of days between two dates. The solution I have working is a jail inmate management system. I order to know the number of days time served, I need to calculation between the two dates. One of my problems is if a person is booked and released on the same date, I need to show this person was in jail for one day even if it was 1 hour or 23 hours and 59 minuets. Also maintain a true calulation of 1 day if 24 hours have passed. Thanks for your help. JM

        • 1. Re: Working with dates

          I would suggest that you use timestamps rather than dates. TimestampB - TimestampA is not going to be zero when someone leaves on the same day that they arrived. It will be hours or minutes.


          I would also suggest that you use two fields. One is the raw data, TimestampB - TimestampA. The other is the rounded version which calculates the number of days served.



          • 2. Re: Working with dates

            Can you insert a Case (or If) statement into your calculation?  It would look something like this:


            Case (

                 endDate - startDate = 0 ; 1 ;

                 endDate - startDate



            If I'm interpretting correctly, if the difference between endDate and startDate is 0 (i.e. the same date), it sets it to a 1.  If the dates are different, it would calculate the actual difference so that the difference of a single day would still calculate as a 1.


            I hope find this helpful.  Good luck and happy coding.



            1 of 1 people found this helpful
            • 3. Re: Working with dates

              Thanks for the reply, it seems to work well, but after tallying up the days I realized that each person booked into the system must show the first day as a hole day. So in essence I will come up short a day on all others that stay more than one day. The calulations works fine if the person is booked in and out on the same day. Thanks for your help. JM

              • 4. Re: Working with dates



                Sorry for the delay in following up.  Can you simplify the calculation by eliminating the Case ( ) statement and make it read:


                (endDate - startDate) + 1


                If the endDate and startDate are the same, the difference is 0 plus 1 for the current day.  If the endDate is greater that the startDate, it will simply add the buffer of one day to the difference between the two dates.  You may have already figured something else out but I just wanted to pass this along just in case.


                Best regards,



                • 5. Re: Working with dates

                  I have just deployed my solution and have discovered that if a inmate was intake in the same month but in years past, I am getting a negative results depending if the day of intake was earlier than currant day.



                  If ( Month(Intake_Date) = Month(CurrantDate) ; Day (CurrantDate) + 1 - Day (Intake_Date) ; Day (CurrantDate))



                  End results what I need is the number of days they were with us even if they got out the next day. The inmates that have been with us for an extended time, I need the number of days they were with us for the current month. The above seams to work unless the intale date in previous year is same month as current month. This is were I am getting the negative results.


                  I used the following to get the days when they have been released in the current month.




                  If ( Month(Intake_Date) = Month(CurrantDate) ; Day (Exit_Date) + 1 - Day (Intake_Date) ; (Day (Intake_Date) = 0) + Day (Exit_Date))




                  Thanks to all for your help. JM


                  I am posting a sample file with the same setup.

                  • 6. Re: Working with dates

                    I think you have gotten way too complicated with your calculations. Try this calc for your TotalDays field:


                    Let ( [


                    in = TestDates::Intake_Date ;

                    out = TestDates::Exit_Date ;

                    today = Get ( CurrentDate ) ;

                    stillHere = IsEmpty ( TestDates::Exit_Date ) ;

                    result = If ( stillHere ; today - in ; out - in ) + 1 ] ;


                    result )


                    That gives you a clear and easily read calculation to work with. I'm not clear what you are trying to achieve with the monthly total fields, but your calcs suggest you need to study the logic of some of the date functions. For example, Month_Total_ETS_Date is giving inconsistent results which mean nothing as it presently stands.

                    • 7. Re: Working with dates

                      Thanks for your fast reply. I have changed the calculation in the TotalDays field and works great. The Month_Total_ETS_ Date is" Exit Time Served". In this field I need to show the number of days an inmate was in our facility for a given month. If he is released on the 10th of the month the result should be 10 days. This is Calculated in the Monthly_Total_Intake_Date field.


                      On the active report layout I have only one date to calculate from which is the date the inmate was jailed. If there are 31 days at the end month the result should be 31 days if I run the report on the last day of the month. . With my calculation the problem I am having is when the inmate was jailed in the same month in a differant year. As I run the report, I get a neg. number. Also on the Inactive report layout the same result.


                      Just say the inmate was jailed on 08/21/2011 and I  run the report today the result is a -4 not 16. which is the 16th day of the month. This seams to only happen if the date of Intake_Date is in the same month as I run the report. I hope this explains the results I am trying to accomplish. JM

                      • 8. Re: Working with dates

                        The problem with your Month_Total_ETS_Date calc is that you are not taking account of the year, hence the calculation fails if the person happens to have been jailed in the same month, regardless of the year, as he/she is released.  However, again it seems to me that your calculation is way to complicated. You say "I need to show the number of days an inmate was in our facility for a given month. If he is released on the 10th of the month the result should be 10 days". (I can't quite figure out why you need to know that particular detail, but be that as it may.) If it is a simple as that, why not simply count the number of days the inmate was in prison for that month? I assume that the only circumstance in which the result would be less than the number of days elapsed in the month so far, would be if he/she was jailed within the same month. So a suitable calc would be:


                        Let ( [


                        in = TestDates::Intake_Date ;

                        out = TestDates::Exit_Date ;

                        today = Get ( CurrentDate ) ;


                        monthIn = Month ( in ) & Year ( in ) ;

                        monthOut = Month ( out ) & Year ( out ) ;

                        monthNow = Month ( today ) & Year ( today ) ;


                        stillHere = IsEmpty ( out ) ;


                        daysExitMonth = If ( monthIn = monthOut ; out - in + 1 ; Day ( out ) ) ] ;


                        daysExitMonth )


                        Notice I have used the Let ( ) function in both calculations I have suggested, and that this one uses most of the same variables as the first one, then adds some more. I encourage you to study the calcs and learn to use this function; it is one of the most useful tools in the FM kit.

                        • 9. Re: Working with dates

                          Keywords, thanks for your help. I have ordered today Filemaker 12 Developer Reference Guide today. I have only one other problem to solve. On the active layout I must show the number of days the active inmate has been with us. Some of our inmates will only stay one or two day in the currant month reporting. The year problem is also giving me the problem resulting in a neg. number of days. The reason the end of the month report is important, is that the state pays us a small amount of money to house state inmates for there food bill. Hopefully the manual will give me a better understanding on logic. Thanks so much for all that has helped. You guys and gals are great. JM