# Generate range of time between two times

#### artvguy

##### New Member
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.

### 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
Hi,

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

Format result cells as "Time" of your choice.

#### artvguy

##### New Member
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

</tbody>
Sheet600

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

</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!

#### jtakw

##### Well-known Member
You're welcome, welcome to the forum.

Replies
4
Views
76
Replies
0
Views
28
Replies
2
Views
16
Replies
3
Views
170
Replies
4
Views
129