Forum Discussion
How to format cells so 4 digits changes to time format with colon automatically?
It depends.
If you type 0502 as the text, it's nothing to do with that.
If the number 502 you may apply custom number format 00\:00, but the value still will be 502, just shown as 05:02
If you'd like to convert the text "0502" into the time 05:02 which is equal to number 0.209722222222222 - perhaps with VBA
To elaborate a bit on the point that SergeiBaklan makes, when he says, "If you'd like to convert the text "0502" into the time 05:02 which is equal to number 0.209722222222222 - perhaps with VBA" --the implications of this are that IF you are planning to calculate elapsed time between 05:02 and say 15:22, then you need to have it not just "appear to your human eye" a certain way, AS IF it were time, but you'd need to have the underlying value be something that Excel recognizes as time.
I have no idea how one would use VBA to convert--not a VBA user myself--but it's important that you realize the distinction between the appearance of a time value and the actual underlying data. You gave no reason for your request, which is why I wanted to add this point of clarification.
