9 Replies Latest reply on Dec 16, 2014 3:26 PM by philmodjunk

    Calculating number of days in the "Selected period" between two dates?

    MikhailSmirnov

      Title

      Calculating number of days in the "Selected period" between two dates?

      Post

      Hello everyone,

      Does anyone knows how to calculate number of days in the "Selected period" between two dates?

      I'm trying to create Monthly payments calculation by number of days in a "Selected period".

      Our students live in accommodation and pay by nights. The dates could be like: From 15/01/2015 To 20/03/2015. We need to split these dates by number of days in each month period to get Monthly payments?

      1.jpg

        • 1. Re: Calculating number of days in the "Selected period" between two dates?
          MikhailSmirnov

          Hi, 

          In "Monthly Payment" Field must be summary of payments by each month.

          How could it be done?

          • 2. Re: Calculating number of days in the "Selected period" between two dates?
            philmodjunk

            Seems like you'd need a looping script to generate the set of records from the start to end dates. You can  increment the month with each time the script loops and subtract the dates to get the number of nights in each month.

            • 3. Re: Calculating number of days in the "Selected period" between two dates?
              MikhailSmirnov

              Hello PhilModJunk,

              Thank you for your help.

              I've been searching all around the Internet about this case. I still have little idea how to make this calculation work.

              That how I see possible solution: I need to calculate number of days in the selected period between two dates - "Date From" and "Date To":

              So, I added to my table two fields (Date, Global) "FirstDateCalculation" and "LastDateCalculation".


              I need to create formula:


              A: (Calculation, Number) =("FirstDateCalculation" - "Date From") Only If("FirstDateCalculation" ≥ "Date From"

              B: (Calculation, Number) =("Date To" - "LastDateCalculation") Only If("Date To" ≥ "LastDateCalculation")

              Number of days in period between two dates: (Calculation, Number) =("Date To" - "Date From") - (A + B)

              Does it possible?

              • 4. Re: Calculating number of days in the "Selected period" between two dates?
                philmodjunk

                I need to calculate number of days in period between two dates

                That part is simple, doesn't seem like you needed that assistance.

                DateEnd - DateStart returns the number of days form DateStart to DateEnd

                It would seem that dividing such an interval of days into a) one partial month, b) 0 to N full months, c) one more partial month is the real problem here.

                I would set up a script to generate a series of records for each of those time periods from the initially specified date interval:

                If [Not IsEmpty ( YourTable::DateStart ) and YourTable::DateEnd > YourTable::DateStart ]
                  Set Variable [$ID ;value: YourTable::__pkPrimaryKey ]
                  Set Variable [$Date1 ; value: YourTable::DateStart ]
                  Set Variable [$Date2 ; value: YourTable::DateEnd ]
                  Go to Layout ["MonthIntervals" ; (MonthIntervals) ]
                  New Record/Request
                  Set Field [MonthIntervals::ForeignKey ; $ID ]
                  Set Field [ MonthIntervals::DateOne ; $Date1 ]
                  Set FIeld [MonthIntervals::DateTwo ; Min ( $Date2 ; Date ( Day ( Month ( $Date1 ) + 1 ; 0 ; Year ( $Date1 ) ) ]
                  Loop
                     New Record/Request
                     Set Field [MonthIntervals::ForeignKey ; $ID ]
                     Set Field [ MonthIntervals::DateOne ; $Date1 ]
                     Set FIeld [MonthIntervals::DateTwo ; Min ( $Date2 ; Date ( Day ( Month ( $Date1 ) + 1 ; 0 ; Year ( $Date1 ) ) ]
                     Set Variable [ $Date1 ; Date ( Month ( $Date1 ) + 1 ; 1 ; Year ( $Date1 ) ) ]
                     Exit Script If [ $Date1 > $Date2 ]
                  End Loop
                  Go to Layout [original layout]
                End IF

                A portal to MontyIntevals on your original layout can show the records created by this script.

                A calculation field in MonthIntervals can be defined as DateTwo - DateOne to show the number of days in that interval.

                • 5. Re: Calculating number of days in the "Selected period" between two dates?
                  MikhailSmirnov

                  Hi Phill, I very new in FM 13 Pro.

                  Interesting idea with a looping script. I will use it in the "Students attendance" Calculation.

                  In My case it is just a mathematics. I'm not sure if I need to generate billions of rows with a looping script to calculate this "Selected period" between two dates. Maybe I wrong.

                  • 6. Re: Calculating number of days in the "Selected period" between two dates?
                    MikhailSmirnov

                    Hi guys,

                    Please, help me to improve formula(s) to calculate "Selected period" between two dates.

                    This one below works as I need but I still have some doubts.

                    # Calculating number of days in the "Selected period" between two dates:

                     

                    # Field_1 =(Student's first day of renting accommodation)
                    DateFrom (Date; Indexed)

                    # Field_2 =(Student's last day of renting accommodation)
                    DateTo (Date; Indexed)

                    # Field_3 =(Calculating number of Nights between "DateFrom" and "DateTo" only If (DateTo ≥ DateFrom) Else = 0)
                    Nights (Calculation; Number) = [Case ( DateTo ≥ DateFrom; DateTo - DateFrom; 0 )]

                    # Field_4 =(First date of calculation period, Global for all fields)
                    FirstDateCalculation (Date, Global)

                    # Field_5 =(Last date of calculation period, Global for all fields)
                    LastDateCalculation (Date, Global)

                    #Field_6 =(Accommodation Fee per Night)
                    Rate (Number; Indexed)

                    # Start Calculating number of days in the "Selected period" between two dates:

                     

                    # In Calculation_1 we’re calculating number of days in the period between ("DateFrom" and "FirstDateCalculation") must be ≥ 0.
                    Calculation_1 = [Case ( not IsEmpty ( DateFrom ) and DateTo > DateFrom and FirstDateCalculation ≥ DateFrom; FirstDateCalculation - DateFrom; 0 )]

                    # In Calculation_2 we’re calculating number of days in the period between ("LastDateCalculation" and "DateTo") must be ≥ 0.
                    Calculation_2 = [Case ( not IsEmpty ( DateFrom ) and DateTo > DateFrom and DateTo ≥ LastDateCalculation; DateTo - LastDateCalculation; 0 )]

                    # In Calculation_3 we’re calculating number of days before and after "Selected period" between two dates.
                    Calculation_3 = [Calculation_1 + Calculation_2]

                    # In Calculation_4 we’re calculating number of days in the "Selected period".
                    Calculation_4 = [Case ( Calculation_3 ≥ 0; Nights - Calculation_3 )]

                    # In Calculation_5 we’re just reassure "Calculation4" ≥, Else = 0.
                    Calculation_5 = [Case ( Calculation_4 ≥ 0; Calculation_4;0 )]

                    # Now we're calculating "Selected Period Total" in Euro
                    Calculation_6 = [Calculation_5 * Rate]

                    # End of calculation

                    Thanks

                    • 7. Re: Calculating number of days in the "Selected period" between two dates?
                      philmodjunk

                      As I stated earlier, the number of days between two dates is simply a case of subtracting Date 1 from Date 2. The only extra code you need is to handle data entry errors such as entering the dates out of order or leaving one field empty. You don't need a lot of extra fields just to compute the number of days between two dates.

                      • 8. Re: Calculating number of days in the "Selected period" between two dates?
                        MikhailSmirnov

                        Hi Phill,

                        I'm giving up...

                        "subtracting "Date1" from "Date2" simply doesn't work...

                        In my case I have two dates, "Date1" and "Date2" (Could be any period throughout a year) and it's really easy to calculate days between them. However, I also have "Global" dates, - "Date3" and "Date4" (Month). They are not just two dates but a range of the days. I need these dates to sum up number of days in the periods between "Date1" and "Date2". Problem is I have hundreds of "Date1-Date2" periods throughout the year. I need to calculate only those days, that are in the range between "Date3" and "Date4"


                        Sorry, I don't know how to explain it more clear.

                        • 9. Re: Calculating number of days in the "Selected period" between two dates?
                          philmodjunk

                          I think you need to take another look at my suggested script. Your original post described this date interval:

                          15/01/2015 To 20/03/2015

                          I read that as the interval from January 15 to March 20 of the year 2015. My script should generate the following records in a portal:

                          15/1/2015         31/1/2015        16 days
                          1/2/2015            28/1/2015        28 days
                          1/3/2015            20/3/2015        20 days

                          Isn't that what you wanted?

                          This allows you to set up a list of monthly billing records for each person--each staying a different number of  days.