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 …

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: f…

[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 wo…

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…

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…

[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 pas…

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	6491	November 12, 2021
🆕 Date-time Math General data-editor	123	14235	September 27, 2023
Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts Ask for Help	125	14851	September 9, 2022
📅 Guide on Dates & Times Community Resources	22	1705	April 2, 2025
🆕 Format Date Plugin General	58	2568	August 22, 2022

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

Related topics