Feb 21 2024 10:26 AM
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.
Feb 21 2024 11:47 AM
For example in L7:
=TIMEVALUE(TEXT(K7,"00\:00"))
Format the cell with the formula as time.
Feb 22 2024 12:04 AM
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
= Time.FromText(Text.PadStart(Number.ToText([YourColumnName]), 4, "0"), "0000")
Explanation:
Method 2: Using Derived Column
Time.From(Value, #duration(0, 0, Hour, Minute))
Explanation:
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:
By following these steps, you can efficiently convert the time numbers in your Power Query table to the desired format.