Formatting Existing Excel Cells to Time Format Without Date

Question

I'm working on an excel 2010 sheet where I mark down the date and time an event happens. The date is in one column, and auto formats to 17-Nov when I would type in 11-17 (I was fine with this). The time is in a separate column.

I am trying to find the average time an event occurred, without regard to the date, so I would use =AVERAGE(C1:C10). However, I only receive a date back (like 17-APR).

I did not format the cells before I began to enter in data, and I would simply type in a 3:27pm event as 1527, and no reformatting would happen.

Now, when I attempt to reformat the column to hhmm, all the numbers entered so far turn to 0000. When I try to edit the 0000, it is formatted as 6/13/1906 12:00:00 AM.

What I want to do is have the time formatted as hhmm and not include a date in the cell, and be able to run formulas on it, such as the average time an even occurred.

Summary:

*Currently time is entered simply as ####. I entered 3:27pm as 1527.

*Trying to reformat the time column results in 0000 in all cells in the column that previously had a ####.

*Modifying the 0000 displays as 6/13/1906 12:00:00 AM

*I want to format the time as hhmm so I can simply type in 2357, and have it display as 2357, but understand I mean 11:57pm, and let me take averages.

*Hell, even being able to enter 1547 and have it auto format to 15:47 or 3:47p would be great.

Thanks for reading my question!

Solution

An easy way to apply an autoformat (though Excel won't see it as a true "Time") is to go into Format Cells>Custom> and use ##":"##. This will turn 1245 into 12:45. Mind you, this will be a text string so if you copy it to another cell and then apply a time, it will show as 12:00:00. Excel will also not be able to run formulas on it, but it's a quick and dirty way to make it look pretty.

Another option is to have a formula such as =TIME(LEFT(A1,2),RIGHT(A1,2),) where A1 would be replaced with the cell you are actually referencing. This will convert the number to a time that Excel will recognize as a time allowing you to run other functions on it, but requires another column.