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.
wmfexcel 
Pingback: Time Conversion – How to convert 24hr input as 4-digit number into TIME in Excel? | wmfexcel
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!
LikeLike
Thanks for your comment.
If you are interested in Excel Forum, I would absolutely recommend this:
http://www.mrexcel.com/forum/forum.php
Cheers,
LikeLike
Pingback: need help with custom function
Pingback: Time calculation
Pingback: What would be the formula for this?