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.
1 Like
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.
@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.
2 Likes
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!
2 Likes
No problem. We probably went a bit deep down the rabbit hole there. But I don’t mind, it’s kinda fun 
1 Like
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!
2 Likes
Weird issue after doing some testing… Not sure what’s up.
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)
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 
)
1 Like
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).
1 Like
hmm… have you thought about using the date/time math functions that are available in Glide?
I’m imagining something like this:
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.
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.
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?
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.
1 Like
@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?
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.
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.
2 Likes
Did you see this? Maybe that will help?
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.
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!
1 Like