## 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.

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. 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

2. Pingback: need help with custom function

3. Pingback: Time calculation

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

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