# Google Sheet formula to apply on Glide
**URL:** https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802
**Category:** Ask for Help
**Created:** [August 26, 2022, 7:07am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802 "2022-08-26T07:07:21Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@biha](https://community.glideapps.com/u/biha)
#### Post date: [August 26, 2022, 7:07am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/1 "2022-08-26T07:07:21Z")
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?
---
### Author:  [@Uzo](https://community.glideapps.com/u/Uzo)
#### Post date: [August 26, 2022, 7:17am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/2 "2022-08-26T07:17:07Z")
use the Glide excel column and enter your formula
---
### Author:  [@biha](https://community.glideapps.com/u/biha)
#### Post date: [August 26, 2022, 7:25am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/3 "2022-08-26T07:25:05Z")
Oh! Got new plugin , just noticed.
Tried it but, why is not workss?
---
### Author:  [@Uzo](https://community.glideapps.com/u/Uzo)
#### Post date: [August 26, 2022, 7:26am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/4 "2022-08-26T07:26:27Z")
let me test it
---
### Author:  [@biha](https://community.glideapps.com/u/biha)
#### Post date: [August 26, 2022, 7:27am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/5 "2022-08-26T07:27:10Z")
Ok
MAX(MIN(A1,A2)-MAX(A3,A4)+1,0)
---
### Author:  [@Uzo](https://community.glideapps.com/u/Uzo)
#### Post date: [August 26, 2022, 7:56am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/6 "2022-08-26T07:56:55Z")
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
---
### Author:  [@biha](https://community.glideapps.com/u/biha)
#### Post date: [August 26, 2022, 8:11am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/7 "2022-08-26T08:11:39Z")
so need to do two math columns?
---
### Author:  [@Uzo](https://community.glideapps.com/u/Uzo)
#### Post date: [August 26, 2022, 8:15am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/8 "2022-08-26T08:15:38Z")
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
---
### Author:  [@biha](https://community.glideapps.com/u/biha)
#### Post date: [August 26, 2022, 8:16am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/9 "2022-08-26T08:16:37Z")
ok… no idea for now…
---
### Author:  [@Uzo](https://community.glideapps.com/u/Uzo)
#### Post date: [August 26, 2022, 8:17am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/10 "2022-08-26T08:17:46Z")
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
---
### Author:  [@biha](https://community.glideapps.com/u/biha)
#### Post date: [August 26, 2022, 8:22am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/11 "2022-08-26T08:22:15Z")
appreciated if you share a little bit more details.
---
### Author:  [@Uzo](https://community.glideapps.com/u/Uzo)
#### Post date: [August 26, 2022, 8:24am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/12 "2022-08-26T08:24:34Z")
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
---
### Author:  [@biha](https://community.glideapps.com/u/biha)
#### Post date: [August 26, 2022, 8:27am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/13 "2022-08-26T08:27:23Z")
ok… have a tight sleep
---
### Author:  [@biha](https://community.glideapps.com/u/biha)
#### Post date: [August 26, 2022, 8:31am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/14 "2022-08-26T08:31:44Z")
I’m very appreciate, if anyone else could help me …
@iamtomgray maybe you could help?
---
### Author:  [@iamtomgray](https://community.glideapps.com/u/iamtomgray)
#### Post date: [August 26, 2022, 9:05am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/15 "2022-08-26T09:05:32Z")
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.
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [August 26, 2022, 10:33am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/16 "2022-08-26T10:33:49Z")
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
```auto
S1 E1 S2 E2
<-----------> <----------->
```
Case 2 - First range is contained within the second range
```auto
S1 E1
<----------->
S2 E2
<------------------------>
```
Case 3 - Second range is contained within the first range
```auto
S2 E2
<----------->
S1 E1
<------------------------>
```
Case 4 - Overlapping, with second end date after the first end date
```auto
S1 E1
<-------------------------->
S2 E2
<------------------------------>
```
Case 5 - Overlapping, with first end date after the second end date
```auto
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
```auto
Floor(E1-S1)
```
- A math column to calculate the result for Case 3
```auto
Floor(E2-S2)
```
- A math column to calculate the result for Case 4
```auto
Floor(E2-S1)
- Floor(S2-S1)
- Floor(E2-E1)
```
- A math column to calculate the result for Case 5
```auto
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 🤪
---
### Author:  [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)
#### Post date: [August 26, 2022, 3:01pm UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/17 "2022-08-26T15:01:13Z")
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:
```auto
MAX(MIN(end1-seed, end2-seed)-MAX(start1-seed,start2-seed)+1,0)
```
@Darren_Murphy, here is your 🍺 and a single column solution. 🤪😉
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [August 26, 2022, 3:06pm UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/18 "2022-08-26T15:06:48Z")
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 😉
---
### Author:  [@Uzo](https://community.glideapps.com/u/Uzo)
#### Post date: [August 27, 2022, 4:40am UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/19 "2022-08-27T04:40:09Z")
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
---
### Author:  [@gvalero](https://community.glideapps.com/u/gvalero)
#### Post date: [August 27, 2022, 12:47pm UTC](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802/20 "2022-08-27T12:47:33Z")
> [@biha](#):
>
> 
>
> image632×836 45.2 KB
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!
[Next page](https://community.glideapps.com/t/google-sheet-formula-to-apply-on-glide/47802.md?page=2)