Converting :00 to 0:00

[baijixu]

Hello,

I am working on a report that uses a lot of time data. Our reporting tool outputs seconds like this :50 The problem with this is when I paste this into excel, excel does not recognize this as a time or a valid number, only if the field goes over :59 -- then it shows up as 1:00 or one minute.

Here is my example:
excel.jpg

This is causing a problem for our charting because excel is not recognizing this in time. If I go through and add a 0 manually to make it 0:50 then excel will recognize it, however, there are over 2000 lines like this.

I have tried =TEXT(CELL,"0000") but this does not work and I have tried =CONCATENATE("0000",CELL) and neither of these methods work.

Is there a way to format these cells or is their a formula I can use to fix this?

Thanks for your help!

[SDCh]

try this:

Please Login or Register  to view this content.

Note: because you not attach file example, I don't know the real condition of the cell that has value ":XX"

[baijixu]

That worked nearly perfectly, however, now any times that are over a minute i.e. 1:00 show up as #VALUE instead of 1:00.

[SDCh]

Try this:

Please Login or Register  to view this content.

And like what I said, I don't know the real condition of your worksheet, if this still error,
you must upload a sample work book, (all sensitive data removed), showing what data you are working with,
a few examples of what your expected outcome is, and how you arrived at that.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

[baijixu]

Okay your last suggestion fixed the problem! I have attached the excel sheet sample as a reference. I appreciate your help very much!

[baijixu]

time-fix.xlsx

There's the attachment.

[SDCh]

Glad know your problem solved and thanks for the rep.

By the way, please mark this thread as Solved if your problem really solved,
to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved