CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
sc_jal
sc_jal

Reputation: 41

Formatting Existing Excel Cells to Time Format Without Date

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!

Upvotes: 4

Views: 28946

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34220

If you are entering the times as 4-digit numbers, you'll need to do a calculation to get the hours and minutes, then use the TIME function to get an actual time:-

=TIME(A1/100,MOD(A1,100),0)

Another way is

=LEFT(A1,2)/24+RIGHT(A1,2)/1440

but then you have to format the result as a time.

Excel sees a number like 1547 as approximately 4 years on from 1st January 1900 if you format it as a date, so it will come out as something like 26/3/1904 in UK format or 3/26/1904 in US-style format.

Note that the time function can only give you values up to 23:59:59 (stored as 0.999988426), but the second method will give you a datetime value with one or more days as the whole number part. This can be useful if you want to do calculations on times spanning more than one day.

The above behaviour is because dates and times are stored as real numbers with the whole number part representing days and the decimal part representing fractions of a day (i.e. times). In spite of misleading information from Microsoft here, dates actually start from 31/12/1899 (written as 0/1/1900) with serial number 0 and increment by 1 per day from then on.

Upvotes: 0

NicCio
NicCio

Reputation: 41

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.

Upvotes: 3

Related Questions