Home

Time Conversion Issue

%3CLINGO-SUB%20id%3D%22lingo-sub-762880%22%20slang%3D%22en-US%22%3ETime%20Conversion%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-762880%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20am%20trying%20to%20converter%20the%20IND%20Time%2FCON%20Time%20from%20number%20to%20time.%20The%20problem%20seems%20to%20be%20that%20its%20trying%20(no%20matter%20what%20format%20in%20the%20time%20section%20i%20pick)%20to%20put%20a%20date%20in%20then%20a%20time%20and%20it%20just%20converts%26nbsp%3Bthe%20column%20to%20all%26nbsp%3Bmidnight%20of%20a%20random%20date.%20The%20screenshot%20shows%20the%20number%20values%20in%20the%20first%20column%2C%20each%20different%20TIME%20format%20option%20that%20i%20can%20choose%2C%20and%20the%20complete%20text%20that%20excel%20converts%20the%20number%20to.%20None%20of%20the%20options%20come%20up%20with%20JUST%20a%20HH%3AMM%3ASS.%20They%20all%20have%20a%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-762880%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-763025%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20Conversion%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-763025%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F378347%22%20target%3D%22_blank%22%3E%40Frost2FA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%3C%2FP%3E%0A%3CP%3EFormat%20column%20B%20as%20Time%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20use%20the%20formula%26nbsp%3B%3D--(LEFT(A3%2C2)%26amp%3B%22%3A%22%26amp%3BMID(A3%2C3%2C2)%26amp%3B%22%3A%22%26amp%3B(RIGHT(A3%2C2)))%3C%2FP%3E%0A%3CP%3EThe%20formula%20parses%20the%20value%20in%20column%20A%20into%20its%20three%20numeric%20components%20and%20adds%20a%20%22%3A%22%20in%20between%20each%20set.%3C%2FP%3E%0A%3CP%3EThe%20double%20unary%20minus%20%22--%22%20at%20the%20beginning%20of%20the%20formula%20then%20coerces%20the%20resultant%20text%20to%20a%20numeric%20and%20it%20displays%20with%20the%20correct%20time%20format.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-763420%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20Conversion%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-763420%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F106033%22%20target%3D%22_blank%22%3E%40Roger%20Govier%3C%2FA%3E%26nbsp%3BThis%20almost%20solved%20it!!%20The%20only%20issue%20I%20ran%20into%20was%20single%20digit%20hours%20didn't%20make%20the%20cut%20or%20formatted%20kind%20of%20crazy.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-770699%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20Conversion%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-770699%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F378347%22%20target%3D%22_blank%22%3E%40Frost2FA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECouple%20of%20more%20variants%3C%2FP%3E%0A%3CPRE%3E%3D--REPLACE(REPLACE(A1%2CINT(LEN(A1)%2F2)%2C0%2C%22%3A%22)%2CLEN(A1)%2C0%2C%22%3A%22)%3C%2FPRE%3E%0A%3CP%3Eand%3C%2FP%3E%0A%3CPRE%3E%3DTIME(LEFT(A1%2CLEN(A1)-4)%2CLEFT(RIGHT(A1%2C4)%2C2)%2CRIGHT(A1%2C2))%3C%2FPRE%3E%0A%3CP%3Eapplying%20HH%3AMM%3ASS%20format%20to%20the%20result%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-770725%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20Conversion%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-770725%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F378347%22%20target%3D%22_blank%22%3E%40Frost2FA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EThis%20issue%20can%20be%20fixed%20in%20one%20of%202%20ways%20without%20the%20need%20of%20formulas%20and%20functions%3C%2FP%3E%3CP%3E%3CSTRONG%3EMethod%201%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EPower%20Query%3C%2FSTRONG%3E%3C%2FP%3E%3COL%3E%3CLI%3EConvert%20the%20column%20showing%20date%20and%20time%26nbsp%3B%20(or%20even%20the%20entire%20list)%20into%20Table%20(CTRL%20%2BT)%3C%2FLI%3E%3CLI%3EClick%20on%20the%20Data%20Tab%20%26gt%3B%26gt%3B%20From%20Table%20%26gt%3B%26gt%3B%20Most%20probably%20there%20will%20be%20an%20automatic%20change%20in%20Data%20Type%20to%20the%20correct%20Date.%20If%20Not%3C%2FLI%3E%3CLI%3ESelect%20the%20Date%20column%20to%20Fix%20%26gt%3B%26gt%3B%20Click%20on%20Add%20Column%26nbsp%3B%3C%2FLI%3E%3CLI%3ETo%20the%20Right%20side%20%26gt%3B%26gt%3B%20Click%20on%20Date%20%26gt%3B%26gt%3B%20Date%20Only%3C%2FLI%3E%3CLI%3EOr%20Click%20on%20Time%20%26gt%3B%26gt%3B%20Time%20Only%3C%2FLI%3E%3CLI%3ERemove%20unwanted%20columns%20(right%20Click%20%26gt%3B.remove)%3C%2FLI%3E%3CLI%3EClick%20on%20the%20Home%20Tab%20%26gt%3B%26gt%3B%20Close%20and%20load%20%26gt%3B.%20Data%20Fixed%20an%20sent%20back%20to%20Excel%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EMethod%202%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EText%20To%20Column%3C%2FSTRONG%3E%3C%2FP%3E%3COL%3E%3CLI%3Eselect%20the%20Column%20to%20Fix%3C%2FLI%3E%3CLI%3EData%20Tab%20%26gt%3B%26gt%3B%20Text%20To%20Column%3C%2FLI%3E%3CLI%3EStep%201%20of%20the%20Wizard%20%26gt%3B%26gt%3B%20Select%20Delimiter%3C%2FLI%3E%3CLI%3EStep%202%20%26gt%3B%26gt%3B%20Select%20Space%3C%2FLI%3E%3CLI%3EStep%203%20%26gt%3B%26gt%3B%20Select%20MDY%20(for%20Date)%3C%2FLI%3E%3CLI%3ESelect%20Destination%20to%20paste%20Fixed%20Data%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-770746%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20Conversion%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-770746%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3EIMHO%2C%20both%20won't%20convert%20123456%20into%2012%3A34%3A56%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frost2FA
New Contributor

 

So I am trying to converter the IND Time/CON Time from number to time. The problem seems to be that its trying (no matter what format in the time section i pick) to put a date in then a time and it just converts the column to all midnight of a random date. The screenshot shows the number values in the first column, each different TIME format option that i can choose, and the complete text that excel converts the number to. None of the options come up with JUST a HH:MM:SS. They all have a date.

 

6 Replies

@Frost2FA 

Hi

Format column B as Time 

Then use the formula =--(LEFT(A3,2)&":"&MID(A3,3,2)&":"&(RIGHT(A3,2)))

The formula parses the value in column A into its three numeric components and adds a ":" in between each set.

The double unary minus "--" at the beginning of the formula then coerces the resultant text to a numeric and it displays with the correct time format.

@Roger Govier This almost solved it!! The only issue I ran into was single digit hours didn't make the cut or formatted kind of crazy.

@Frost2FA 

Couple of more variants

=--REPLACE(REPLACE(A1,INT(LEN(A1)/2),0,":"),LEN(A1),0,":")

and

=TIME(LEFT(A1,LEN(A1)-4),LEFT(RIGHT(A1,4),2),RIGHT(A1,2))

applying HH:MM:SS format to the result

@Frost2FA 

Hi

This issue can be fixed in one of 2 ways without the need of formulas and functions

Method 1:

Power Query

  1. Convert the column showing date and time  (or even the entire list) into Table (CTRL +T)
  2. Click on the Data Tab >> From Table >> Most probably there will be an automatic change in Data Type to the correct Date. If Not
  3. Select the Date column to Fix >> Click on Add Column 
  4. To the Right side >> Click on Date >> Date Only
  5. Or Click on Time >> Time Only
  6. Remove unwanted columns (right Click >.remove)
  7. Click on the Home Tab >> Close and load >. Data Fixed an sent back to Excel

 

Method 2:

Text To Column

  1. select the Column to Fix
  2. Data Tab >> Text To Column
  3. Step 1 of the Wizard >> Select Delimiter
  4. Step 2 >> Select Space
  5. Step 3 >> Select MDY (for Date)
  6. Select Destination to paste Fixed Data

 

Hope that helps

Nabil Mourad

 

@nabilmourad ,

IMHO, both won't convert 123456 into 12:34:56

@Sergei Baklan 

Hi Sergei, How are you... It's too late in the evening now  :)

it's exactly 43670.0902777778

 

Well, you know, I am talking about the rightmost column in the screenshot...

When we do not have sample Excel file then we are just guessing what the question is

Take care my friend

Nabil Mourad

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies