Forum Discussion

Mich8261's avatar
Mich8261
Copper Contributor
Feb 21, 2024

Converting number to time

I am trying to automate the conversion of a data point, which is supposed to capture the hour and minutes an action takes place. The data entry is meant to capture HH:MM but on the Excel file it comes across as a simple number, so 0030 which is 12:30 AM comes across as 30. 2049 which is 8:49 PM come across as 2049. I can't use duration because it turns 2049 into 2,049 minutes, which is not the same as 8:49 PM. I tried using =TEXT(K7,"H:MM AM/PM") but that only returns every value as 12:00 AM. I was hoping to accomplish this in Power Query, since it's already taking care of other elements in my table.

 

Thank you

 

PS: AM/PM is not essential. 24-hour clock would be fine too.

excel
Formulas and Functions

2 Replies

Sort By
  • smylbugti222gmailcom's avatar
    smylbugti222gmailcom
    Iron Contributor
    Feb 22, 2024

    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.

Resources