HI Guys firstly thank you for an amazing resource... I am building a rental system and the period could either be "Weekly" or "Monthly" so say it's set to weekly and the item is $100 per week and when the user goes to create the invoice they are prompted to do a Pro Rata invoice or normal. If Pro Rata I need to know how many days left in the current week, e.g 3 days then I would have to divide the $100 p/w fee by 7 and times it by 3. And the same if its monthly I need to get the amount of days till the end of month, divide the monthly fee by number of days in the month and times it by the number of days left in the month. Companies do this to move into a 1st of month/week billing cycle.

Thank you for any help regards Ron

 

Try something like:

Case (
Period = "Weekly" ;
8 - DayOfWeek ( StartDate ) ;

Period = "Monthly" ;
Date ( Month ( StartDate ) + 1 ; 1 ; Year ( StartDate ) ) - StartDate
)

This is assuming that your week starts on Sunday.

 

6 hours ago, Ron Neville said:

when the user goes to create the invoice they are prompted to do a Pro Rata invoice or normal.

Couldn't this be automated (e.g. if the starting date is not the 1st of the month or a Sunday, then ... )?

---

Note that the above will calculate the number of days in the period being billed. If you prefer, you could extend it to calculate the percentage of the rent to be applied:

Case (
Period = "Weekly" ;
( 8 - DayOfWeek ( StartDate ) ) / 7;

Period = "Monthly" ;
Let ( [
daysInMonth = Day ( Date ( Month ( StartDate ) + 1 ; 0 ; Year ( StartDate ) ) ) ;
proRataDays = daysInMonth - Day ( StartDate ) + 1
] ;
proRataDays / daysInMonth
)
)

This will return 1 (100%) when the period being billed starts on the first day of the week/month - so you could apply this unconditionally.
 

 

Edited October 12, 20169 yr by comment

Sweet!