Time Conversion

An end user entered 8 pages of time data-but entered them all as straight numbers! How can I take a number like 2149 and convert it to 21:49? I'd be fine with just this conversion and then I could change the format to time, I think.

If not, then: how do I take a straight number like 2149 and make it be the HH:MM 21:49? (I will be doing time calculations on this)

Kitty

Assuming that number is in A1, you could do this in B1:

=VALUE(INT(A1/100)&":"&MOD(A1,100)&":00")

Format the cell as [h]:mm, then copy down. If you fix the values of those formulae in column B, then you could delete column A.

Hope this helps.

Pete

As

Please Login or Register  to view this content.

produces a time, you can do it in a small piece of VBA.
Here is a draft (untested)

Please Login or Register  to view this content.

That's brilliant! For whatever reason, they both work and I would never have figured it out by myself! Thanks-you've just saved me a couple of hours of typing.

One more question-and this is fairly obvious. I now have a column (A) that contains my original data: 1849 I have a column (B) that contains a forumla that references the first number, and shows as 18:49. How can I both delete column A and keep my number while keeping it formatted as time in B?

Originally Posted by Kitty Tambling

How can I both delete column A and keep my number while keeping it formatted as time in B?

That's what I referred to at the end of my post - you need to fix the values. To do that, select column B by clicking on the B at the top of the column, then click <copy> (or CTRL-C if you prefer), then right-click and choose Paste-special, then click against Values, then OK and press the <Esc> key. Now you can delete column A.

Hope this helps.

Pete

Originally Posted by shg

Or

=--text(a1, "0\:00")

That's rather beautiful with the caveat that I don't understand it!