Converting Time

[g4getmoney]

Hello all, excited to be part of the forum!

Hopefully I can help and be helped (with Excel issues at least)

I've hit a bit of a road block.

The report generator I'm working with spits out time values as follows:
3:45 (in h:mm format)
1:20
:15
etc

The problem is with the 3rd example entry.

How do I convert the :15 to 0:15?

I've tried searching the forum and web, and to no avail.

Please help... thanks all,

TGIF.

Just to elaborate:

I've tried adding a custom formatting:
[h]:mm

However, all the entries except the troubled one are converted and correct.

It just seems like Excel is recognizing the field as text.

[martindwilson]

try =IF(LEN(a1)=3,SUBSTITUTE(A1,":","00:"),A1)+0
then copy paste back special values

[g4getmoney]

Worked magically, I thank you kind sir.

Have a great weekend.

try =IF(LEN(a1)=3,SUBSTITUTE(A1,":","00:"),A1)+0
then copy paste back special values