Forum Discussion
Time data issues
Hello all,
As I mentioned in my first post, I am a complete novice with Excel, so I will probably be here often.
I am having a terrible time working with elapsed time data. I’m trying to enter the time as minutes and seconds and get an average of the data. On the included worksheet, rows 7 and 11 are the time rows. Both are now formatted h:mm. (I couldn’t make mm:ss work either.) In row 7, the data has a time value from B7:J7; an ‘as entered’ value (like 'Text') from K7:R7; and from S7:AM7, the value reverts back to time.
In row 11, the data has an ‘as entered’ value from B11:AI11 then changes to a time value. Also, the data entered in AL11 just isn’t right. When I enter 25:58, it shows in the cell as 1:58 and a time value in the formula bar. As near as I can tell, none of the other cells behave this way. As a result of this, the average function is going nuts.
I’ve tried converting the entered time values to decimal; getting the average; and putting the result back into mm:ss, but either that won’t work, or I’m doing something wrong.
I’m using Excel Office 365.
Any help would be greatly appreciated,
Rick Lizotte
Hi Rick,
it looks like that some entries were e.g. entered as text and preserves that even after having changed the format to e.g. [h]:mm. Please e.g. double click the value and press Enter. Will then change the value to a time value (appears then as e.g. 15:53:00 in the formula bar).
Best,Mourad
4 Replies
Hi Rick,
with your choosen custom format hh:mm, the display does not take into account the number of elapsed days. Therefore it shows you 01:58 = 25:58-24:00 in the cell. The formula bar correctly shows 1 day (01.01.1900) + the time (01:58). You can enclose the hour code with squared brackets in your custom format for including the elapsed days into the display, e.g. [h]:mm.
Please have a look in this article for more possiblities regarding date and time formats in Excel.
Best,
Mourad
- Thanks Mourad, that worked for that specific cell, but why do the cell entries in both rows 7 and 11 show a combination of text entries and time entries when both rows are formatted as time? I don't understand that at all.Thanks again for your help,Rick Lizotte
Hi Rick,
it looks like that some entries were e.g. entered as text and preserves that even after having changed the format to e.g. [h]:mm. Please e.g. double click the value and press Enter. Will then change the value to a time value (appears then as e.g. 15:53:00 in the formula bar).
Best,Mourad
