
1. Re: Calculating number of days in the "Selected period" between two dates?
MikhailSmirnov Dec 11, 2014 4:06 AM (in response to 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 Dec 11, 2014 7:37 AM (in response to MikhailSmirnov)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 Dec 14, 2014 9:06 AM (in response to 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 Dec 13, 2014 11:20 AM (in response to MikhailSmirnov)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 IFA 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 Dec 14, 2014 9:25 AM (in response to 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 Dec 15, 2014 3:34 AM (in response to 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 Dec 15, 2014 11:38 AM (in response to MikhailSmirnov)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 Dec 16, 2014 3:33 AM (in response to 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 "Date1Date2" 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 Dec 16, 2014 3:26 PM (in response to MikhailSmirnov)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 daysIsn't that what you wanted?
This allows you to set up a list of monthly billing records for each personeach staying a different number of days.