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
microsoft-excel
edited Jan 23 at 12:15
fixer1234
18.7k144982
asked Jan 23 at 10:45
Greg OryGreg Ory
62
add a comment |
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
microsoft-excel
edited Jan 23 at 12:15
fixer1234
18.7k144982
asked Jan 23 at 10:45
Greg OryGreg Ory
62
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 |
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
microsoft-excel
edited Jan 23 at 12:15
fixer1234
18.7k144982
asked Jan 23 at 10:45
Greg OryGreg Ory
62
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
microsoft-excel
microsoft-excel
edited Jan 23 at 12:15
fixer1234
18.7k144982
asked Jan 23 at 10:45
Greg OryGreg Ory
62
edited Jan 23 at 12:15
fixer1234
18.7k144982
asked Jan 23 at 10:45
Greg OryGreg Ory
62
edited Jan 23 at 12:15
fixer1234
18.7k144982
edited Jan 23 at 12:15
fixer1234
18.7k144982
edited Jan 23 at 12:15
fixer1234
18.7k144982
18.7k144982
asked Jan 23 at 10:45
Greg OryGreg Ory
62
asked Jan 23 at 10:45
Greg OryGreg Ory
62
asked Jan 23 at 10:45
Greg OryGreg Ory
62
62
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
1
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
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
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
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
18.7k144982
answered Jan 23 at 11:24
p._phidot_p._phidot_
646412
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
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
18.7k144982
answered Jan 23 at 11:24
p._phidot_p._phidot_
646412
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)
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
18.7k144982
answered Jan 23 at 11:24
p._phidot_p._phidot_
646412
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)
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
18.7k144982
answered Jan 23 at 11:24
p._phidot_p._phidot_
646412
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
18.7k144982
answered Jan 23 at 11:24
p._phidot_p._phidot_
646412
edited Jan 23 at 12:12
fixer1234
18.7k144982
edited Jan 23 at 12:12
fixer1234
18.7k144982
edited Jan 23 at 12:12
fixer1234
18.7k144982
18.7k144982
answered Jan 23 at 11:24
p._phidot_p._phidot_
646412
answered Jan 23 at 11:24
p._phidot_p._phidot_
646412
answered Jan 23 at 11:24
p._phidot_p._phidot_
646412
646412
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
That did the trick Thank you so much. Now I can sleep again.
– Greg Ory
Jan 23 at 11:46
1
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
@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
@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
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 |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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