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