I have two columns with one is Start Date and another one is End Date.
I need to do as of today Potential Income.
Basic information is End Date - Start Date for Machine Rental and for each machine got Rental Rate and its different for each daily rate, weekly rate and monthly rate.
Example rate Machine A : Daily Rate - RM200 Weekly Rate - RM800, Monthly Rate RM3,000.
Which :
Customer A rent Machine A from 1/12/2021 until 9/12/2021 for weekly basis.
Rate for one week : RM800
Actual Price to be Paid : (RM800/7) x 9 days = RM1028.57
Today’s Date : 7/12/2021
Start Date until Today’s Date ; 7 days
AS OF TODAY’s POTENTIAL INCOME : RM800
In order to get the “Today’s Potential Income”
I use one column if-then : if End Date is after Today , then Today else End Date.
My problem is the the if-then column cannot be substract with Start Date as error “Invalid operation in date/time”
I also try to use the Math and also Template Column reflected to the if-then column but the results wrong.
use math column end date (or now()) - start date… you will get duration… multiple that by 24… that will give you hours… if you multiply by x24x60 that will give you minutes… now you can use that values to determine rental rates
I mean if you don’t have an end date, because someone is still renting the tool, use NOW value… simply create an IF-ELSE column to check if the end date is empty then NOW() ELSE end date
I understand this is the first step, and my problem to get the duration
At first , I use one column if-then : if End Date is after Today , then Today else End Date.
The math column > the if then - start date ///here is the problem
calculate duration first, then use the if-else column to check if the duration is over 24 hours…
or if is after today then show the duration…
do not get data to calculate duration from if-else column… just calculate it first and then use if-else column to determine what to do with the data
I would suggest you to use the code and hyperformula for the same.
Theres a formula called “Days” which takes input of two dates and gives in return the no of days between.
Detailed info can be found below.