# Help with a formula (likely going to be in Sheets)

**URL:** <https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015>\
**Category:** Ask for Help\
**Created:** [November 5, 2020, 5:54pm UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015 "2020-11-05T17:54:13Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![kyleheney](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kyleheney/32/41173_2.png) [@kyleheney](https://community.glideapps.com/u/kyleheney)\
**Post date:** [November 5, 2020, 5:54pm UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/1 "2020-11-05T17:54:13Z")

</div>

I don’t know if this is possible, but I think it should be with some wild sheets formula(s)… hoping a Sheets guru sees this and can lend a hand!

Breakdown:

- Users enter Start time and End time in 15 minute increments.
- I’d like to have a formula that calculates all the 15 minute incremental values in one cell, separated by a comma (I want to use these values for a joined list in my app)

Example: Start Time 8AM; End Time 9AM… result of formula should be 8.00, 8.25, 8.50, 8.75 (I don’t want to include the end time itself, as someone could use the end time as a start time in their next entry).

The only way I can think of doing this inside Glide is to create a TON of columns to add 0.25 to my start time over and over for the whole day. Then I’d somehow use an IFTHEN column to exclude values that are greater than the End time (so that the IFTHEN result only includes times between the start and end time).

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [November 6, 2020, 12:11am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/2 "2020-11-06T00:11:31Z")

</div>

Sheet officer reporting in for help…

Do you want it to be 8:15 AM, 8.30 AM etc. instead of 8.25, 8.50?

---

<div class="post-metadata">

**Author:** ![kyleheney](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kyleheney/32/41173_2.png) [@kyleheney](https://community.glideapps.com/u/kyleheney)\
**Post date:** [November 6, 2020, 1:07am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/3 "2020-11-06T01:07:07Z")

</div>

Hey @ThinhDinh! I knew you’d want to try this one haha

I’d actually prefer if it was just 8.00, 8.25, etc. It’s easier for other calculations in the app

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [November 6, 2020, 1:11am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/4 "2020-11-06T01:11:36Z")

</div>

Let me try it and get back to you later.

---

<div class="post-metadata">

**Author:** ![kyleheney](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kyleheney/32/41173_2.png) [@kyleheney](https://community.glideapps.com/u/kyleheney)\
**Post date:** [November 6, 2020, 1:12am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/5 "2020-11-06T01:12:32Z")

</div>

Thanks for any help (or alternative suggestions) you can provide.

---

<div class="post-metadata">

**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:** [November 6, 2020, 1:41am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/6 "2020-11-06T01:41:27Z")

</div>

If you don’t want the AM/PM indicators, is it safe then to assume you’ll be using a 24hr display format?  
ie. 3:45 PM will appear as 15:45?

---

<div class="post-metadata">

**Author:** ![kyleheney](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kyleheney/32/41173_2.png) [@kyleheney](https://community.glideapps.com/u/kyleheney)\
**Post date:** [November 6, 2020, 1:48am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/7 "2020-11-06T01:48:43Z")

</div>

Yes that is correct.

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [November 6, 2020, 2:14am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/8 "2020-11-06T02:14:18Z")

</div>

> **[Thinh Dinh's lab V2.0](https://docs.google.com/spreadsheets/d/1JzBrp4R1zK9CMfmTzWWcPPep9-ZCl3OXxgWu27NWCy8/edit#gid=1558239636)**
>
> Dynamic column names
> 
> Item 1,Item 2,Item 3,Item 4,Item 5,,,,,,,,,,,,,,
> A
> B
> C,AE,AF,AG,AH,df
> D,AB,AC,AD
> E,EI,EJ,EK,BA
> F

Here’s a working version. What you need is in column E, I suppose.

---

<div class="post-metadata">

**Author:** ![kyleheney](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kyleheney/32/41173_2.png) [@kyleheney](https://community.glideapps.com/u/kyleheney)\
**Post date:** [November 6, 2020, 2:22am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/9 "2020-11-06T02:22:54Z")

</div>

Wow! That looks like it should work! Thanks a ton!!

I’ll keep looking for harder challenges haha

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [November 6, 2020, 3:20am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/10 "2020-11-06T03:20:21Z")

</div>

Must be noted the formulas are in cell C1, D1, E1 and F2. I unhide the supporting calculations in the columns to the right of E.

---

<div class="post-metadata">

**Author:** ![manyone](https://avatars.discourse-cdn.com/v4/letter/m/ee59a6/32.png) [@manyone](https://community.glideapps.com/u/manyone)\
**Post date:** [November 6, 2020, 5:51am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/11 "2020-11-06T05:51:26Z")

</div>

wow! it blows my mind!

---

<div class="post-metadata">

**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:** [November 6, 2020, 11:46am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/12 "2020-11-06T11:46:09Z")

</div>

If you don’t mind using a custom function with a bit of apps script, here is an alternative solution:

```
function get_15_minute_increments(start, end) {
  var start_hh = parseInt(start.split(":")[0]);
  var start_mm = start.split(":")[1];
  var end_hh = parseInt(end.split(":")[0]);
  var end_mm = end.split(":")[1];
  var epoch_start = new Date(0, 0, 0, start_hh, start_mm, 0, 0).getTime();
  var epoch_end = new Date(0, 0, 0, end_hh, end_mm, 0, 0).getTime();  
  var times = [];
  while (epoch_start < epoch_end) {
    var time = new Date(epoch_start);
    times.push(time.getHours() + time.getMinutes()/60);
    epoch_start += (15 * 60 * 1000);
  }
  var str = times.join(', ');
  return str;
}

```

You would call that like any other function, passing your start time and end time as parameters, eg; `=get_15_minute_increments(A1,B1)`

 ![Screen Shot 2020-11-06 at 7.39.47 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/7/7/773f5244a72b530912fc0f0a4217fc7ab7272930.png)

Caveats:

- The Start Time and End Time columns must be formatted as _plain text_
- It won’t work if the end time crosses over into the next day

---

<div class="post-metadata">

**Author:** ![kyleheney](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kyleheney/32/41173_2.png) [@kyleheney](https://community.glideapps.com/u/kyleheney)\
**Post date:** [November 6, 2020, 11:56am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/13 "2020-11-06T11:56:03Z")

</div>

Thanks for this option! I’m going to try both ways and see which one leads to the least delay in the app (since this is a sheets-based process).

---

<div class="post-metadata">

**Author:** ![kyleheney](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kyleheney/32/41173_2.png) [@kyleheney](https://community.glideapps.com/u/kyleheney)\
**Post date:** [November 6, 2020, 2:49pm UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/14 "2020-11-06T14:49:13Z")

</div>

Hi @ThinhDinh… is there any way to maintain the formatting in your Joined hours column? If I’m going to use it, I need the numbers to match the ones in my app for relations to work (so 8 needs to be 8.00, etc.). I tried to change the formatting to plain text, but it still reverts the joined hours column to the incorrect format.

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [November 6, 2020, 3:10pm UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/15 "2020-11-06T15:10:17Z")

</div>

![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/2/2/229209b855c5993d1e44e13dc1409cfb799e1e58.png)

Do you mean like this?

---

<div class="post-metadata">

**Author:** ![kyleheney](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kyleheney/32/41173_2.png) [@kyleheney](https://community.glideapps.com/u/kyleheney)\
**Post date:** [November 6, 2020, 4:18pm UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/16 "2020-11-06T16:18:39Z")

</div>

Bahh how’d you do that?!

---

<div class="post-metadata">

**Author:** ![kyleheney](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kyleheney/32/41173_2.png) [@kyleheney](https://community.glideapps.com/u/kyleheney)\
**Post date:** [November 6, 2020, 4:22pm UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/17 "2020-11-06T16:22:15Z")

</div>

Hi @Darren_Murphy — I want to give your method a go too (I’ll report back my findings on which method worked best so it can help anyone else who might need to do the crazy stuff I’m trying to accomplish).

Is there a way to use your method, but have the start and end times formatted as “8.00” and “9.45” (not with a colon)? I tried by editing the script to split at “.” instead, which works, but then it results in the similar formatting issue that I had with @ThinhDinh’s method (8.00 would get inserted as 8, but I need it to be 8.00).

The other caveats you mentioned shouldn’t be an issue.

---

<div class="post-metadata">

**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:** [November 6, 2020, 5:01pm UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/18 "2020-11-06T17:01:44Z")

</div>

> [@kyleheney](#):
>
> Is there a way to use your method, but have the start and end times formatted as “8.00” and “9.45” (not with a colon)?

Give this a go:

```
function get_15_minute_increments(start, end) {
  var start_hh = parseInt(start.split('.')[0]);
  var start_mm = start.split('.')[1];
  var end_hh = parseInt(end.split('.')[0]);
  var end_mm = end.split('.')[1];
  var epoch_start = new Date(0, 0, 0, start_hh, start_mm, 0, 0).getTime();
  var epoch_end = new Date(0, 0, 0, end_hh, end_mm, 0, 0).getTime();  
  var times = [];
  while (epoch_start < epoch_end) {
    var time = new Date(epoch_start);
    var h = time.getHours();
    var m = (time.getMinutes()/60).toFixed(2);
    m = m.slice(-2);
    times.push(h + '.' + m);
    epoch_start += (15 * 60 * 1000);
  }
  return times.join(', ');
}

```

---

<div class="post-metadata">

**Author:** ![kyleheney](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kyleheney/32/41173_2.png) [@kyleheney](https://community.glideapps.com/u/kyleheney)\
**Post date:** [November 6, 2020, 5:05pm UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/19 "2020-11-06T17:05:26Z")

</div>

> [@Darren\_Murphy](#):
>
> ```auto
> function get_15_minute_increments(start, end) {
> var start_hh = parseInt(start.split('.')[0]);
> var start_mm = start.split('.')[1];
> var end_hh = parseInt(end.split('.')[0]);
> var end_mm = end.split('.')[1];
> var epoch_start = new Date(0, 0, 0, start_hh, start_mm, 0, 0).getTime();
> var epoch_end = new Date(0, 0, 0, end_hh, end_mm, 0, 0).getTime();  
> var times = [];
> while (epoch_start < epoch_end) {
> var time = new Date(epoch_start);
> var h = time.getHours();
> var m = (time.getMinutes()/60).toFixed(2);
> m = m.slice(-2);
> times.push(h + '.' + m);
> epoch_start += (15 * 60 * 1000);
> }
> return times.join(', ');
> }
> 
> ```

That worked! Last question on your option… is there way to put the function in an arrayformula? I’ve never used a function like this before (just onChange scripts), so I’m having trouble figuring out how to apply this to all rows.

---

<div class="post-metadata">

**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:** [November 6, 2020, 5:10pm UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/20 "2020-11-06T17:10:25Z")

</div>

> [@kyleheney](#):
>
> is there way to put the function in an arrayformula?

I expect so - I don’t use array formulas much, so I didn’t consider that.  
I’ll have a fiddle and let you know.

[Next page](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015.md?page=2)