Time Range: Increase by 15 minutes through 24 hour period

[ShirleyKnot]

Hi

I'm sure this is fairly easy problem for someone out there. What I want to know is on my spreadsheet in column B I've got a time range that I want to increase by 15 min increments, for example, starting at 00:00 - 00:15, I then want it to automatically calculate the quarter hours for each hour, down to 22:00 - 23:59. Is this possible to do? I've been puzzling over this most of the afternoon. I'm using Excel 97.

Thanks in anticipation.

 

[grizle]

Can you provide a bit more explanation please? If the first range is 00:00 - 00:15 is the second range 00:15 - 00:30 (same duration but 15 minutes later) or is it 00:15 - 00:45 (range duration increased by 15 mins) or is it something else.

I can see its complicated since you're starting with a text string, but you can extract the individual times by using a LEFT() and RIGHT() along with TIMEVALUE().

 

[ShirleyKnot]

Grizle

Thanks for the response. Sorry, should have been more specific. The times through the 24 hour period is as follows:

00:00 - 00:15
00:15 - 00:30
00:30 - 00:45
00:45 - 00:59
01:00 - 00:15
01:15 - 01:30
01:30 - 01:45
01:45 - 01:59
going through to the final time of 23:45 - 23:59

 

[grizle]

It increases the complexity having non-uniform time periods (each hour ends at 59 mins rather than on the hour). Would it be acceptable take it to the hour or to use a standard 14mins 45 sec every time?

 

[ShirleyKnot]

Grizle

I'm happy for it to be taken to the hour, so to be definite the times would be:

00:00 - 00:15
00:15 - 00:30
00:30 - 00:45
00:45 - 01:00

Thanks.

 

[T. Valko]

Grizle

Thanks for the response. Sorry, should have been more specific. The times through the 24 hour period is as follows:

00:00 - 00:15
00:15 - 00:30
00:30 - 00:45
00:45 - 00:59
01:00 - 00:15
01:15 - 01:30
01:30 - 01:45
01:45 - 01:59
going through to the final time of 23:45 - 23:59

Try this...

Book1
	A	B
2	0:00	0:15
3	0:15	0:30
4	0:30	0:45
5	0:45	0:59
6	1:00	1:15
7	1:15	1:30
8	1:30	1:45
9	1:45	1:59
10	2:00	2:15
11	2:15	2:30
12	2:30	2:45
13	2:45	2:59
14	3:00	3:15
15	3:15	3:30
16	3:30	3:45
17	3:45	3:59
18	4:00	4:15
19	4:15	4:30
20	4:30	4:45
21	4:45	4:59
Sheet1

Enter 0 in A2.

Enter this formula in A3:

=A2+TIME(0,15,0)

Enter this formula in B2 and copy down to B3:

=IF(MINUTE(A2)=45,A2+TIME(0,14,0),A2+TIME(0,15,0))

Select A3 and B3 and copy down to A97:B97

Format as h:mm

 

[markmzz]

ShirleyKnot,

If I understand correctly what you want, then maybe this can help you (two formulas - B2 and C2 - choose one):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;background-color: #C5D9F1;;">00:00 - 00:15</td><td style="text-align: center;background-color: #C5D9F1;;">00:00 - 00:15</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">00:15 - 00:30</td><td style="text-align: center;;">00:15 - 00:30</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">00:30 - 00:45</td><td style="text-align: center;;">00:30 - 00:45</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">00:45 - 01:00</td><td style="text-align: center;;">00:45 - 00:59</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">01:00 - 01:15</td><td style="text-align: center;;">01:00 - 01:15</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">01:15 - 01:30</td><td style="text-align: center;;">01:15 - 01:30</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">01:30 - 01:45</td><td style="text-align: center;;">01:30 - 01:45</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">01:45 - 02:00</td><td style="text-align: center;;">01:45 - 01:59</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">02:00 - 02:15</td><td style="text-align: center;;">02:00 - 02:15</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">02:15 - 02:30</td><td style="text-align: center;;">02:15 - 02:30</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">02:30 - 02:45</td><td style="text-align: center;;">02:30 - 02:45</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">02:45 - 03:00</td><td style="text-align: center;;">02:45 - 02:59</td></tr><tr ><td style="color: #161120;text-align: center;">90</td><td style="text-align: center;;">22:15 - 22:30</td><td style="text-align: center;;">22:15 - 22:30</td></tr><tr ><td style="color: #161120;text-align: center;">91</td><td style="text-align: center;;">22:30 - 22:45</td><td style="text-align: center;;">22:30 - 22:45</td></tr><tr ><td style="color: #161120;text-align: center;">92</td><td style="text-align: center;;">22:45 - 23:00</td><td style="text-align: center;;">22:45 - 22:59</td></tr><tr ><td style="color: #161120;text-align: center;">93</td><td style="text-align: center;;">23:00 - 23:15</td><td style="text-align: center;;">23:00 - 23:15</td></tr><tr ><td style="color: #161120;text-align: center;">94</td><td style="text-align: center;;">23:15 - 23:30</td><td style="text-align: center;;">23:15 - 23:30</td></tr><tr ><td style="color: #161120;text-align: center;">95</td><td style="text-align: center;;">23:30 - 23:45</td><td style="text-align: center;;">23:30 - 23:45</td></tr><tr ><td style="color: #161120;text-align: center;">96</td><td style="text-align: center;;">23:45 - 00:00</td><td style="text-align: center;;">23:45 - 23:59</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=RIGHT(<font color="Blue">B1,5</font>)&" - "&TEXT(<font color="Blue">(<font color="Red">RIGHT(<font color="Green">B1,5</font>)*1+"0:15"*1</font>),"hh:mm"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=TEXT(<font color="Blue">LEFT(<font color="Red">C1,5</font>)*1+"0:15"*1,"hh:mm"</font>)&" - "&TEXT(<font color="Blue">LEFT(<font color="Red">C1,5</font>)*1+IF(<font color="Red">RIGHT(<font color="Green">C1,2</font>)="45","0:29"*1,"0:30"*1</font>),"hh:mm"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B90</th><td style="text-align:left">=RIGHT(<font color="Blue">B89,5</font>)&" - "&TEXT(<font color="Blue">(<font color="Red">RIGHT(<font color="Green">B89,5</font>)*1+"0:15"*1</font>),"hh:mm"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C90</th><td style="text-align:left">=TEXT(<font color="Blue">LEFT(<font color="Red">C89,5</font>)*1+"0:15"*1,"hh:mm"</font>)&" - "&TEXT(<font color="Blue">LEFT(<font color="Red">C89,5</font>)*1+IF(<font color="Red">RIGHT(<font color="Green">C89,2</font>)="45","0:29"*1,"0:30"*1</font>),"hh:mm"</font>)</td></tr></tbody></table></td></tr></table><br />

Markmzz

 

[ShirleyKnot]

Markmzz

This is absolutely brilliant! This is exactly what I wanted. Both formulas will be useful and will save me a lot of time when I return to work on Monday.

Thank you tons!

 

[markmzz]

Markmzz

This is absolutely brilliant! This is exactly what I wanted. Both formulas will be useful and will save me a lot of time when I return to work on Monday.

Thank you tons!

ShirleyKnot,

I'm happy that I could to help you.

You are welcome and thanks for the feedback.

Markmzz