Forum Discussion
time average in excel??
hi, im trying to average times in format hh:mm, for example 22:00 21:30 03:00 00:00 so i get average between the smallest and latest value? any help thanks
When working with times that span midnight, you should include the dates:
3-May-2021 22:00
3-May-2021 21:30
4-May-2021 03:00
4-May-2021 00:00
The average of these is 3-May-2021 23:37. You can format the cell with the average formula to just show the time using a number format of hh:mm, which keeps the date in the cell. Or you can adjust the formula to:
=AVERAGE(A1:A4)-INT(AVERAGE(A1:A4))
3-May-2021 22:00
3-May-2021 21:30
4-May-2021 03:00
4-May-2021 00:00
The average of these is 3-May-2021 23:37. You can format the cell with the average formula to just show the time using a number format of hh:mm, which keeps the date in the cell. Or you can adjust the formula to:
=AVERAGE(A1:A4)-INT(AVERAGE(A1:A4))