converting numbers to time

[iosiflupis]

Hello all,

I have run up against a wall. I am trying to convert a 4-digit number to time. I have 4 columns; Sched Dep Time, Act Dep Time, Sched Arr Time, and Act Arr Time. The two Sched columns have times in every cell in the row in the files that I am running the query on. The two Act Arr columns might not have time in a cell, as this indicates that the flight was canceled. When I load and start transforming the data it is pulled in as "null".
The steps that I am applying are:
1. Change type to text and replace values (null to "0000")
2. Split the column by the number of characters (once, as far right as possible)
3. Change the type back to number
4. Merge Columns with a colon as a delimiter
5. Change the type to Time

This is working for the first three columns, Sched Dep Time, Act Dep Time, and Sched Arr Time. However, when I repeat the process for the column Act Arr Time I get an error [Expression.Error] We couldn't parse the input provided as a Time value. This ONLY happens on the Act Arr Time column. Could someone please tell me what I am doing wrong here?

I did ask for help yesterday on how to remove specific characters in specific cells. I received an answer but couldn't understand where to insert it.

 

[Kaper]

It's not obvious to me how you process the data.
As you mentioned null values - it most often (nowadays) means you are using Power Query (Get and Transform Data).
If that's right, you can add "transformed time" column as a conditional column with the condition if column to be transformed is null then #time(0, 0, 0) else your transformationUse such approacho for all input columns, then delete input columns and keet only "transformed time" columns

 

[iosiflupis]

Kaper,

I tried, but I am going to guess that my lack of knowledge and failure to give information may be causing the failure. The original column is a 4-digit number without leading zeros. If I try to change to time type I get Errors. I am gonna sit back, contemplate my navel and think about whether I need the time data at all.

Thank you for the help

 

[Kaper]

Don't give up that easily. Obviously it would be easier to answer if one can see the data itself in a workbook. This forum does not offer such service but other forums (like excelforum.com) do. If you publish the same question on other forum, please remember to post also a lint to this thread (crossposting rule). You may also save (anonymized) file to some public file hosting services