14 Replies Latest reply on Nov 16, 2009 8:14 PM by yoelcita

    International Calendar/Date Conversion

    yoelcita

      Title

      International Calendar/Date Conversion

      Post

      I have used FileMaker in the past, but am creating a database for the first time.  My current challenge is figuring out a way to have two date fields: The first based on the Ethiopian calendar, and the second being the same day in history on the standard (for me) Gregorian calendar.  Is this possible, or must I calculate and enter both fields manually for each entry?  Is it possible to have a drop-down Ethiopian calendar for a date field, perhaps with some kind of plugin?  Any help or suggestions greatly welcome!

       

      I am using FileMaker Pro 10, on Mac OS X 10.5

        • 1. Re: International Calendar/Date Conversion
          philmodjunk
            

          I don't see a way to answer that without knowing what the differences are between the Gregorian and Ethiopian calendars.

           

          It should be possible to create a calculation so that selecting entering a date from one calendar enables displaying the corresponding date from the other calendar without having to enter the date twice.

          • 2. Re: International Calendar/Date Conversion
            raybaudi
               I do not like years that begin on September 11
            • 3. Re: International Calendar/Date Conversion
              yoelcita
                

              (Daniele, neither do I)  :)

               

              It's a little complicated.  The Ethiopian calendar has 13 months: the first twelve have 30 days, and the year begins on Sept 11 of our calendar (except on the leap year).  The thirteenth month has 5-6 days, their leap year working just like ours.  The year is 7-8 years behind ours.  Today in Ethiopia, the date is: Kheder (third month) 7, 2002.

               

              There are conversion sites and plugins out there, and I'm sure if I spend some time, I can write a conversion.  I just don't know how to implement one (simple or complicated) in FileMaker, and don't want to spend the brain cells until I know where I'll go with it.

               

              Thanks in advance for any direction!

              • 4. Re: International Calendar/Date Conversion
                philmodjunk
                  

                using http://calendopedia.com/ethiopian.htm as a reference, the following expressions should be accurate from 1900 until 2099. If you need historal dates, additional work will be needed to handle the difference in how the two calendars deal with leap years.

                 

                Let ([

                MonthNumberEthiopian = Div (DayofYear(GregorianDate) - 1 ; 30 ) ;

                DayOfYearEthiopian = DayofYear(Gregoriandate) - DayofYear (Date(9 ; 10 ; Year(Gregoriandate)) ;

                DayofMonthEthiopian = Mod(DayofYearEthiopian - 1 ; 30 ) + 1 ;

                YearEthiopian = Year(GregorianDate) + 7 + (GregorianDate < Date (9 ; 11 ; Year(Gregoriandate))) ] ;

                MonthNumberEthiopian & "/" & DayofMonthEthiopian & "/" & YearEthiopian )

                 

                If I haven't made any mistakes, that should convert a Gregorian Date into text showing the equivalent Ethiopian Date.

                • 5. Re: International Calendar/Date Conversion
                  yoelcita
                    

                  You are amazing!  I don't need historical dates before 1900, so that is fine.

                   

                  Is there a good way to convert the other way around?  (from Ethiopian to Gregorian?)  I'm guessing I will need a calendar recognizing a 13th month to start with... Of course, I'm a beginner, so my mind is doing some uncomfortable acrobatics right now.

                   

                  Thanks again

                  • 6. Re: International Calendar/Date Conversion
                    philmodjunk
                       Gregorian to Ethiopian is fairly straight forward because ethoipian months are all the same number of days except for the last month. That makes for straight forward conversion. The other approach is much more complex since Gregorian months can be 28, 29, 30 or 31 months in length and the leap month falls in month 2. That would take a fairly complex case function to make it happen.
                    • 7. Re: International Calendar/Date Conversion
                      yoelcita
                         I thought so... But even if I were to go about creating a function, can I even classify the Ethiopian date field as a "Date" or would it just have to be "Text" ?  Perhaps I would have to make separate Day, Month and Year fields?
                      • 8. Re: International Calendar/Date Conversion
                        philmodjunk
                          

                        I would recommend treating the Ethiopian date as text--which is what my expression assumes. You can retain the Gregorian equivalent for proper sorting.

                         

                        For finding Ethiopian date ranges, you might want to use elements of my example to isolate the Ethopian month, day and year numbers in separate fields.

                         

                        PS. I have not TESTED the suggested calculation. If anyone spots any mistakes in what I posted, feel free to point them out. :smileywink:

                        • 9. Re: International Calendar/Date Conversion
                          yoelcita
                             Ok, thanks again!  This has been a huge help.
                          • 10. Re: International Calendar/Date Conversion
                            comment_1
                              

                            yoelcita wrote:
                            can I even classify the Ethiopian date field as a "Date" or would it just have to be "Text" ?  Perhaps I would have to make separate Day, Month and Year fields?

                            It cannot be a Date field, since you need 13 months. I believe the most convenient way woud be three separate fields (of type Number).

                             

                             

                            To convert Ethiopian date to Gregorian, try the following calculation (result is Date) =

                             

                             

                            1461 * Div ( eYear - 1 ; 4 ) + 365 * Mod ( eYear - 1 ; 4 ) + 30 * ( eMonth - 1 ) + eDay + 2795

                             

                            This should work for all days that are common to the  Ethiopian and Filemaker eras, i.e. from August 27, 0008 until December 31, 4000.

                             

                             

                             


                            • 11. Re: International Calendar/Date Conversion
                              philmodjunk
                                

                              Interesting and welcome back Comment.

                               

                              I can concieve of two sets of fields with auto-enter options set. Enter E dates into the three fields Comment describes and the G date field auto enters the Gregorian equivalent. Enter a gregorian date into the G date field and auto-enter calcs could enter the respective Ethopian month, date and year values.

                              • 12. Re: International Calendar/Date Conversion
                                yoelcita
                                  

                                Hi Comment,

                                 

                                Your calculation is really close.  The handful of dates I've tested so far come out as one year late, plus or minus one or two days (assuming the converter at http://www.funaba.org/en/calendar-conversion.cgi is correct).  For my purposes, the exact day may be of less importance than the year... but either way this is certainly getting me going in the right direction.

                                 

                                Thank you both for your help!

                                • 13. Re: International Calendar/Date Conversion
                                  comment_1
                                    

                                  Sorry, I have copied the wrong line...

                                   

                                  I have edited the original post - hopefully it will work correctly now.

                                  • 14. Re: International Calendar/Date Conversion
                                    yoelcita
                                       This is perfect... precise to within a day, which is just what I need.  You guys are incredible; I never expected to have a complete solution so soon!  Thank you, thank you!