1 2 3 Previous Next 42 Replies Latest reply on Jan 23, 2012 6:05 AM by beverly

    Help with a date formular

    aeropc

      Hi guys - new here

       

      I am after a formula for a calculated date field.

       

      I have one field that sets the "Period": eg 12 months

      Then an input field for "Date Installed" eg 20 Jan 2012

       

      The formula must auto calculate the date for removal minus one day. eg 19 Jan 2013

       

      It's to deal with a legislative requirement allowing up to midnight on the 19th and no more.

       

      Regards Paul

        • 1. Re: Help with a date formular
          comment

          1. Is Period always expressed in months?

           

          2. If Date Installed is 31 Jan 2012, and Period is 1 month - what is the correct answer?

          • 2. Re: Help with a date formular
            aeropc

            Yes always in months, but never less than 3 months.

            • 3. Re: Help with a date formular
              comment

              aeropc wrote:

               

              never less than 3 months.

               

              The second question still stands. Consider a date of 31 Oct 2011 and a period of 4 months. The point is that there is no 30 Feb 2012, so the term "4 months later" is ambiguous. By default, Filemaker would calculate the "30th day of February 2012" as 1 Mar 2012.

              • 4. Re: Help with a date formular
                aeropc

                Yes I see the dilema - that is why we manually insert the due date. Is it too much to ask of a formula?

                • 5. Re: Help with a date formular
                  AlanStirling

                  Hi Paul

                   

                  Here is a simple calc to give the result you need;

                   

                  Set Field [ Removal Date ] to

                   

                  Date ( Month (Date Installed) + Period ; Day (Date Installed) - 1 ; Year (Date Installed))

                   

                  Please test this out, but I think it will fit the bill.

                   

                  Best wishes - Alan Stirling, London UK

                  • 6. Re: Help with a date formular
                    comment

                    aeropc wrote:

                     

                    Yes I see the dilema - that is why we manually insert the due date. Is it too much to ask of a formula?

                     

                    No, you just need to state exactly what rules you want to go by. Especially when, as you say, this is "to deal with a legislative requirement".

                    • 7. Re: Help with a date formular
                      aeropc

                      Many thanks Alan - will give that a go.

                       

                      Regards Paul

                      • 8. Re: Help with a date formular
                        LyndsayHowarth

                        If you make it a Number field with an auto-enter calculation, you can always adjust it manually afterwards.

                         

                        - Lyndsay

                        • 9. Re: Help with a date formular
                          comment

                          Lyndsay Howarth wrote:

                           

                          ... you can always adjust it manually afterwards.

                           

                          Isn't that true of any calculation?

                          • 10. Re: Help with a date formular
                            beverly

                            The field of type Calculated cannot be altered except through the define field dialog. All records then update when the dialogs are closed. The auto-entered field (text, number, date, time, container) can be "calculated" at the time of record creation (or commit). It can be MANUALLY changed (if access is granted), in the record at a later time. Changing the auto-enter in the define dialog does not update existing records, only new ones.

                             

                            HTH those who might be unaware.

                             

                            -- sent from my iPhone4 --

                            Beverly Voth

                            --

                             

                            On Jan 20, 2012, at 2:11 AM, comment wrote in whole or in part:

                             

                             

                            created by comment in Scripting and Calculations - View the full discussion

                            Lyndsay Howarth wrote:

                             

                            If you make it a Number field with an auto-enter calculation, you can always adjust it manually afterwards.

                             

                            Isn't that true of any calculation?

                             

                            • 11. Re: Help with a date formular
                              comment

                              Hi Beverly,

                               

                              I am afraid you may have missed my point - which wasn't at all technical...

                              • 12. Re: Help with a date formular
                                beverly

                                Comment, you missed my point. I was clarifying for those who were not aware. There are a lot of newbies on this list now. I welcome them, but it takes the extra step sometimes.

                                Beverly

                                • 13. Re: Help with a date formular
                                  comment

                                  Beverly Voth wrote:

                                   

                                  Comment, you missed my point.

                                   

                                  That's entirely possible, and perhaps I still do. Auto-entry or calculation field, you can always enable users to influence or override the result. The question whether you should is a separate one - and IMHO the answer in this case is quite clear.

                                  • 14. Re: Help with a date formular
                                    LyndsayHowarth

                                    comment wrote:

                                     

                                    Beverly Voth wrote:

                                     

                                    Comment, you missed my point.

                                     

                                    That's entirely possible, and perhaps I still do. Auto-entry or calculation field, you can always enable users to influence or override the result.

                                     

                                    As Beverly pointed out... the value in a Calculation field cannot be modifiable... only a Text, or Number or Date or Time or Container field can.

                                     

                                    The value entered in one of these Text/Number/Date/Time/Container fields can be generated by an auto-enter calculation... which after it is generated can be modified... if you allow it.

                                     

                                    The question whether you should is a separate one - and IMHO the answer in this case is quite clear.

                                     

                                    Given that you said "Isn't that true of any calculation?" in response to my additional suggestion, I don't believe the answer was at all clear to you... which also prompted Beverly's straightforward comment.

                                     

                                    As Paul was attempting to create a calculation to replace what has been a manually entered date, it was not unreasonable for me to assume that he might make it a Type= Calculation field... therefore prompting me to suggest an auto-enter calculation on a date field so he could have the best of both worlds for the odd occasion when the auto-entered calculation did not provide the optimal date.

                                     

                                    Beverly was clear in her explanation and is a highly skilled professional who has supported people of all skill levels on these forums for many years. She has learned to provide detail and background to the debate... not a speedy reply with no substance. Please be respectful.

                                     

                                    Can we call you a name other than 'comment'?

                                    1 2 3 Previous Next