I need your help on this. I need to determine the utilization on a Machine Rental in Monthly basis.
I have a column for Start Date on Machine Rental and another one column for End Date.
I should have another one column for the total days in a month don’t have an idea how.
Eg : if January > 31, if February >28 and etc.
Let’s say rental date for Machine A start date 1/1/2021 - 4/2/2021 , so the utilization for Machine A in January is 100% where the utilization for Machine A in February is 14% (4 divide by 28 x 100).
For days calculations between dates you can use either the math column in glide (Start date - end date should give you the numbers of days) or in the google sheet (I would advise the math column though).
The end of the day, I need the chart represent for each Machine and its utilization.
I noted that the End date - Start Date will be give us the days number. But how do we substitute Month in the date range ? What I have to do ?
How do you determine in advance how many months are there to show utilization for each month though? Let’s say I have 15 months of usage for a machine, then you’ll have to show me the utilization in each of those 15 months?
I need the chart like for the year 2021, how much the utilization percentage in January , Feb, March etc.
Let’s say today is 20/10/2021 if data for Machine D got rental from 1/8/2021 - 15/9/2021 and 21/9/2021 - 29/10/2021
August will be 100% utilization for Machine D
September will be 15 (1/9/2021 - 15/9/2021) + 10 (21/9/2021 - 31/10/2021) = 25 / 31 days = 80.56%
October will be 100% calculate from 1/1/2021 until today.
This is a tricky one.
I think it will be possible in Glide, but I suspect it won’t be a simple solution.
Can I assume that there could be several rows for each machine per month?
eg. Machine A might be rented from Aug 1 to Aug 5, and then again from Aug 11 to Aug 23
This is the sort of problem for which I might end up turning to Apps Script for a solution. I’ll give it some thought, and if I come up with a nice solution I’ll let you know.
All that said, Jeff will probably come along and show us how to do it with a single math column
I’ve used the if-the-column for Start and End date.
If the Start date is before 1st Jan , it will be the 1st Jan else it will be the original Start Date.
If End date is after today, it will be today else the original end date. also if end date before 1st Jan it will be 1st Jan.
mmm yes, this is tricky.
The biggest challenge is the fact that you have rentals that span several months, and even years.
I think it’s easy enough to show the utilisation for any given machine for a single month, but if I understand correctly, you want to select a machine and the show the utilisation for that machine for all months in a single table - month by month. Is that correct?