Forum Discussion
somme.si avec comme critere le temps
j'utilise somme.si pour additionner le temps et je prend comme critère le temps lui même (en minute) mais j'ai des résultat erroné et merci d'avance par exemple je veut avoir la somme des temps supérieur a 00:10
habib_a You stumbled an a floating point error (Google for that if you want to know more about it). Excel has problems with very large and very small numbers. Enter 10 minutes by itself and display it as a number with more than 15 decimals. Then compare it with the 15 decimal number of the calculated value of 10 minutes (thus an end time minus a start time) and you will note that they are not the always the same. In your case, SUMIF missed quite a few rows with "calculated 10 minutes" were just a fraction smaller that the "clean 10 minutes". Solved that by rounding the calculated duration, as well as the criteria, to fewer decimals,. Now the SUMIF and the manual calculation give the same result. See attached.
6 Replies
- With the permission of all involved, if you allow me to add this additional information to the very good explanation from Mr. @Riny_van_Eekelen , where all the different possibilities of time counting are explained in detail by Microsoft.
Calculate the difference between two times
Calculate the time difference in minutes
Formula: = (B1-A1) * 1440
Number format: standard
Because: 1 day ~ 24 hours * 60 minutes = 1,440 minutesThank you for your patience and time.
Nikolino
I know I don't know anything (Socrates)
thank you Mr NikolinoDE for you time and information
- habib_a Is it perhaps so the the sum of the times becomes greater than 23 hours and 59 minutes? If so, make sure to custom format the cell that contains the end result as [h]:mm or [mm] to display the duration as hours and minutes or as minutes alone.For instance, if the total duration equals 2108 minutes, use custom format [mm] to display as 2108. Use [h]:mm to display 35:08If the cell is formatted as Time (as in clock-time) or as hh:mm:ss, Excel will display, 11:08:00 being the number of "clock" hours after midnight.See the attached workbook for an example.
thank you for your support but the problem is not due to this tip here is a file extract to better understand the situation
habib_a You stumbled an a floating point error (Google for that if you want to know more about it). Excel has problems with very large and very small numbers. Enter 10 minutes by itself and display it as a number with more than 15 decimals. Then compare it with the 15 decimal number of the calculated value of 10 minutes (thus an end time minus a start time) and you will note that they are not the always the same. In your case, SUMIF missed quite a few rows with "calculated 10 minutes" were just a fraction smaller that the "clean 10 minutes". Solved that by rounding the calculated duration, as well as the criteria, to fewer decimals,. Now the SUMIF and the manual calculation give the same result. See attached.
