Time Range

Raiyan19

New Member
Joined
Mar 12, 2021
Messages
11
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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.
1615607424387.png
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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")
 
Upvote 0
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top