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.
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.
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.
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,
Best Response confirmed by
Rick.Lizotte (Occasional Contributor)