Let's suppose I have cells in Excel with time duration data from a website, copied and pasted in a text file. The format is (hh:mm:ss), like 00:16:30 (equivalent to 0 hours, 16 minutes, and 30 seconds). However, upon opening the text file in Excel, the format switches to (mm:ss:hh), displaying 16:30:00, which Excel reads as (16 hours, 30 minutes, and 0 seconds). How can this problem be fixed or how can the cell be reflected or reversed?
Add a comment
|
1 Answer
Here is one way you could try:
=TEXTJOIN(":",,SORTBY(TEXTSPLIT(TEXT(A1,"[h]:mm:ss"),":"),-SEQUENCE(,3)))
Updated solution based on OP's edit after the initial answer was posted
=TEXTJOIN(":",,CHOOSECOLS(TEXTSPLIT(TEXT(A1,"[h]:mm:ss"),":"),3,1,2))
Per OP:
Thank you for your prompt reply and sorry for any inconvenience. I am sure I am missing something, your solution didn't work for me. the cells is a custom format 16:30 is readed by excel 4:30:00 PM !!!
Dont see any issues here still, working on my end!
With general value as 0.6875
The given formula still working!!
Posting a screenshot of the file sent by OP over Discussions Chat:
answered Jun 27 at 7:57
-
sorry I have updated my problem .. your solution didnt work either, maybe i am missing somthing– hsawiresCommented Jun 27 at 8:04
-
Your query was something else, now once an answer was posted you changed the query altogether!, This can also be amended! Commented Jun 27 at 8:05
-
-
Thank you for your prompt reply and sorry for any inconvenience. I am sure I am missing something, your solution didn't work for me. the cells is a custom format 16:30 is readed by excel 4:30:00 PM !!!– hsawiresCommented Jun 27 at 8:18
-
@hsawires could you make it a general value and show me what is it showing? Commented Jun 27 at 8:21