Convert Numbers to Proper Time Format in Power Query

Welcome to Excel Jet Consult blog post. In this Excel video tutorial, we will learn how to convert numbers to proper time format using Power Query. Let’s get started


Sample Data

In the screenshot below, we have transaction data which is officially formatted as an Excel Table using CTRL + T and renamed as Data. Our goal is to convert the Request Time in column B from numbers to proper time format

Load Dataset To Power Query Editor

We need to get the formatted data into the Power Query editor. To do that:

  • Select a single cell in the dataset
  • In the Get & Transform Data group of the Data tab, click on From Table/Range
  • Split Column By Number of Character

In order to achieve the correct result, we need to split the Request Time column into two columns. In total, we will have 3 columns each for hours, minutes and seconds respectively. To do that:

  • Right-click on the Request Time column and Select Split Column
  • In the Split Column, select By Number of Characters

In the Split Column dialogue box, we are going to split by using 2 as the number of characters

Very importantly, we need to specify the split type. By default, Power Query will split Repeatedly. But, that won’t give us what we wanted because we wants to split the seconds first, then we split the minutes.

  • Therefore, select Once, as far right as possible split type

  • Click OK
  • Again, select the original Request Time column and click on Split Column
  • Select By Number of Characters and select Once, as far right as possible split type

  • Click OK

From the screenshot below, we have three columns. One housing splitted  hours, minutes and seconds respectively

Merge Splitted Column using Separator

Next, we want to merge the splitted columns into one and then, we will provide the merge separator. To do this:

  • Select all the three splitted columns only by clicking on the first one, hold down the shift key and click on the three column
  • Right-click and select Merge Columns

  • In the Separator dropdown of the Merged Columns dialogue box, select Colon
  • Optionally provide meaningful for the new column


  • Click OK

From the screenshot below, we can see that the coolumns have been merged into a single column with the colon separator.

Apply Time Data Type

To complete the process, we need to apply Time data type. To do that:

  • Select the data type icon left of the heading and choose Time
  • Click on Home tab and choose Close & Load To…
  • In the import data wizard, Table is automatically selected. Therefore, Click OK

Voila! from the screenshot below, we now have a proper time format

See you in the next tutorial

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *