Forum Discussion
How to format cells so 4 digits changes to time format with colon automatically?
For example, I want to be able to type the time as 0502 and it automatically changes to 05:02
Thanks for your help
3 Replies
- thanks
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.
