Basically I need to know how many days were overlapping between two dates range.
In google sheets,
the formula would be :
Summary
=MAX(MIN(end1, end2)-MAX(start1,start2)+1,0)
My question is how to do in Glide?
use the Glide excel column and enter your formula
let me test it
Ok
MAX(MIN(A1,A2)-MAX(A3,A4)+1,0)
use a math column
floor(date1end-date2start)
and check first with the if-else column if they are overlapping: if date1end > date2start
or simply if the result is greater than 0
so need to do two math columns?
maybe more… you also need to check if date 1 start is greater than date 2 start… and date 1 end greater than date 2 end and do math on that 
and yea… that excel formula is not working on dates
ok… no idea for now…
i believe it has something to do with how Glide is formatting the dates…
just follow the logic of the math formula I give you to check the other 2 instances and you will get your overlapping number
appreciated if you share a little bit more details.
there is a faster way to do it using date difference code:
you need to do that 3 times and use if else column to check if the dates are inside each other
i have to go to sleep… that’s all i can do for today
ok… have a tight sleep
1 Like
I’m very appreciate, if anyone else could help me …
@iamtomgray maybe you could help?
Hi @biha, happy to help! I’m connecting you with certied experts and spreadsheet wizards, @Darren_Murphy & @Robert_Petitto, who can point you in the right direction.
1 Like
mmm, doing this with pure Glide computed columns is a bit tricky. I had a play with this, and I think there are 5 cases you need to deal with:
Case 1 - No Overlap
S1 E1 S2 E2
<-----------> <----------->
Case 2 - First range is contained within the second range
S1 E1
<----------->
S2 E2
<------------------------>
Case 3 - Second range is contained within the first range
S2 E2
<----------->
S1 E1
<------------------------>
Case 4 - Overlapping, with second end date after the first end date
S1 E1
<-------------------------->
S2 E2
<------------------------------>
Case 5 - Overlapping, with first end date after the second end date
S1 E1
<------------------------->
S2 E2
<-------------------------->
So after a bit of fiddling around, I managed to come up with a solution. It requires 7 columns:
- A math column to calculate the result for Case 2
Floor(E1-S1)
- A math column to calculate the result for Case 3
Floor(E2-S2)
- A math column to calculate the result for Case 4
Floor(E2-S1)
- Floor(S2-S1)
- Floor(E2-E1)
- A math column to calculate the result for Case 5
Floor(E1-S2)
- Floor(S1-S2)
- Floor(E1-E2)
- An if-then-else column to determine if Case 2 should be applied
– If S1 is before S2, then null
– If E1 is after E2, then null
– Else true - An if-then-else column to determine if Case 3 should be applied
– If E2 is after E1, then null
– If S2 is before S1, then null
– Else true - And a final if-then-else column to return the result
– If S1 is after E2, then 0 (Case 1)
– If S2 is after E1, then 0 (Case 1)
– If FirstWithinSecond is checked, then E1-S1 (Case 2)
– If SecondWithinFirst is checked, then E2-S2 (Case 3)
– If E2 is after E1, then E2Latest (Case 4)
– Else E1Latest (Case 5)
Here is how that looks:
I haven’t tested it extensively, so quite possibly there are some more cases that I’ve overlooked, or some errors in my logic.
This is a situation where I’d probably just use a single JavaScript column instead, or maybe try and figure out how to get the Excel plugin to work (but I usually try and avoid that one). But it’s fun to try and nut these things out using pure Glide computed columns.
It’s also quite possible that I’ve completely overthought this one and made it way more complicated than it needs to be, so now I’m looking forward to the single column solution that @Jeff_Hager comes up with 
2 Likes
I used @Darren_Murphy’s sample dates and came up with a way to do it with 2 columns. I’m not sure if the results are what you want, but it seems to be giving me the correct number of overlap days.
First you need a seed date. This is a date that’s populated across all rows. I used the Text To Date plugin to create this. I chose January 1st 2000 as a seed date, but it probably doesn’t matter which date you use.
Then using your formula as a base, I created a math column that also uses the seed date to convert the Start and End dates into numbers, so the math formula will work with numbers instead of dates.
This is what the final table looks like.
BUT, technically you don’t even need a Seed Date column as any date will work. The solution can simply be one single math column that uses one of the existing date columns as a seed date. So, the math column would look like this. Notice End1 is used for both the End1 replacement as well as the Seed replacement.
and you end up with this.
Here is the math formula:
MAX(MIN(end1-seed, end2-seed)-MAX(start1-seed,start2-seed)+1,0)
@Darren_Murphy, here is your 
and a single column solution.
6 Likes
Wonderful!
And it’s a lot simpler than I expected. Never occurred to me to convert the dates to numbers, but it makes so much sense.
I knew you wouldn’t let me down
2 Likes
I did come up with the same conclusion to convert dates to numbers, Glide should fix this date problem, and do like Google is doing… it is a number! and then you can convert it to whatever you need by changing the column format, which will simplify all math and logic struggling
Hola!
To make it works properly, your date value needs to be a text type variable, not a date type.
Use a template column to convert your dates to text and then, apply the Excel plugin again.
Saludos!
2 Likes