Trying to turn a field into a time but it has an unsual set up. The cells is imported as "hhh mm:ss" which is not an excel accepted timing. Does anyone have an idea on how to accomplish this.
Trying to turn a field into a time but it has an unsual set up. The cells is imported as "hhh mm:ss" which is not an excel accepted timing. Does anyone have an idea on how to accomplish this.
Last edited by AliGW; 04-03-2019 at 08:56 AM.
There may be an easier way
=LEFT(A1,3)/24+("00:"&RIGHT(A1,5))+0
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Unfortunately, as you can see below it gives you the incorrect time. using that formula
excel.PNG
Format the cells as custom using [h]:mm:ss
What are you expecting to see (you havent said).
What I am expecting to see for the example of 123 21:37 is what is showing on the site in an excel format saying hhh:mm:ss in this term 123 hours 21 minutes and 31 seconds
" in this term 123 hours 21 minutes and 31 seconds "
Thats the first time you've mentioned what format the output should take.
Use this
=LEFT(A1,3)&" hours "&MID(A1,5,2)&" minutes "&RIGHT(A1,2)&" seconds"
but that result will be text, you wont be able to manipulate it as a number.
Using the top forumula and adding the MID. I have been able to get this to show in hhh:mm:ss format. Thanks for the help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks