Results 1 to 9 of 9

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. #1
    New Member
    Join Date
    Jul 2011
    Posts
    4

    Default 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.
    Last edited by ShirleyKnot; Jul 28th, 2011 at 04:09 PM.

  2. #2
    Board Regular
    Join Date
    Jul 2011
    Posts
    51

    Default 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. #3
    New Member
    Join Date
    Jul 2011
    Posts
    4

    Default 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. #4
    Board Regular
    Join Date
    Jul 2011
    Posts
    51

    Default 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. #5
    New Member
    Join Date
    Jul 2011
    Posts
    4

    Default 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. #6
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Time Range: Increase by 15 minutes through 24 hour period

    Quote Originally Posted by ShirleyKnot View Post
    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

     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



    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
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  7. #7
    MrExcel MVP
    Join Date
    May 2011
    Posts
    3,191

    Default 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. #8
    New Member
    Join Date
    Jul 2011
    Posts
    4

    Default 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. #9
    MrExcel MVP
    Join Date
    May 2011
    Posts
    3,191

    Default Re: Time Range: Increase by 15 minutes through 24 hour period

    Quote Originally Posted by ShirleyKnot View Post
    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com