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

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

Sheet officer reporting in for help…

Do you want it to be 8:15 AM, 8.30 AM etc. instead of 8.25, 8.50?

1 Like

Hey @ThinhDinh! I knew you’d want to try this one haha

I’d actually prefer if it was just 8.00, 8.25, etc. It’s easier for other calculations in the app

1 Like

Let me try it and get back to you later.

Thanks for any help (or alternative suggestions) you can provide.

1 Like

If you don’t want the AM/PM indicators, is it safe then to assume you’ll be using a 24hr display format?
ie. 3:45 PM will appear as 15:45?

Yes that is correct.

Here’s a working version. What you need is in column E, I suppose.

3 Likes

Wow! That looks like it should work! Thanks a ton!!

I’ll keep looking for harder challenges haha

2 Likes

Must be noted the formulas are in cell C1, D1, E1 and F2. I unhide the supporting calculations in the columns to the right of E.

2 Likes

wow! it blows my mind!

2 Likes

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

Caveats:

• The Start Time and End Time columns must be formatted as plain text
• It won’t work if the end time crosses over into the next day
2 Likes

Thanks for this option! I’m going to try both ways and see which one leads to the least delay in the app (since this is a sheets-based process).

1 Like

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.

Do you mean like this?

1 Like

Bahh how’d you do that?!

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.

Give this a go:

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

I expect so - I don’t use array formulas much, so I didn’t consider that.
I’ll have a fiddle and let you know.