Function Problems

Convert an Excel Time into an Hourly Range

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to convert a specific time value into a formatted text string that displays the corresponding one-hour range, ensuring leading zeros are included for single-digit hours.

Product: Spreadsheet
Device & OS: not provided
Scenario: Grouping detailed timestamp data into consistent hourly brackets for easier reading, pivot table grouping, or shift tracking.
Observed behavior: The user needs a formula to automatically extract the hour from a cell (e.g., 12:14) and output a standardized text range like '12:00 to 12:59'.

Before you start

Verify that your source cells contain valid time formats or correctly structured time strings (like '12:14') so the spreadsheet's time functions can read them properly.

Solution 1Recommended

Use the TEXT and HOUR Functions for Standard Hourly Ranges

This is the most straightforward method to extract the hour and format it as a text string with leading zeros included.

By combining the TEXT function to format the number and the HOUR function to extract the hour of the day, you can quickly build an hourly range string.

Select a target cell

Click on an empty cell where you want the hourly range to appear (e.g., cell B1).

Enter the formula

Assuming your original time is in cell A1, type the following formula: =TEXT(HOUR(A1),"00")&":00 to "&TEXT(HOUR(A1),"00")&":59"

Press Enter and apply to the column

Press Enter to see the result. You can then drag the fill handle down to copy this formula for the rest of your time entries.

Leading Zeros Included: Using the "00" format in the TEXT function ensures that a time like 1:23 displays accurately as '01:00 to 01:59' instead of '1:00 to 1:59'.

Solution 2

Use the TIME Function to Generate the Range

Use this method if you prefer to build the range using calculated time values rather than extracting the hour alone.

Solution 3

Use TEXTJOIN for Microsoft 365 or Modern Spreadsheet Versions

For users with software supporting dynamic arrays and the TEXTJOIN function, this presents a cleaner, shorter formula.

Organize Spreadsheet Time Data

Efficiently Format Time Data with WPS Spreadsheet

WPS Spreadsheet makes it incredibly simple to group and format raw time entries into clean hourly brackets. With full support for standard data manipulation formulas, you can automate your time-tracking sheets in seconds.

1. Open your data file: Launch WPS Spreadsheet and open the document containing your timestamps.
2. Enter the time conversion formula: Select a blank column and type =TEXT(HOUR(A1),"00")&":00 to "&TEXT(HOUR(A1),"00")&":59".
3. Drag the fill handle: Double-click the bottom-right corner of the cell to instantly apply the hourly range format to your entire dataset.

100% compatible with Microsoft Excel's time and date formulas, including HOUR, TIME, and TEXT.Provides powerful PivotTable features to further summarize your newly created hourly ranges.Free, lightweight, and features a clean, tabbed interface for seamless multitasking.

microsoft office alternative - wps office

Frequently Asked Questions

How can I display the range without leading zeros for single-digit hours?

If you want a time like 1:23 to display as '1:00 to 1:59', you can use the HOUR function without the TEXT formatting. Simply use the formula: =HOUR(A1) & ":00 to " & HOUR(A1) & ":59".

Can I group time into 30-minute intervals instead of hourly ranges?

Yes. Instead of just extracting the hour, you can use the FLOOR or MROUND functions to round the time down to the nearest 30-minute mark before applying your text formatting.

Why is my formula returning a #VALUE! error?

This error usually occurs if the source cell is formatted as plain text containing hidden characters or spaces instead of a recognized time format. Check the cell formatting and ensure there are no trailing spaces.

Excel & Data Problems