CODEWITHSUNDEEP
excelcell-formatting
mrjaws
mrjaws

Reputation: 103

How do I format cells to display a time range instead of just the time?

I'm trying to create a schedule for myself, but I get confused by seeing the following:

8:00 AM
8:30 AM
9:00 AM
9:30 AM
10:00 AM
10:30 AM
11:00 AM
11:30 AM
12:00 PM

I can never remember whether I am looking at the right block, so I instead want to see this:

8:00 AM to 8:30 AM
8:30 AM to 9:00 AM
9:00 AM to 9:30 AM
9:30 AM to 10:00 AM

Is there an easy way to do this that doesn't involve me typing it into every cell? I want to be able to flash fill the rest of my table, if I can.

(Note: the below is a workaround where I just made a separate column, but it'd be nice if I could do it in one cell.)

Start        End
8:00 AM      8:30 AM
8:30 AM      9:00 AM
9:00 AM      9:30 AM
9:30 AM     10:00 AM
10:00 AM    10:30 AM
10:30 AM    11:00 AM
11:00 AM    11:30 AM
11:30 AM    12:00 PM
12:00 PM    12:30 PM

Upvotes: 0

Views: 11830

Answers (2)

addohm
addohm

Reputation: 2475

Put 8:00 in A1 and drag down until your schedule ends.

In Another column, C1 in my example, insert the formula =A1+0.5/24 0.5 being a half hour in a 24 hour period. Again, drag down until your schedule ends.

Finally, enter =TEXT(A1,"H:MM AM/PM")&" to "&TEXT(C1,"H:MM AM/PM") in another column, and drag down until your schedule ends.

*If you don't want to see the columns with the start and end times, hide them or put them on another sheet.

enter image description here

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

Use this formula:

=TEXT((ROW(1:1)-1)*TIME(0,30,0)+TIME(8,0,0),"hh:mm AM/PM") & " to " & TEXT((ROW(1:1))*TIME(0,30,0)+TIME(8,0,0),"hh:mm AM/PM")

Change each of the TIME(8,0,0) to the first hour you want to show.

If you want a different time block than 30 minutes, change each TIME(0,30,0) to the span desired.

enter image description here

Upvotes: 4

Related Questions