End Date - Start Date Calculation

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

OR means i have to use if -then column right ?

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

No, we have end date .
But if the end date is after today , then today.

then just end date - start date

I have done your idea just now, but this is my problem

Uzo, please read my elaboration above.

read my post again… create a duration column
i have this problem fixed in this sample:

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

For your information , after I calculate the duration , i have to calculated for each daily , weekly , and mothly rate, and years rates

so, i should have two columns for durations , two daily rate column(for actual end date/now date) ,two weekly rate and etc… isn’t?

Only then , if then column.
like so many column…

yes, have all 3 rate calculations, and show the relevant one for the customer

if then column also we should more than one

as much you need, glide counts only rows… not columns :wink:
use duplicate column, to make it fast

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.

The same formula can be used in hyperformula

um, why use hyperformula for something that’s trivial with Glide Date math? :thinking:

2 Likes

Hyperformula can also take text entries and thus solving the issue of invalid operation on dates!
Direct Glide is much easier though!

why all this trouble if the simple math column is doing this? just have the right order of calculating columns

Because simple flow cannot using math column. Then, have to use very long flow.