Converting number to time

Mich8261 

Here's how you can convert the numbers representing time in your Power Query table to a 24-hour format:

Method 1: Using Custom Column

1. In Power Query Editor, select the column containing the time numbers.
2. Go to the Add Column tab and click Custom Column.
3. In the formula bar, enter the following formula:

= Time.FromText(Text.PadStart(Number.ToText([YourColumnName]), 4, "0"), "0000")

Explanation:

• Number.ToText([YourColumnName]): Converts the number in your column to text.
• Text.PadStart(..., 4, "0"): Pads the text with leading zeros to ensure a four-digit format (e.g., 0030, 2049).
• Time.FromText(..., "0000"): Converts the padded text to a time value, assuming the format "HHMM".

Method 2: Using Derived Column

1. Select the column containing the time numbers.
2. Go to the Transform tab and click Split Column.
3. Choose By Number of Characters and set the Number of characters to 4.
4. In the Name column for each of the two split columns, enter "Hour" and "Minute", respectively.
5. Right-click the "Hour" column and choose Transform > Unpivot Other Columns.
6. In the Value Column dropdown, select the "Minute" column.
7. Click OK.
8. Right-click the combined "Hour" and "Minute" column and choose Transform > Combine.
9. Choose Custom and enter the following formula:

Time.From(Value, #duration(0, 0, Hour, Minute))

Explanation:

• This method splits the four-digit number into separate "Hour" and "Minute" columns.
• It then combines them using a custom formula that creates a time value from the extracted hour and minute values.

Both methods achieve the same result of converting the numbers to a 24-hour time format. Choose the method that you find more convenient based on your preference.

Additional Notes:

• Replace [YourColumnName] with the actual name of your column containing the time numbers.
• If you need the time in AM/PM format, you can modify the formula in Method 1 to include formatting options.

By following these steps, you can efficiently convert the time numbers in your Power Query table to the desired format.

Mich8261 

For example in L7:

=TIMEVALUE(TEXT(K7,"00\:00"))

Format the cell with the formula as time.