Software

Ask something
Question Locked

Excel Formula

0 helpful
9 replies

by jimmymario1970

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Im entering time in Excel down to 1/100 of a second. Im using the cell format of [h]:mm:ss.00. Once I have the date I wantto use the MAx and Min formulas but they do not work,can anyone tell me how to work with time in thsi format.
ta James

9 replies

by thumbsup2

The problem is the MAX and MIN formulas see the cells they're comparing are times, so it formats the result as standard time of hh:mm:ss, hiding the 100ths. They're there, but they're hidden.

Format the cell containing the MAX and MIN formulas the same way you've formated the cells with the times in them.

I used a custom format mask of [h]:mm:ss.00 in my MAX formula cell like you did in your time cells, and it worked fine.

=MAX(A1:A322)
=MIN(A1:A322)

by jimmymario1970

yes I got it to work yesterday but the answer was 00:00:00:00 meaning it just formated the answer in that format but the time didnt show up, if that makes sense. I just tried again and still giving me the answer of 00:00:00:00, so I must be doing something wrong. Can you format the cell after that data and formula is in or does it have to be done first !

by thumbsup2

.... until I formated the cell after I had put the formula into it.

I tried formatting a cell first, then putting the formula into it and it did NOT work. I got the 00:00:00:00 like you.

The only way I could get it to work was to format the MAX/MIN cells after the formulas were typed.

.

by jimmymario1970

Hi There, I used the Min/Max formula got and answer of 00:00:00:00, thne formated the cell to hh:mm:ss:00 but it still showed 00:00:00:00. Are you saying it showered a time like 00:34:12:25. Can I email you my sheet !!!
james

by thumbsup2

You need to format it as either:

hh:mm:ss.00

or

[h]:mm:ss.00

The period before the zeros instead of a colon makes the difference.

..

by jimmymario1970

I just opened my spreed sheet and formatted the cell with [h]:mm:ss.00 with still the min max producing an answer of 00:00:00:00, then formated the cell with hh:mm:ss.00 and still showing no time only getting 00:00:00:00. Im not sure what else to try ?

Thanks for all your help with this so far
James

by thumbsup2

The first thing I did was to type some times into cells in column A using your format as input (ie: I typed 13:47:50.01, 13:47:50.02, 13:47:50.03). I then did a copy down to create about 150 rows of data to test with, the greatest value (max) being on the bottom row. At this point, the 100ths didn't show in the data, but I knew it would be stored internally, so I didn't mess with the formatting yet.

I then typed the formula ... =MAX(A1:A150) into cell B1. It gave me all zeros as a result and dropped the 100ths same as what showed in column A. Well, we know 00:00:00 isn't right.

I then highlighted the data only, not the entire column, and formatted all of the data cells in column A using the custom data type format and created a new mask of hh:mm:ss.00 (note, that is a period, not a colon before the zeros). I could then see the 100ths were present for all of the times in column A. But, my formula cell still showed all zeros.

So, I formatted cell B1 (my formula cell) using the SAME custom data type format mask of hh:mm:ss.00 and it THEN displayed the last time in column A, which it should have because it was max for my data.

So, I tested with another custom data type format mask in Column A using [h]:mm:ss.00 for all of the cells with times in them. My formula cell B1 changed to showing all zeros and dropping the 100ths again, so I formatted it with the SAME format that was used on the data in column A, ie: [h]:mm:ss.00 ... and it went back to showing me the largest value in column A along with the 100ths.

I think there are two key points here. First, you can not just click the column header and format the whole column. You must highlight JUST the cells with data in them and apply your formatting. Second is that your formula cell MUST be formatted with the same mask as the data it's looking at, or it produces zeros.

Hope this helps.

...

If you want to reply, you'll need to reply to your own post above mine titled Wrong Formula. We've reached max. Just be sure to put in the title who you're replying to.

...

by jimmymario1970

Hey Thumbsup2.

Ive done it !!!!I think it was me highlighting the whole column instead of just the cells with data in it. I have now a working spreedsheet. Thanks so much for your help it been great. Enjoy you day James

James

This discussion is closed to new replies.