# 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:** 2

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

</div>

It’s in the same sheet link as above, I forced it to follow a “0.00” format using TEXT.

Darren’s solution is brilliant as well but mind the arrayformula problem, probably needs an additional script for that.

---

<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:51pm UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/22 "2020-11-06T17:51:49Z")

</div>

> [@ThinhDinh](#):
>
> Darren’s solution is brilliant as well but mind the arrayformula problem, probably needs an additional script for that.

yep, it seems so. I couldn’t figure out how to use it with an array formula.  
@kyleheney - Here’s the additional script that could be used as a trigger:

```
function on_sheet_change(event) {
  var sheetname = event.source.getActiveSheet().getName();
  if (sheetname == 'Test') { // Change this
    var sheet = event.source.getActiveSheet();
    // Assumes that start time is col1 and end time is col2
    var data = sheet.getRange(2,1,sheet.getLastRow()-1,2).getValues();
    var times = [];
    data.forEach(function (pair) {
      times.push(get_15_minute_increments(pair[0], pair[1]));
    });
    var row = 2;
    while (times.length > 0) {
      // Assumes that intervals should be written to col3
      sheet.getRange(row++,3).setValue(times.shift());
    }
  }
}

```

I tested that with the `ON_CHANGE` event, which triggers when a new row is added.

EDIT: Note that when called, this will recalculate the intervals for _every_ row, which is a bit inefficient. It would probably be better to have two scripts: one that tiggers for `ON_EDIT`, that just recalculates the row that was edited, and a second for `ON_CHANGE`, which would be a variation of the above that only calculates for the row that was added.

---

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

</div>

> [@Darren\_Murphy](#):
>
> EDIT: Note that when called, this will recalculate the intervals for _every_ row, which is a bit inefficient. It would probably be better to have two scripts: one that tiggers for `ON_EDIT` , that just recalculates the row that was edited, and a second for `ON_CHANGE` , which would be a variation of the above that only calculates for the row that was added.

@kyleheney - here’s the two scripts I referred to above.

```
function on_sheet_change(event) {
  var sheetname = event.source.getActiveSheet().getName();
  if (sheetname == 'Test') { // Change this
    var sheet = event.source.getActiveSheet();
    var row = sheet.getLastRow();
    var data = sheet.getRange(row,1,1,2).getValues();
    var times = get_15_minute_increments(data[0][0], data[0][1]);
    sheet.getRange(row,3).setValue(times);
  }
}

function on_sheet_edit(event) {
  var range = event.range;
  var sheetname = range.getSheet().getName();
  if (sheetname == 'Test') {. // Change this
    var row = range.getRow();
    var sheet = range.getSheet();
    var data = sheet.getRange(row,1,1,2).getValues();
    var times = get_15_minute_increments(data[0][0], data[0][1]);
    sheet.getRange(row,3).setValue(times);
 }
} 

```

Note that both assume that start and end time are the first two columns in the sheet, and the intervals should be written into the 3rd column.

---

<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, 6:19pm UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/24 "2020-11-06T18:19:37Z")

</div>

Thanks a lot! I’m going to find some time to test these out and will report back my findings. Really appreciate the help on this… I didn’t think it would be possible, but now it seems there are multiple options!

---

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

</div>

No problem. We probably went a bit deep down the rabbit hole there. But I don’t mind, it’s kinda fun 😆

---

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

</div>

Haha I know what you mean. I actually consider myself above average when it comes to Excel/Sheets, but this one had me scratching my head. You and @ThinhDinh did things that I barely understand, but there’s always room to learn!

---

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

</div>

Weird issue after doing some testing… Not sure what’s up.

 ![ts test](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/7/d/7df03693f9ca72deeaa658a7034ec75b9a3dd8db.jpeg)

Only seems to happen when first time is not ending in .00 (so start with 8.00 and it’s fine, but start at 8.25, it adds increments incorrectly.

---

<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, 7:13pm UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/28 "2020-11-06T19:13:59Z")

</div>

> [@kyleheney](#):
>
> Only seems to happen when first time is not ending in .00 (so start with 8.00 and it’s fine, but start at 8.25, it adds increments incorrectly.

It’s actually correct. Remember that your start and end times are in hh.mm, but these are being converted to decimal hours for the intervals. So when you enter a start time of 15.25, the 25 gets converted to .42 hours (25/60). And then 15 minutes (0.25 hours) is added to each subsequent value: 15.42 + .25 = 15.67, etc…

---

<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, 7:23pm UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/29 "2020-11-06T19:23:26Z")

</div>

Oooo okay. I’ll have to change something app-side to make it function properly. My only intervals in the app are 8.00, 8.25, 8.45, 9.00 ---- these are the choice values, which get displayed as 8:00 AM, 8:15 AM, 8:30 AM, 8:45AM, 9:00 AM.

I guess I just need to change my values to 8.00, 8.15, 8.30, 8.45, 9.00 for your solution to work. I’ll have to see how that affects my other calcs. Might just need an ifthen to convert it back if needed (the formula results are correct though, so maybe it’ll be okay)

![ts test2](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/1/4/1488b26b68ee38bddb83addf18469b0e56a5d677.jpeg)

---

<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, 7:28pm UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/30 "2020-11-06T19:28:56Z")

</div>

> [@kyleheney](#):
>
> My only intervals in the app are 8.00, 8.25, 8.45, 9.00

Is there a typo there? (because the intervals are 25min, 20min, 15min)  
Anyway, I can modify the script to accept decimal hours for start/end times if that makes it easier?  
Let me know and I’ll do it tomorrow (way past my bedtime now 🤪)

---

<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, 7:33pm UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/31 "2020-11-06T19:33:21Z")

</div>

Haha all good. No, my time values aren’t actual times… they just correspond to an actual time (hour divided into quarters, 0.25 per quarter… so 8:45AM is 8.75. This is needed because I subtract the two values to get the duration of the shift. This is done this way because the app is a time sheet app where the number of hours worked is multiplied by the hourly rate… so I need the number of hours, not the time (so 8.25 x $10 an hour is $82.50… but 8.15 x $10 an hour is only $81.50).

---

<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 7, 2020, 7:33am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/32 "2020-11-07T07:33:05Z")

</div>

> [@kyleheney](#):
>
> so I need the number of hours, not the time (so 8.25 x $10 an hour is $82.50… but 8.15 x $10 an hour is only $81.50)

hmm… have you thought about using the date/time math functions that are available in Glide?

I’m imagining something like this:

 ![Screen Shot 2020-11-07 at 3.23.25 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/4/0/401e47c037d2e20f2c9390c45ead0c4eff44daef.png)

In the above:

- Start and End are Date/Time columns displaying just the time in hh:mm format
- Duration is a math column, subtracting the Start from the End and then converting to decimal hours. The formula looks like: `(E - S) * 1440 / 60`, where E is the End Time and S is the Start Time
- And then the Charge is another math column simply multiplying the Duration by the Hourly Rate

The advantage of something like this is that you can work in native time formats (hh:mm), but then because the result of the Duration calculation comes out in decimal hours, there is no additional jumping through hoops to calculate the charge. It. Just. Works.

---

<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 7, 2020, 10:58am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/33 "2020-11-07T10:58:39Z")

</div>

Hmm I’ve thought about doing it that way and I may have to give it a go. I’m just trying to replicate an old Excel-based time sheet I created years ago and have it in an app, so I’m trying to keep things the same as they were in the Excel. I also need the value (the 8.25 hours) to be in the actual Sheet (not just Glide) because it will need to be exported into our payroll software.

Hmm thinking further… If I gather the start and end time before the form, I could do that duration calculation like you mentioned and pass it to the sheet as a column value. So this may work and give me what I need… Hmmm!

Okay I’ve got to work on this a bit more, but you’ve given me something to think about! If I do things the way ou suggested, I’d just have to change the script back to use “:” as the separator right? Oh wait, no then the increment would be just 15 instead of 0.25.

---

<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 7, 2020, 11:16am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/34 "2020-11-07T11:16:59Z")

</div>

> [@kyleheney](#):
>
> If I do things the way ou suggested, I’d just have to change the script back to use “:” as the separator right? Oh wait, no then the increment would be just 15 instead of 0.25.

If you’re doing that, then use this version:

```
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 version accepts start/end time in hh:mm format, and outputs decimal hours.  
Just out of curiosity, what are you using the generated list for? Is it to populate a choice component for the user?

---

<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 7, 2020, 11:26am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/35 "2020-11-07T11:26:49Z")

</div>

Sweet, thanks!

Yes, the user’s previously submitted times are removed from the list of choices in order to prevent the user from submitting the same hours on the same day. The choice component is tied to a relation that looks for the user’s entry on a date that is chosen before the start/end times.

---

<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 7, 2020, 11:49am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/36 "2020-11-07T11:49:19Z")

</div>

@kyleheney - I was just thinking a bit more about this…

If you wanted to keep this within Glide, could the following approach possibly work?

- Create a Glide table with just a single column containing all the decimalised time values that you will need (9.00, 9.25, 9.50, 9.75, 10.00, etc, etc)
- Then use this as the source to your choice component, and apply filtering (using your start and end times) to limit the available choices

Would that work?

---

<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 7, 2020, 11:51am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/37 "2020-11-07T11:51:48Z")

</div>

My current setup is that the times reside in a separate sheet in two columns — 8:00AM format (time) in one column, then 8.00 format (number) in the other column. My choice uses the time as the display and the number as the value.

I like users to see the AM/PM times as the choices — you’d be surprised how many people are clueless when it comes to the 24 hour clock.

---

<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 17, 2020, 6:00pm UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/38 "2020-11-17T18:00:23Z")

</div>

Quick update on this — I appreciate all the help from @ThinhDinh and @Darren_Murphy, but I think I’m going to have to build this out in Glide (approx 100 computed columns). Even though the methods above work, they’re not 100% reliable as there still needs to be a delay for Glide to wait for the formula to sync and/or the script to run and write back to the app ---- I thought that I could possibly get around that delay by using the formula or script as a column value that gets passed to a form, but that column value needs to be sync’d back to the app _before_ the Open Form button is pressed.

Thinking further (before I create 100 computed columns)… maybe I could use the new Actions to do this. The kicker would be the ability to clear a column after a button is pressed. This would allow me to hide the “confirm” button until the script/formula column is not empty (this column would need to be cleared after the button is pressed so that the next time someone tries to submit it, the column is empty and the button is hidden again).

Going to try this approach — without being able to copy columns in the editor, the thought of creating 100 computed columns makes me want to cry haha

EDIT: this works, but makes for a clunky UX. Someone can enter their values quickly, but then have to wait (up to a minute) for the button to appear so they can submit. It also becomes unreliable if someone changes values as they fill out the form — the script could be unempty, but need to recompute to get the latest value before the button should be pressed.

Going to keep trying to make Actions make this process better.

---

<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 18, 2020, 1:12am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/39 "2020-11-18T01:12:21Z")

</div>

> [@kyleheney](#):
>
> The kicker would be the ability to clear a column after a button is pressed.

Did you see [this](https://community.glideapps.com/t/how-to-clear-an-entire-column-compound-action-trickery/18555/10)? Maybe that will help?

---

<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 18, 2020, 1:15am UTC](https://community.glideapps.com/t/help-with-a-formula-likely-going-to-be-in-sheets/18015/40 "2020-11-18T01:15:15Z")

</div>

I did, but then I posted something in the Actions thread about the clear action happening before the values are passed to the form screen. I see it as a bug, but it might just be the way it currently works.

> [@new Actions! zapzapzap](https://community.glideapps.com/t/actions/18026/257):
>
> Posting this here as it’s actions-related. I don’t think this is necessarily a bug… maybe more of a feature request. The issue is that I want column values to get pushed to a form, then clear the original choice component after the form button is pressed. Currently, the show form action and clear column action happen at the same time, which clears the column values (resulting in blank columns once the form is submitted). If the Add Row action could include Column Values, that would fix this too …

Edit: I think I see what you’re saying now… compare the last submitted a form value to the choice component and if they match, clear the column. This would have to happen the form submit though, which can’t have actions tied to it yet. The “after form submit” action is a much needed addition to really round out the new Actions!

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