Time Range

Raiyan19

New Member
Joined
Mar 12, 2021
Messages
2
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

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
Joined
Aug 12, 2009
Messages
1,691
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")
 

Raiyan19

New Member
Joined
Mar 12, 2021
Messages
2
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

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
154
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
Joined
Aug 18, 2011
Messages
6,502
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top