Ron Neville Posted October 12, 2016 Share Posted October 12, 2016 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 Link to comment Share on other sites More sharing options...
comment Posted October 12, 2016 Share Posted October 12, 2016 (edited) 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, 2016 by comment 1 Link to comment Share on other sites More sharing options...
LaRetta Posted October 12, 2016 Share Posted October 12, 2016 Sweet! Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 2881 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now