Dec 31 2018 04:20 PM
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
Jan 01 2019 02:15 AM
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
Jan 01 2019 02:14 PM
Jan 02 2019 01:08 AM
SolutionHi 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
Jan 02 2019 02:56 PM
Thanks Mourad, that did the trick. I had no idea the formatting didn't apply immediately.
You've been a big help. Thanks again.
Rick Lizotte
Jan 02 2019 01:08 AM
SolutionHi 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