8 Replies Latest reply on Jan 24, 2013 7:06 AM by Annette

    Waiting Time

    Annette

      Title

      Waiting Time

      Post

           Hi,

           I need to create a calculation that gives me the waiting time between two fields in months and days. 

           If I have two fields called, Date Recommended and Date of Course I basically need to know how long they waited in months in days between when it was recommended and the date of the course.  If the date of course field is empty I want it to use the current date (As they would still be waiting for a date to be offered). 

           Thanks as always for the help!

        • 1. Re: Waiting Time
          raybaudi

                

               An UNSTORED calculation field, result TEXT, will do it:
                
               If ( Date of Course ; Date of Course ; Get ( CurrentDate ) ) - Date Recommended & " days"
          • 2. Re: Waiting Time
            philmodjunk

                 I basically need to know how long they waited in months in days between when it was recommended and the date of the course.

                 And what represents a "month" for your results? Can we use 30 days for each month or do you want the number of complete calendar months that have elapsed?

            • 3. Re: Waiting Time
              Annette

                   Unfortunately that doesn't give me the waiting time in months and days and if there is no date in the date of course then it's giving a negative number. 

              • 4. Re: Waiting Time
                Annette

                     We can use 30 days...i have another calculation which is far more complicated that truncates the months (like this.....  Truncate ( ElapsedDays/30.4; 0) & " months and " & Truncate ( Mod (ElapsedDays; 30.4); 0) & " days" which works fine to break it down the way I need it to) .... but no matter how I tried to adjust the first half of the calculation it didn't work.  That particular calculation took a whole host of other things into consideration before giving me the time frame where this SHOULD be simpler.  (I'm sorry, i'm clueless about these calculations and scripts, i'm waiting for my bright spark moment where i can wake and say I GET IT NOW!

                      

                     Sign.......

                • 5. Re: Waiting Time
                  philmodjunk
                       

                            ...need to know how long they waited in months in days between when it was recommended and the date of the course.  If the date of course field is empty I want it to use the current date...

                       To get the number of elapsed days:

                       If ( DateOfCourse ; DateOfCourse ; Get ( CurrentDate ) ) - DateRecommended

                       just as Raybaudi posted. If there is no date in dateOfCourse, the current date is substituted so you should not get a negative value here unless DateRecommended is a later date than the current date. You could, however, get a large positive number if there is no date in DateRecommended.

                       To get months and days, plug the above calculation into the one you have posted:

                       Let ( ElapsedDays = If ( DateOfCourse ; DateOfCourse ; Get ( CurrentDate ) ) - DateRecommended ;
                                                        int ( ElapsedDays/30.4 ) & " months and " & int ( Mod (ElapsedDays; 30.4)) & " days"
                              )

                  • 6. Re: Waiting Time
                    Annette

                         Hey,

                         I've plugged it in as you wrote above, and I've checked my dates to make sure one date is not past the other......

                         I have date recommended as 13/09/12  and the date of the course as 15/09/12  just to see if it is working properly which clearly should return 0 months 2 days but it returns 65789 months and 14 days. 

                    • 7. Re: Waiting Time
                      philmodjunk

                           Is your date field a field of type date or of type text? Sounds like you are using a text field for your date field.

                      • 8. Re: Waiting Time
                        Annette

                             UGH!  How frustrating and stupid of me!!!!  That was the problem!

                              

                             THANK YOU!!!!!!!!!!!