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.

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.