# End Date - Start Date Calculation **URL:** \ **Category:** Ask for Help\ **Created:** [December 7, 2021, 2:09am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606 "2021-12-07T02:09:43Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![biha](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/biha/32/84110_2.png) [@biha](https://community.glideapps.com/u/biha)\ **Post date:** [December 7, 2021, 2:09am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/1 "2021-12-07T02:09:43Z")
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. ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [December 7, 2021, 3:18am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/2 "2021-12-07T03:18:48Z")
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 ---
**Author:** ![biha](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/biha/32/84110_2.png) [@biha](https://community.glideapps.com/u/biha)\ **Post date:** [December 7, 2021, 3:22am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/3 "2021-12-07T03:22:59Z")
> [@Uzo](#): > > or now() OR means i have to use if -then column right ? ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [December 7, 2021, 3:25am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/4 "2021-12-07T03:25:10Z")
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 ---
**Author:** ![biha](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/biha/32/84110_2.png) [@biha](https://community.glideapps.com/u/biha)\ **Post date:** [December 7, 2021, 3:26am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/5 "2021-12-07T03:26:03Z")
No, we have end date . But if the end date is after today , then today. ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [December 7, 2021, 3:26am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/6 "2021-12-07T03:26:54Z")
then just end date - start date ---
**Author:** ![biha](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/biha/32/84110_2.png) [@biha](https://community.glideapps.com/u/biha)\ **Post date:** [December 7, 2021, 3:27am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/7 "2021-12-07T03:27:12Z")
I have done your idea just now, but this is my problem > [@biha](#): > > 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. ---
**Author:** ![biha](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/biha/32/84110_2.png) [@biha](https://community.glideapps.com/u/biha)\ **Post date:** [December 7, 2021, 3:27am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/8 "2021-12-07T03:27:50Z")
Uzo, please read my elaboration above. ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [December 7, 2021, 3:27am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/9 "2021-12-07T03:27:56Z")
read my post again… create a duration column i have this problem fixed in this sample: > **[Personal Time Card DEMO](https://time-card-demo.glideapp.io/dl/ewAiAHQAIgA6ADAALAAiAHMAIgA6ACIAYwBsAGEAcwBzAC0AVABpAG0AZQAgAEMAYQByAGQAIgAsACIAcgAiADoAIgBIAHMAcAB1AEUAcwAzAEgAVABGAEcARQA1AG4ANwAyAHQAZQBKADYAWABRACIALAAiAG4AIgA6ACIAUABlAHIAcwBvAG4AYQBsACAAVABpAG0AZQAgAEMAYQByAGQAIgB9AA==)** > > Track your working hours, sort by week, month, or year. Export as PDF files ---
**Author:** ![biha](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/biha/32/84110_2.png) [@biha](https://community.glideapps.com/u/biha)\ **Post date:** [December 7, 2021, 3:30am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/10 "2021-12-07T03:30:58Z")
> [@Uzo](#): > > use math column end date (or now()) - start date… you will get duration… 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 ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [December 7, 2021, 3:33am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/11 "2021-12-07T03:33:57Z")
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 ---
**Author:** ![biha](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/biha/32/84110_2.png) [@biha](https://community.glideapps.com/u/biha)\ **Post date:** [December 7, 2021, 3:39am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/12 "2021-12-07T03:39:48Z")
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… ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [December 7, 2021, 3:42am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/13 "2021-12-07T03:42:11Z")
yes, have all 3 rate calculations, and show the relevant one for the customer ---
**Author:** ![biha](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/biha/32/84110_2.png) [@biha](https://community.glideapps.com/u/biha)\ **Post date:** [December 7, 2021, 3:43am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/14 "2021-12-07T03:43:25Z")
if then column also we should more than one ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [December 7, 2021, 3:44am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/15 "2021-12-07T03:44:14Z")
as much you need, glide counts only rows… not columns 😉 use duplicate column, to make it fast ---
**Author:** ![ShantanuIyengar](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/shantanuiyengar/32/35546_2.png) [@ShantanuIyengar](https://community.glideapps.com/u/ShantanuIyengar)\ **Post date:** [December 7, 2021, 8:06am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/16 "2021-12-07T08:06:29Z")
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. > **[How to Count the Days Between Two Dates in Google Sheets](https://www.howtogeek.com/447556/count-the-days-between-two-dates-in-google-sheets/)** > > If you want to count the number of days between two dates, you can use the DAYS, DATEDIF, and NETWORKDAYS functions in Google Sheets to do so. DAYS and DATEDIF count all days, while NETWORKDAYS excludes Saturday and Sunday. The same formula can be used in hyperformula ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [December 7, 2021, 8:21am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/17 "2021-12-07T08:21:51Z")
um, why use hyperformula for something that’s trivial with Glide Date math? 🤔 ![Screen Shot 2021-12-07 at 4.20.33 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/3/0/306f58ac8c94b31701240e020a0ae57cff5e6036.png) ---
**Author:** ![ShantanuIyengar](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/shantanuiyengar/32/35546_2.png) [@ShantanuIyengar](https://community.glideapps.com/u/ShantanuIyengar)\ **Post date:** [December 7, 2021, 8:22am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/18 "2021-12-07T08:22:43Z")
Hyperformula can also take text entries and thus solving the issue of invalid operation on dates! Direct Glide is much easier though! ---
**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\ **Post date:** [December 7, 2021, 8:29am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/19 "2021-12-07T08:29:41Z")
why all this trouble if the simple math column is doing this? just have the right order of calculating columns ---
**Author:** ![biha](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/biha/32/84110_2.png) [@biha](https://community.glideapps.com/u/biha)\ **Post date:** [December 7, 2021, 8:50am UTC](https://community.glideapps.com/t/end-date-start-date-calculation/35606/20 "2021-12-07T08:50:24Z")
Because simple flow cannot using math column. Then, have to use very long flow. [Next page](https://community.glideapps.com/t/end-date-start-date-calculation/35606.md?page=2)