Excel Formula

By jimmymario1970 ·
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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

re: Excel Formula

by ThumbsUp2 In reply to Excel Formula

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.


Collapse -

re: Excel Formula

by jimmymario1970 In reply to re: Excel Formula

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 !

Collapse -

I got the same thing...........

by ThumbsUp2 In reply to re: Excel Formula

.... 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.


Collapse -

Excel formula

by jimmymario1970 In reply to I got the same thing..... ...

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 !!!

Collapse -

Wrong formating....

by ThumbsUp2 In reply to Excel formula

You need to format it as either:




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


Collapse -

Wrong Formula

by jimmymario1970 In reply to Wrong formating....

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

Collapse -

Ok, here's what I did to test it.....

by ThumbsUp2 In reply to Wrong Formula

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.


<edited for afterthought>

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.


Collapse -

Wrong Formula

by jimmymario1970 In reply to Wrong Formula

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


Related Discussions

Related Forums