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

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.

[image] 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.

[image] 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.

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!

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

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!

Weird issue after doing some testing… Not sure what’s up. [ts test] 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.

[image] 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…

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]

[image] 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 :crazy_face:)

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

Ask for Help

Darren_Murphy November 18, 2020, 1:12am 39

Did you see this? Maybe that will help?

Topic		Replies	Views
New computed column: Math General	95	6894	November 12, 2021
🆕 Date-time Math General data-editor	123	14798	September 27, 2023
Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts Ask for Help	125	15468	September 9, 2022
📅 Guide on Dates & Times Community Resources	22	1874	April 2, 2025
🆕 Format Date Plugin General	58	2877	August 22, 2022

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

Related topics