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.