Formula > Convert date to time format

**Ransom12** · 05-26-2014, 03:45 PM

Hi all,

I am fairly savvy in using excel for my needs, but this problem has literally stumped me and is why I am here asking for your expert advice

In Column A1>A3 cell the following will appear from some raw data :

2d, 3h, 45m
or
3h, 45m
or
45m

I need to turn this into the following format :

48:03:45
&
00:03:45
&
00:00:45

I have literally no idea where to begin with this so please if anyone can help, it would be greatly appreciated.

Any questions please just shout.

Thanks.

**zbor** · 05-26-2014, 03:52 PM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

**mrice** · 05-26-2014, 04:04 PM

Try this user defined function

Please Login or Register  to view this content.

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In B2, enter =TimeSpecial(A1)

Remember to save the workbook as a macro enabled workbook .xlsm

**zbor** · 05-26-2014, 04:11 PM

Check does this help.
Format cell as [h]:mm:ss

Works for XL2007 and newer

Formula:

Please Login or Register  to view this content.

**Ransom12** · 05-26-2014, 04:18 PM

Originally Posted by mrice

Try this user defined function

Please Login or Register  to view this content.

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In B2, enter =TimeSpecial(A1)

Remember to save the workbook as a macro enabled workbook .xlsm

Yes this works perfectly. I am not sure the policy on Macros with work, but it absolutely worked a treat.