Time Range: Increase by 15 minutes through 24 hour period

ShirleyKnot

New Member
Joined
Jul 28, 2011
Messages
4
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.
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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().
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
AB
20:000:15
30:150:30
40:300:45
50:450:59
61:001:15
71:151:30
81:301:45
91:451:59
102:002:15
112:152:30
122:302:45
132:452:59
143:003:15
153:153:30
163:303:45
173:453:59
184:004:15
194:154:30
204:304:45
214:454: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
 
Upvote 0
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
 
Upvote 0
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!:biggrin:
 
Upvote 0
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!:biggrin:

ShirleyKnot,

I'm happy that I could to help you.

You are welcome and thanks for the feedback.

Markmzz
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top