1

I have a column of start times ranging anywhere from 23:30 (11:30 PM) to 01:00 (1:00 AM). at the end of the year I will have 365 start times. I need an average start time for that column. As long as I am Past midnight Average(A:A) works great. But prior to midnight is a nightmare. Using these 3 numbers 00:01, 00:01, 00:01 the average is 00:01 which is correct and obvious, BUT if I have 23:59, 00:01, 00:01 I get 08:00 as the average. All cells are formatted [hh]:mm. The start time starts at around mid-night and may begin early such as 23:00 or run late such as 01:00. Any ideas on this? I'm Stumped. All times are at night. Times start in column D6, Average is in D5 (+Average(D6:D370) Times will never be sooner than 23:00 or later than 01:30. But they will be 7 days a week for the entire year. Col D
05:06 <-- This is D5 the average with the numbers listed below. in Column "D"

00:12

00:08

00:15

23:54 chang this to 00:00 and average becomes 00:19

01:01

2
  • 1
    Please supply some sample data so we have something to work with. Edit your question, do not reply to this comment. Are all your times in the night or do you have people starting in the middle of the day too? If all are around midnight then you could add an hour or two to your time and average that before subtracting it again? Jan 23 '19 at 10:50
  • 1
    If the times will never be earlier than 23:00, just pretend you are doing the calculation from a time zone one hour to the east. From that perspective, all of the times are after midnight. Add 1 hour to every value, calculate the average, then subtract the hour.
    – fixer1234
    Jan 23 '19 at 11:17
3

Using these data only :

Times start in column D6, Average is in D5 (+Average(D6:D370) Times will never be sooner than 23:00 or later than 01:30.

You may do :

  1. shift all data to (60 minutes) after midnight [ minus 23 hours if more than 2300 hrs, plus 1 hour if more than 0000 hrs ]
  2. find average
  3. then minus the shift value .

Details:

Using another column as helper, say column F. in F6, put :

=IF(D6="","",IF(D6>=TIME(23,0,0),D6-TIME(23,0,0),D6+TIME(1,0,0)))

then in D5 :

=AVERAGE(F:F)-TIME(1,0,0)

Note: if we add 1 hour to everything, 23:30 (numerical value = 0.97916666667) will become 1 day + 30 minutes (numerical value = 1.0208333333), so the average function will count the extra 1 day in the average. Subtracting 23 hours from times after 11 PM will do the same shift (23:30 becomes 0 day + 30 minutes or 00:30 (numerical value = 0.0208333333) but without the extra 1 day in the average calculation.

4
  • That did the trick Thank you so much. Now I can sleep again.
    – Greg Ory
    Jan 23 '19 at 11:46
  • 1
    @fixer1234 intent to do that too.. but if we add 1 hour to everything, 1130 hours (numerical value = 0.97916666667) will become 1 day + 30 minutes (numerical value = 1.0208333333) so the average function will count the extra 1 day in in average. If I minus by 23 hrs, It'll do the same shift (become 0 day + 30 minutes or 0030 hrs (numerical value = 0.0208333333) but without the extra 1 day in the average calc.
    – p._phidot_
    Jan 23 '19 at 11:48
  • @GregOry You're welcomed. Glad it works. You may click on the tick to accept this answer to close it. ( :
    – p._phidot_
    Jan 23 '19 at 11:55
  • errata : * it should be 2330 hrs (in the previous comment), instead of 1130 hours. my bad. || Thanks @fixer1234 for the answer edit. ( :
    – p._phidot_
    Jan 24 '19 at 4:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.