1 2 Previous Next 29 Replies Latest reply on Sep 19, 2012 9:09 AM by cardezco

    Been fighting with a date calculation formula... can someone help?

    cardezco

      I have a field, FilingDate containing a date, and I am comparing the Day (DD) against the Day in Current_Date... for some reason, in the comparison, if Day(Current_Day) is greater than Day(FilingDate) it is only true, when the day in Current_Date is greater than Day in FilingDay by 5 days... in other words, today, Sep 16 (when I am entering this question) won't detect that the day is greater, unless the Day in FilingDate is the 10, if I enter 9/14/2012, and compare if ( Day(Current_Date) > Day(FilingDate)), it will comeout FALSE... until I change the date in FilingDate to 9/10/2012... day 11 will not work, 12 will not work, 13 will not work, 14, will not work, 15 will not work... Any help is greatly appreciated!

        • 1. Re: Been fighting with a date calculation formula... can someone help?
          comment

          What exactly is Current_Date? Is it set to return a Date result and is it unstored? Why is it needed at all, when you can do =

           

          Day ( Get ( CurrentDate ) > Day ( FilingDate )

           

          directly?

           

           

           

          BTW, what is the significance of such comparison?

          • 2. Re: Been fighting with a date calculation formula... can someone help?
            cardezco

            Thank you Michael for your prompt response. I had replied to the email I got from Filemaker regarding your response, but it got rejected and sent to my spam mail, so I didn't realize it 'til today. In any event, your questions answered my problem. I was storing Current_Date (Get(CurrentDate)) on the database with the box checked not to update content... so I unchecked the box and everything worked just fine... Thanks again...

            • 3. Re: Been fighting with a date calculation formula... can someone help?
              cardezco

              BTW- what's happenning is that I have to translate everything from MM/DD/YYYY to DD/MM/YYYY since the people will be using the APP do not want to change system dates, or anything like that... and I have the need to come up with future payment dates, monthly, quarterly, semi-annual and annual depending on the initial filing date, and the day is one of the pieces of information I need to compare, to determine whether it applies to this term, or next... roughly 150 lines of code so far...    : 0

              • 4. Re: Been fighting with a date calculation formula... can someone help?
                comment

                cardezco wrote:

                 

                BTW- what's happenning is that I have to translate everything from MM/DD/YYYY to DD/MM/YYYY since the people will be using the APP do not want to change system dates

                 

                Nor should they be required to: why don't you simply set the file to 'Always use current system settings' (under File…> File Options > Text > Data Entry)?

                • 5. Re: Been fighting with a date calculation formula... can someone help?
                  cardezco

                  OK I can do that. Unfortunately there is no getting around having to translate date formats manually and do all the calculations myself...

                  • 6. Re: Been fighting with a date calculation formula... can someone help?
                    comment

                    cardezco wrote:

                     

                    Unfortunately there is no getting around having to translate date formats manually and do all the calculations myself...

                     

                    I don't think that's necessary. Perhaps you should explain in more detail what are you doing and why setting the file as suggested won't work for you.

                    • 7. Re: Been fighting with a date calculation formula... can someone help?
                      cardezco

                      I am going to do what you suggest. But,

                       

                      The first user of this system, has to create customer profiles, going back 10 years, and the only piece of informaion they will provide me with, is an initial filing date in a DD/MM/YYYY format. From that I have to figure out what the next payment date for each client will be, taking into account that date, the type of payment (M=monthly, Q=quarterly, S=semianual, Y=yearly)... and retrun a date in a DD/MM/YYYY format as well... so I have to figure out first whether is M, Q, S or Y type payment, and add the number of months or years to the date. Monthly and Yearly are pretty straight forward, I just need to make sure that if the day of the filing month/day was, say 28 of Sep, to make the payment month to 28 of Sep 2012, if it was 17 of Sep, then it will go to Oct if it is monthly, or Sep 10, 2013 if it is yearly. The quarterly though, are extremly complex, since I have to account for the user, entering really really old filing dates, and creating profiles, any month of the year, not just this month... in other words, a new user could come online in Jaunary 2013, and start creating profiles for customers going back 10, 20 years...

                      • 8. Re: Been fighting with a date calculation formula... can someone help?
                        taylorsharpe

                        Welcome to the bane of most every database developer working with date data from different sources.  I've gotten pretty good at date format conversions, but it can be time consuming with little benefit to show from the time spent.  I can empathize, but it is just one of those things us developers have to do. 

                        • 9. Re: Been fighting with a date calculation formula... can someone help?
                          cardezco

                          *** 17 of Sep meant to type*** if it is yearly...

                          • 10. Re: Been fighting with a date calculation formula... can someone help?
                            cardezco

                            Tell me about it... I finished the routines for M, Y, and S in a day... and it has been 5 days, and I am still finding issues with my Q dates... when I started testing for CurrentDate of January, February, and March... as well as October, November and December... April through September seem to be working good...

                            • 11. Re: Been fighting with a date calculation formula... can someone help?
                              taylorsharpe

                              There are a couple of tips about dates.  When searching, you can search with asterisks.  So if you want to find everything in June, you can search for "6/*/2012".  Or if you wanted everyone having a birthday today, you can search for "9/18/*". 

                               

                              You can also add across boundaries such that a formula like Date ( Mnth ; Day + 14 ; Yr ) would give you the date of 14 days in advance even if it goes into the next month.  It works for years and months too.  So, Date ( 12 +3 ; 15 ; 2011 ) is the same thing as Date ( 3 ; 15 ; 2012 ).  And subtraction obviously works too.  Being flexible like this is great especailly when dealing with leap years and such. 

                              • 12. Re: Been fighting with a date calculation formula... can someone help?
                                cardezco

                                Great - That's really good to know... I know that report request is coming, when they are going to want bday's today, etc... Also good to know about the date additions... I will look into them, to optimize my calculations. Thank you!

                                • 13. Re: Been fighting with a date calculation formula... can someone help?
                                  comment

                                  I don't know... I still think you're making this more complicated than it needs to be. If the data to import is known to be in D/M/Y format, you could import it into a Text field, and calculate the real date by parsing the text. Then it wouldn't matter at all what format is being used by the file.

                                  • 14. Re: Been fighting with a date calculation formula... can someone help?
                                    cardezco

                                    Believe me, if I could figure out a way to simplify this, I would... for instance, this is the logic to figure out the year for Semi-annual payments, I have a similar routine to figure out the month:

                                     

                                    /* Tipo Pago = Payment Type, Fecha Emision=Filing Date */

                                     

                                               Tipo Pago = "S";

                                                Case (

                                                           Month(Fecha Emision) > Month (Current_Date);

                                                           Year(Current_Date);

                                                          

                                                           Month(Fecha Emision) = Month (Current_Date);

                                                           Case (Month(Current_Date)  ≤ 6; Year(Current_Date);

                                                                    Month(Current_Date)  > 6;

                                                                    If ( Day(Current_Date)  ≥ Day(Fecha Emision); Year(Current_Date)+1;

                                                                          Year (Current_Date))

                                                                      );

                                                          

                                                           Month (Fecha Emision) < Month (Current_Date);

                                                           Case (          

                                                                      Month(Current_Date)  ≤  6;

                                                                      Year(Current_Date);

                                     

                                                                      Month(Current_Date) > 6;

                                                                      Case ( Month(Fecha Emision)  > 6;

                                                                                Year(Current_Date) +1;

                                                                                Month(Fecha Emision)  ≤ 6;

                                                                                If ( Month(Fecha Emision)+6  ≥ Month (Current_Date);

                                                                                Year(Current_Date); Year(Current_Date)+1)

                                                                                  )

                                     

                                                                        )

                                                                )

                                    1 2 Previous Next