Generate range of time between two times

artvguy

New Member
Joined
Feb 22, 2019
Messages
2
Hey all... long time reader of the forum but first time poster. I've been banging my head against the wall for days trying to solve a problem I'm having and I was hoping one of you guys might have an answer.

I have a column with random times of day in ascending order (9:00am, 9:01am, 9:04am, 9:09am, 9:13am, 9:17am, 9:18am, 9:20am, 9:25am). With this list, I'm trying to figure out how to get the range of times in minutes between two adjacent times. For example, if I was looking at 9:00am and 9:01am, I'd type a formula in the cell to the right of 9:01am that would return 9:00am in it's own cell and 9:01am in its own cell. If I was working with 9:04am and 9:09am, I'd type a formula in the cell to the right of 9:09am that would return 9:04am in its own cell, 9:05am in its own cell, 9:06 in its own cell, 9:07 in its own cell, 9:08 in its own cell and 9:09 in its own cell.

Basically, I need to pluck out each minute (in hours and minutes in AM or PM) between two adjacent times of day in a column. These times change multiple times each day and I'm just trying to automate a portion of what I've been doing manually. I'm wondering if there's some sort of magical Excel formula that will work no matter what the range is between the two times.

Thanks in advance!
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Try this, formula copied Across, if you need the formula copied Down instead, we'll need to adjust the formula:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">9:01 AM</td><td style="text-align: right;;">9:02 AM</td><td style="text-align: right;;">9:03 AM</td><td style="text-align: right;;">9:04 AM</td><td style="text-align: right;;">9:05 AM</td><td style="text-align: right;;">9:06 AM</td><td style="text-align: right;;">9:07 AM</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">9:07 AM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet600</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><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: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">A1+(<font color="Green">1/1440</font>)>$A2,"",A1+(<font color="Green">1/1440</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

Format result cells as "Time" of your choice.
 

artvguy

New Member
Joined
Feb 22, 2019
Messages
2
Hi,

Try this, formula copied Across, if you need the formula copied Down instead, we'll need to adjust the formula:

ABCDEFGHI
19:01 AM9:02 AM9:03 AM9:04 AM9:05 AM9:06 AM9:07 AM
29:07 AM

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet600

Worksheet Formulas
CellFormula
B1=IFERROR(IF(A1+(1/1440)>$A2,"",A1+(1/1440)),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Format result cells as "Time" of your choice.

Thank you so very much for the reply and I apologize for the delayed response. This is exactly what I needed. Thanks so much for the help!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,312
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top