# Time Range: Increase by 15 minutes through 24 hour period

This is a discussion on Time Range: Increase by 15 minutes through 24 hour period within the Excel Questions forums, part of the Question Forums category; Hi I'm sure this is fairly easy problem for someone out there. What I want to know is on my ...

1. ## Time Range: Increase by 15 minutes through 24 hour period

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.

2. ## Re: Time Range: Increase by 15 minutes through 24 hour period

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

3. ## Re: Time Range: Increase by 15 minutes through 24 hour period

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

4. ## Re: Time Range: Increase by 15 minutes through 24 hour period

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?

5. ## Re: Time Range: Increase by 15 minutes through 24 hour period

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.

6. ## Re: Time Range: Increase by 15 minutes through 24 hour period

Originally Posted by 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
Try this...

Sheet1

 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

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

7. ## Re: Time Range: Increase by 15 minutes through 24 hour period

ShirleyKnot,

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

Excel 2007
BC
100:00 - 00:1500:00 - 00:15
200:15 - 00:3000:15 - 00:30
300:30 - 00:4500:30 - 00:45
400:45 - 01:0000:45 - 00:59
501:00 - 01:1501:00 - 01:15
601:15 - 01:3001:15 - 01:30
701:30 - 01:4501:30 - 01:45
801:45 - 02:0001:45 - 01:59
902:00 - 02:1502:00 - 02:15
1002:15 - 02:3002:15 - 02:30
1102:30 - 02:4502:30 - 02:45
1202:45 - 03:0002:45 - 02:59
9022:15 - 22:3022:15 - 22:30
9122:30 - 22:4522:30 - 22:45
9222:45 - 23:0022:45 - 22:59
9323:00 - 23:1523:00 - 23:15
9423:15 - 23:3023:15 - 23:30
9523:30 - 23:4523:30 - 23:45
9623:45 - 00:0023:45 - 23:59

Sheet1

Worksheet Formulas
CellFormula
B2=RIGHT(B1,5)&" - "&TEXT((RIGHT(B1,5)*1+"0:15"*1),"hh:mm")
C2=TEXT(LEFT(C1,5)*1+"0:15"*1,"hh:mm")&" - "&TEXT(LEFT(C1,5)*1+IF(RIGHT(C1,2)="45","0:29"*1,"0:30"*1),"hh:mm")
B90=RIGHT(B89,5)&" - "&TEXT((RIGHT(B89,5)*1+"0:15"*1),"hh:mm")
C90=TEXT(LEFT(C89,5)*1+"0:15"*1,"hh:mm")&" - "&TEXT(LEFT(C89,5)*1+IF(RIGHT(C89,2)="45","0:29"*1,"0:30"*1),"hh:mm")

Markmzz

8. ## Re: Time Range: Increase by 15 minutes through 24 hour period

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!

9. ## Re: Time Range: Increase by 15 minutes through 24 hour period

Originally Posted by 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!
ShirleyKnot,

You are welcome and thanks for the feedback.

Markmzz

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•