Forum Discussion
Formatting / Inputting Help Needed
Hello!
I am trying to put together a spreadsheet detailing media I own (4k, Blu Ray, DVD, etc.). I would like to include the run time but I'm running into an issue with inputting and formatting. I can set the format of the column as hh:mm:ss but I'm forced to hard input the colon between the hh / mm and mm / ss. Is there any way I can format the cells / column where I don't have to use the colon and it will automatically input it. That is, a movie is 1 hour, 23 minutes and 45 seconds, and I want to just type in 012345 instead of having to manually type the colon between the 1 and 2, and between the 3 and 4. Thanks!
If this is just for display purposes, you can apply the custom number format 00\:00\:00 to the column with the run times.
The run times will still be stored as 12345 etc., so you won't be able to use them in calculations such as totaling, at least not easily.
As an alternative, you can use VBA code (in the desktop version of Excel for Windows and Mac) to convert 12345 to the time value 01:23:45 automatically - see Date and Time Entry (this is an old website that does not use https:// but it is safe)
3 Replies
You could always put in some helper columns (one each for hour, minute and second) and then use the TIME function to pull them together...
If this is just for display purposes, you can apply the custom number format 00\:00\:00 to the column with the run times.
The run times will still be stored as 12345 etc., so you won't be able to use them in calculations such as totaling, at least not easily.
As an alternative, you can use VBA code (in the desktop version of Excel for Windows and Mac) to convert 12345 to the time value 01:23:45 automatically - see Date and Time Entry (this is an old website that does not use https:// but it is safe)
That did it! Thanks so much!
