# 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

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
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?

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.