Your data fields where you are entering in your times should need to be changed from "General" or "Number" format to the custom format of HH:MM. Note that your "formula" or "calculation" fields (average, total time, etc) MUST have the custom format of MM:SS once you have divided by 60 as Excel's default thinking is in HH:MM (hence this issue). =(AVERAGE(B2:B90)/60) and =MIN(C:C) (this is a direct check so no /60 needed here!). As mentioned above, 1D:3H:5M divided by 60 = 27M:5S. If my data was across the 5 time tracking fields was the 4:06, 3:15, 9:12, 2:54, 7:38 (representing MM:SS for us, but the data in the background is actually HH:MM) then when I work out the sum of those 5 fields are, what I want should be 27M:05S but what shows instead is 1D:03H:05M:00S. to total up my total time or average time across 100 cells I would use the normal SUM or AVERAGE formulas and then divide by 60 in the formula.Įxample =(SUM(A1:A5))/60. Accordingly, if I take my 4:06 and divide by 60 when working with the data (eg. So 7H:15M / 60 = 7M:15S - I hope you can see where this is going. All my cells were entered as a Custom Format to only show "HH:MM" - if I entered in "4:06" (being 4 minutes and 6 seconds) the field would show the numbers I entered correctly - but the data itself would represent HH:MM in the background.įortunately time is based on factors of 60 (60 seconds = 60 minutes). The best way I found to resolve this issue was by using a combination of the above.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |