4 Replies Latest reply on Apr 12, 2013 7:54 AM by JimMac

    Waiting Time in Months and Days

    Annette

      Title

      Waiting Time in Months and Days

      Post

           Hi,

           I have a calculation which gives me the waiting time, but how do I write it to give me the time in months and days. 

           This is the calculation:

           If ( IsEmpty ( Date Discharged ) and
           IsEmpty (Service Date Start)
            ;
           Get ( CurrentDate ) - Date Accepted;
           If ( IsEmpty ( Service Date Start) ;
           Date Discharged - Date Accepted
           ; Service Date Start - Date Accepted  ))

           Any help would be greatly appreciated. 

        • 1. Re: Waiting Time in Months and Days
          Jade

               Try these calculations (Date2 must be greater than or equal to Date1):

               Months = (Year(Date2) - Year(Date1)) * 12 + Month(Date2) - Month(Date1) - If(Day(Date2) - Day(Date1) < 0; 1 ; 0)

               Days = Let( [a = Day(Date2); b = Day(Date1); md1 = Month(Date1); yd1 = Year(Date1)] ; If( a < b ;  + Choose( md1 ; 0 ; 31 ; 28 + ( Mod( yd1 ; 4) = 0  ) ; 31 ; 30 ; 31 ; 30 ; 31 ; 31 ; 30 ; 31 ; 30 ; 31 ) - b ; a-b))

                

               So in your calculation, you could replace Get( CurrentDate ) - Date Accepted with something like this:

                

               Let([
               d1 = Date Accepted;
               d2 = Get( CurrentDate );
               yd1 = Year(d1);
               md1 = Month( d1);
               md2 = Month( d2);
               a = Day(d2);
               b = Day(d1);
               y = Year(d2) - yd1 - If( (md2 - md1) < 0 ; 1 ; 0 );
               m = If( (md2 - md1) < 0 ; 12 - md1 + md2 ; md2 - md1 ) - If( a - b < 0; 1 ; 0 );
               d = If( a < b ; a + Choose( md1 ; 0 ; 31 ; 28 + ( Mod( yd1 ; 4) = 0  ) ; 31 ; 30 ; 31 ; 30 ; 31 ; 31 ; 30 ; 31 ; 30 ; 31 ) - b ; a-b )
               ];
               (y *12) + m & " months and " & d & " days"
               )

                

               Then do the same for the Date Discharged - Date Accepted and Service Date Start - Date Accepted

               The calculation result should be set to Text.

                

               Edit: corrected Days calculation

          • 2. Re: Waiting Time in Months and Days
            JimMac

                 For a nominal 30 day month, which is normal banking practise.......

            Int ( (Service Date Start - Date Accepted)/30 )  & " Month(s)"  & " "  &  Mod ( Service Date Start - Date Accepted ; 30 ) & " day(s)"

            Int() function give the 30 day month

            Mod() function gives the remaining days.

                 Jim...

            • 3. Re: Waiting Time in Months and Days
              Jade

                   Geez Jim,  that's a 5 day error per annum.  No wonder banks make so much profit. wink

              • 4. Re: Waiting Time in Months and Days
                JimMac

                     Let me assure you that you pay on 365 days interest, but note terms are based on 30 day intervals normally beginning on the first of month.  Leases work the same way.  A monthly renter gets cheated in Feb, but smiles in March.

                     This question seemed to be a quick look at the total days (no error if you don't count hours in day error potential as partial days].

                     I was guessing it was not a Monetary type look.angel

                     Jim...