Auto populate time period

[Goddess]

Hi,

I need to create a column with time period. For example:

9.15 - 9.30am
9.30 - 9.45am
9.45 - 10.00am
10.00 - 10.15am
10.15 - 10.30am
10.30 - 10.45am

Is there anyway that I can auto populate the cells, instead of typing it row by row?

Thanks,
Irene

 

[Joe4]

Yes there is.

It would be helpful if you could provide us with the following information:
1. What are the starting and ending time ranges you want to list (I assume that you want more than just the 6 you have posted)?
2. What is the range address of the cell the first time range is going in?

If you provide us with that, we should be able to tailor an answer to your exact worksheet set-up, so you can hopefully just "plug-and-play", and not need to have to adapt the answer to get it to work for you.

 

[kvsrinivasamurthy]

It is also better instead of one column 2 columns are used.
A1=9:15 B1=9:30
A2=9:30 b2=9:45
Select A1:B2. Use fill handle and drag down.

 

[Joe4]

Here is an example how you can do it in like the way you asked:

If you enter your starting time in cell A1, then you can enter this formula in cell A2 and copy down as far as you like:

=LET(x,TEXT($A$1+(ROW()-2)*(15/1440),"h.mmam/pm"),LEFT(x,LEN(x)-2)) & " - " & TEXT($A$1+(ROW()-1)*(15/1440),"h.mmam/pm")

Here are the results:

As i mentioned in my first reply, if your sheet is structured a bit differently, updates will probably need to be made to the formula to reflect those differences.

 

[Goddess]

Wow! Thanks for all the reply! Really helpful!!

 

[Joe4]

You are welcome.
Glad we were able to help!