converting text to time

Keyrw01

New Member
Joined
Sep 29, 2018
Messages
5
Good Morning

I am looking for a little help if possible.

I have data that needscoverting to an hh:mm:as format.

the data I am getting is in the following formats and need it all changing to same format as above.

22s
22m 22s
2h 22m 22s

any help would be much apreciated
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

MARZIOTULLIO

Well-known Member
Joined
Aug 22, 2015
Messages
767
A
B
1
22s
00:00:22​
2
22m 22s
00:22:22​
3
2h 22m 22s
02:22:22​

<tbody>
</tbody>


B1=
TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",""),"m",""),"s","")," ","")+0,"00\:00\:00")+0 copy down

Custom format column B hh:mm:ss
 

Keyrw01

New Member
Joined
Sep 29, 2018
Messages
5
A
B
1
22s
00:00:22​
2
22m 22s
00:22:22​
3
2h 22m 22s
02:22:22​

<tbody>
</tbody>


B1=
TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",""),"m",""),"s","")," ","")+0,"00\:00\:00")+0 copy down

Custom format column B hh:mm:ss

First of all thank you very much for the reply.

I have tried the formula that you have provided but I get the error stating 'There's a problem with this formula'
 

Keyrw01

New Member
Joined
Sep 29, 2018
Messages
5

ADVERTISEMENT

Can you post your file ?

unfortunately not. It has personal details of employees on there.

I have tried the same formula on a fresh spreadsheet and I'm getting the same issue.

im thinking I'm probably doing it wrong somehow
 

Keyrw01

New Member
Joined
Sep 29, 2018
Messages
5
Can you post your file ?


A
B
1
22s
00:00:22​
2
22m 22s
00:22:22​
3
2h 22m 22s
02:22:22​

<tbody>
</tbody>


B1=
TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",""),"m",""),"s","")," ","")+0,"00\:00\:00")+0 copy down

Custom format column B hh:mm:ss

the new issue that I am having is that if there is a single number of hours minutes and seconds it will not bring it out correctly.

an example would be 1h 1m 1s. this would be brought back as 00:01:11.
any help would be brilliant
 

Watch MrExcel Video

Forum statistics

Threads
1,108,732
Messages
5,524,516
Members
409,583
Latest member
gkarthick

This Week's Hot Topics

Top