Average Time In Excel

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

edited Jan 23 at 12:15

fixer1234

19k144982

asked Jan 23 at 10:45

Greg Ory

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?

– Kevin Anthony Oppegaard Rose
Jan 23 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 at 11:17

add a comment |

00:12

00:08

00:15

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

01:01

edited Jan 23 at 12:15

fixer1234

19k144982

asked Jan 23 at 10:45

Greg Ory

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?

– Kevin Anthony Oppegaard Rose
Jan 23 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 at 11:17

add a comment |

00:12

00:08

00:15

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

01:01

edited Jan 23 at 12:15

fixer1234

19k144982

asked Jan 23 at 10:45

Greg Ory

00:12

00:08

00:15

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

01:01

microsoft-excel

edited Jan 23 at 12:15

fixer1234

19k144982

asked Jan 23 at 10:45

Greg Ory

edited Jan 23 at 12:15

fixer1234

19k144982

asked Jan 23 at 10:45

Greg Ory

edited Jan 23 at 12:15

fixer1234

19k144982

edited Jan 23 at 12:15

fixer1234

19k144982

edited Jan 23 at 12:15

fixer1234

19k144982

asked Jan 23 at 10:45

Greg Ory

asked Jan 23 at 10:45

Greg Ory

asked Jan 23 at 10:45

Greg Ory

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?

– Kevin Anthony Oppegaard Rose
Jan 23 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 at 11:17

add a comment |

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?

– Kevin Anthony Oppegaard Rose
Jan 23 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 at 11:17

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?

– Kevin Anthony Oppegaard Rose
Jan 23 at 10:50

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 at 11:17

add a comment |

1 Answer
1

active

oldest

votes

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 :

shift all data to (60 minutes) after midnight [ minus 23 hours if more than 2300 hrs, plus 1 hour if more than 0000 hrs ]

find average

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.

edited Jan 23 at 12:12

fixer1234

19k144982

answered Jan 23 at 11:24

p._phidot_

691412

That did the trick Thank you so much. Now I can sleep again.

– Greg Ory
Jan 23 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 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 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 at 4:39

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1397400%2faverage-time-in-excel%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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 :

shift all data to (60 minutes) after midnight [ minus 23 hours if more than 2300 hrs, plus 1 hour if more than 0000 hrs ]

find average

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)

edited Jan 23 at 12:12

fixer1234

19k144982

answered Jan 23 at 11:24

p._phidot_

691412

That did the trick Thank you so much. Now I can sleep again.

– Greg Ory
Jan 23 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 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 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 at 4:39

add a comment |

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 :

shift all data to (60 minutes) after midnight [ minus 23 hours if more than 2300 hrs, plus 1 hour if more than 0000 hrs ]

find average

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)

edited Jan 23 at 12:12

fixer1234

19k144982

answered Jan 23 at 11:24

p._phidot_

691412

That did the trick Thank you so much. Now I can sleep again.

– Greg Ory
Jan 23 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 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 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 at 4:39

add a comment |

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 :

shift all data to (60 minutes) after midnight [ minus 23 hours if more than 2300 hrs, plus 1 hour if more than 0000 hrs ]

find average

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)

edited Jan 23 at 12:12

fixer1234

19k144982

answered Jan 23 at 11:24

p._phidot_

691412

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 :

shift all data to (60 minutes) after midnight [ minus 23 hours if more than 2300 hrs, plus 1 hour if more than 0000 hrs ]

find average

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)

edited Jan 23 at 12:12

fixer1234

19k144982

answered Jan 23 at 11:24

p._phidot_

691412

edited Jan 23 at 12:12

fixer1234

19k144982

edited Jan 23 at 12:12

fixer1234

19k144982

edited Jan 23 at 12:12

fixer1234

19k144982

answered Jan 23 at 11:24

p._phidot_

691412

answered Jan 23 at 11:24

p._phidot_

691412

answered Jan 23 at 11:24

p._phidot_

691412

That did the trick Thank you so much. Now I can sleep again.

– Greg Ory
Jan 23 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 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 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 at 4:39

add a comment |

That did the trick Thank you so much. Now I can sleep again.

– Greg Ory
Jan 23 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 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 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 at 4:39

That did the trick Thank you so much. Now I can sleep again.

– Greg Ory
Jan 23 at 11:46

@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 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 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 at 4:39

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Super User!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Argthtjtr