Time Conversion (2)

How to convert 24hr input as 4-digit number into TIME in Excel? i.e. turn 500 into 05:00 correctly in Excel.

Image

Answer:

=TEXT(Your 4-digit number,”00\:00″)+0 ‘Format the result as TIME

About a quarter ago, I wrote a post about how to convert a 4-digit number into TIME with a formula using ROUND, MOD functions together with some mathematical operations.

Just last week, I wrote another post about how to convert an 8-digit number into Excel-recognizable Date, which uses TEXT function basically.

I like the latter trick more as I considered that more intuitive and easier to use.  The trick is also applicable to converting a 4-digit number into TIME, with just a little modification:

=TEXT(Your 4-digit number,”00\:00″)+0 ‘Format the result as TIME

i.e. it turns something like 0123 to 01:23 nicely in Excel.

To learn more how it works, check my post on how to convert an 8-digit number into Excel-recognizable Date as they follow the same logic.

See!? We may achieve the same goal in so many different ways in Excel.

p.s. I learned both tricks from MrExcel forum.  Thanks to all the experts there.

Advertisements
This entry was posted in Excel Tips, Formula and tagged , . Bookmark the permalink.

6 Responses to Time Conversion (2)

  1. Pingback: Time Conversion – How to convert 24hr input as 4-digit number into TIME in Excel? | wmfexcel

  2. Very good blog you have here but I was wondering if you knew of any forums that cover the same topics
    discussed here? I’d really like to be a part of online community where I can get comments from other experienced individuals that share
    the same interest. If you have any suggestions, please let me know.
    Cheers!

    Like

  3. Pingback: need help with custom function

  4. Pingback: Time calculation

  5. Pingback: What would be the formula for this?

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s