Date range is within date range

Ask for Help
1

Hi,

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).

2

Hello Biha,

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).
MArco

1 Like
3

Thanks for replying.

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 ?

4

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?

1 Like
5

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.

6

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 :joy:

2 Likes
7

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.

So far , am i doing right ?

8

As I said, I don’t think there will be a simple solution for this, and I don’t have one right now.

What is the answer to this question - yes or no?

9

Yes, you are right

10

Okay, I have an idea.

Can you please share a google sheet with some sample data that I can work with?

11

Hee you go,

12

In that sheet, which two dates represent the hire start and end dates?
Is that “MOB Date” and “Demob Date”?

Also, is it safe to assume there will never be overlapping dates for the same machine?
eg.
Machine A: Aug 15 to Aug 23
Machine A: Aug 20 to Aug 28

That would never happen, yes?

13

Yes, you are correct. It will never happen.

Also, correct.

14

Okay, cool.
Just give me a little while to have a play with this.

15

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?

16

Yes,but for the year 2021 only. Jan2021, Feb2021, March2021 etc.

17

Let’s say,

Machine E 30/12/2021 - 1/9/2021 consider 1/1/2021 - 1/9/2021
Machine E 10/9/2021 - 31/12/2021 consider 10/9/2021 until today

Machine E utilization
Jan 2021, Feb 2021 March 2021 until Aug 2021 : 100%
Sept 2021 1/9/2021, & 10/9/2021 - 30/9/2021 : 1+ 21 = 22 days / 30 days = 73%

18

yes, understand.
Let me think about this some more. I have some work to do right now, so I’ll probably come back to it later.

19

Thanks to you Darren, thanks.

20

Okay, I know this probably isn’t exactly what you want, but I think it gets you part of the way there…

Here’s the link to the app. Make a copy and have a play with it. If you have questions, let me know and I’ll do my best to answer them.