Time Range

Raiyan19

New Member
Hello. I have a problem. I have a time, lets say, 9.30 am. I want to insert the time range in the next cell like this "09:00-10:00". Moreover for for other times like this (4:15) i want to write "16:00-17:00". How is it possible? Please if answered it would be really helpful. Thanks.

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Misca

Well-known Member
Welcome to the board!

You can get the full hours before / after a certain time stamp by using the FLOOR / CEILING functions with the significance of 1/24. Excel handles dates and times as numbers where the whole numbers stand for the date part and decimals as hours. Since there's 24 hours in a day 1/24 equals 1 hour.

FLOOR & CEILING functions round the given number down & up to the nearest specified multiple. The results from these formulas are just numbers.

If you just wanted to use the earlier full hour or later full hour you could just enter the FLOOR or CEILING to a column and use number formatting to display the results as times but since you want to have two results in a single cell you're going to have to wrap each formula in a TEXT function that formats the number the way you want to.

When you combine all of the above the formula becomes something like:
Excel Formula:
``=TEXT(FLOOR(B2,1/24),"h:mm-")&TEXT(CEILING(B2+0,0001,1/24),"h:mm")``

Sulprobil

Board Regular
MrExcel_Time_Range.xlsx
ABCDE
1Time RangesInputTempSuggested OutputRequested Output
20:0009:2510:000:00 - 9:30
39:3010:2529:309:30 - 13:00
413:0011:5929:309:30 - 13:00
514:0012:4529:309:30 - 13:00
615:0013:25313:0013:00 - 14:00
716:0014:25414:0014:00 - 15:00
817:0015:01515:0015:00 - 16:00
918:0016:33616:0016:00 - 17:00
1019:0017:05717:0017:00 - 18:00
1124:0018:20818:0018:00 - 19:00
1219:35919:0019:00 - 24:00
1313:00313:0013:00 - 14:00
1406:3410:000:00 - 9:30
Sheet1
Cell Formulas
RangeFormula
C2:C14C2=MATCH(B2,Time_Ranges,1)
D2:D14D2=INDEX(Time_Ranges,C2)
E2:E14E2=TEXT(INDEX(Time_Ranges,C2),"[h]:mm") & " - " & TEXT(INDEX(Time_Ranges,1+C2),"[h]:mm")
Named Ranges
NameRefers ToCells
Time_Ranges=Sheet1!\$A\$2:\$A\$11C2:E14

FormR

MrExcel MVP
Hi, here's another option you can try that uses a lookup table (D2:E8 in this example) - note that times in column D of the lookup table need to be sorted in ascending order.

Book2
ABCDE
1TimeFormulaLower BoundReturn Text
209:3009:30-13:0009:3009:30-13:00
310:2509:30-13:0013:0013:00-14:00
411:5909:30-13:0014:0014:00-15:00
512:4509:30-13:0015:0015:00-16:00
613:2513:00-14:0016:0016:00-17:00
714:2514:00-15:0017:0017:00-19:00
815:0115:00-16:0019:0019:00-23:59
916:3316:00-17:00
1017:0517:00-19:00
1118:2017:00-19:00
1219:3519:00-23:59
Sheet1
Cell Formulas
RangeFormula
B2:B12B2=LOOKUP(A2,\$D\$2:\$E\$8)

Replies
4
Views
365
Replies
8
Views
83
Replies
2
Views
88
Replies
5
Views
101
Replies
4
Views
151

1,130,323
Messages
5,641,530
Members
417,215
Latest member
Diaryman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back