Gathering data from a list of dates
I have a long list of several thousand dates and times of people paying for a bill at a restaurant.
From this list I would like to try to understand:
What are the peak hours (eg: do most people pay for the bill from 6-7PM)
And what are the peak days of the week (eg: most people pay for their bill on Wednesdays around 7PM)
Ideally I am looking for:
A way to visualize all the data over a 1 week period to see frequency over time
A way to visualize all the data over 1 day to see frequency over time
What tools / formulas could I use to answer my questions which are mainly: What time(s) is the restaurant at its busiest and what day(s) of the week is the restaurant at its busiest.
microsoft-excel
asked Nov 9 '15 at 15:11
jmasterxjmasterx
3302615
add a comment |
I have a long list of several thousand dates and times of people paying for a bill at a restaurant.
From this list I would like to try to understand:
What are the peak hours (eg: do most people pay for the bill from 6-7PM)
And what are the peak days of the week (eg: most people pay for their bill on Wednesdays around 7PM)
Ideally I am looking for:
A way to visualize all the data over a 1 week period to see frequency over time
A way to visualize all the data over 1 day to see frequency over time
What tools / formulas could I use to answer my questions which are mainly: What time(s) is the restaurant at its busiest and what day(s) of the week is the restaurant at its busiest.
microsoft-excel
asked Nov 9 '15 at 15:11
jmasterxjmasterx
3302615
This question is a bit open-ended. What have you tried already?
– Kyle
Nov 9 '15 at 17:48
add a comment |
I have a long list of several thousand dates and times of people paying for a bill at a restaurant.
From this list I would like to try to understand:
What are the peak hours (eg: do most people pay for the bill from 6-7PM)
And what are the peak days of the week (eg: most people pay for their bill on Wednesdays around 7PM)
Ideally I am looking for:
A way to visualize all the data over a 1 week period to see frequency over time
A way to visualize all the data over 1 day to see frequency over time
What tools / formulas could I use to answer my questions which are mainly: What time(s) is the restaurant at its busiest and what day(s) of the week is the restaurant at its busiest.
microsoft-excel
asked Nov 9 '15 at 15:11
jmasterxjmasterx
3302615
I have a long list of several thousand dates and times of people paying for a bill at a restaurant.
From this list I would like to try to understand:
What are the peak hours (eg: do most people pay for the bill from 6-7PM)
And what are the peak days of the week (eg: most people pay for their bill on Wednesdays around 7PM)
Ideally I am looking for:
A way to visualize all the data over a 1 week period to see frequency over time
A way to visualize all the data over 1 day to see frequency over time
What tools / formulas could I use to answer my questions which are mainly: What time(s) is the restaurant at its busiest and what day(s) of the week is the restaurant at its busiest.
microsoft-excel
microsoft-excel
asked Nov 9 '15 at 15:11
jmasterxjmasterx
3302615
asked Nov 9 '15 at 15:11
jmasterxjmasterx
3302615
asked Nov 9 '15 at 15:11
jmasterxjmasterx
3302615
asked Nov 9 '15 at 15:11
jmasterxjmasterx
3302615
asked Nov 9 '15 at 15:11
jmasterxjmasterx
3302615
3302615
This question is a bit open-ended. What have you tried already?
– Kyle
Nov 9 '15 at 17:48
add a comment |
This question is a bit open-ended. What have you tried already?
– Kyle
Nov 9 '15 at 17:48
This question is a bit open-ended. What have you tried already?
– Kyle
Nov 9 '15 at 17:48
This question is a bit open-ended. What have you tried already?
– Kyle
Nov 9 '15 at 17:48
add a comment |
2 Answers
2
active
oldest
votes
You need to add a new column for each zoom level you want to show, e.g.:
- for day level chart formula to get day:
=WEEKDAY([@date],2), where2means that week start at Monday, and it's represented by number 1 (with some more complicated formula you can even show day names) - for hourly chart formula is
=HOUR([@date])
For preparing the chart the easiest way is a pivot chart:
- select Insert - PivotTable - PivotChart
- move day (or hour) to axis fields
- move date to values (make sure count of dates is calculated, and not average or sum)
(my data is just 150 random dates, your real data will probably look better)
answered Nov 10 '15 at 8:01
Máté JuhászMáté Juhász
14.4k63352
add a comment |
You might have to massage your data a little bit: add a column to pull the day's name (e.g. with formula =text(A1,"ddd"), then use a pivot table, put your dates / times in the row field, put them also in the data field as a Count, then group the dates in the row field to get the data you want.
You can also build a pivot chart using the same principle.
answered Nov 10 '15 at 0:19
nutschnutsch
1,843914
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%2f998175%2fgathering-data-from-a-list-of-dates%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You need to add a new column for each zoom level you want to show, e.g.:
- for day level chart formula to get day:
=WEEKDAY([@date],2), where2means that week start at Monday, and it's represented by number 1 (with some more complicated formula you can even show day names) - for hourly chart formula is
=HOUR([@date])
For preparing the chart the easiest way is a pivot chart:
- select Insert - PivotTable - PivotChart
- move day (or hour) to axis fields
- move date to values (make sure count of dates is calculated, and not average or sum)
(my data is just 150 random dates, your real data will probably look better)
answered Nov 10 '15 at 8:01
Máté JuhászMáté Juhász
14.4k63352
add a comment |
You need to add a new column for each zoom level you want to show, e.g.:
- for day level chart formula to get day:
=WEEKDAY([@date],2), where2means that week start at Monday, and it's represented by number 1 (with some more complicated formula you can even show day names) - for hourly chart formula is
=HOUR([@date])
For preparing the chart the easiest way is a pivot chart:
- select Insert - PivotTable - PivotChart
- move day (or hour) to axis fields
- move date to values (make sure count of dates is calculated, and not average or sum)
(my data is just 150 random dates, your real data will probably look better)
answered Nov 10 '15 at 8:01
Máté JuhászMáté Juhász
14.4k63352
add a comment |
You need to add a new column for each zoom level you want to show, e.g.:
- for day level chart formula to get day:
=WEEKDAY([@date],2), where2means that week start at Monday, and it's represented by number 1 (with some more complicated formula you can even show day names) - for hourly chart formula is
=HOUR([@date])
For preparing the chart the easiest way is a pivot chart:
- select Insert - PivotTable - PivotChart
- move day (or hour) to axis fields
- move date to values (make sure count of dates is calculated, and not average or sum)
(my data is just 150 random dates, your real data will probably look better)
answered Nov 10 '15 at 8:01
Máté JuhászMáté Juhász
14.4k63352
You need to add a new column for each zoom level you want to show, e.g.:
- for day level chart formula to get day:
=WEEKDAY([@date],2), where2means that week start at Monday, and it's represented by number 1 (with some more complicated formula you can even show day names) - for hourly chart formula is
=HOUR([@date])
For preparing the chart the easiest way is a pivot chart:
- select Insert - PivotTable - PivotChart
- move day (or hour) to axis fields
- move date to values (make sure count of dates is calculated, and not average or sum)
(my data is just 150 random dates, your real data will probably look better)
answered Nov 10 '15 at 8:01
Máté JuhászMáté Juhász
14.4k63352
answered Nov 10 '15 at 8:01
Máté JuhászMáté Juhász
14.4k63352
answered Nov 10 '15 at 8:01
Máté JuhászMáté Juhász
14.4k63352
answered Nov 10 '15 at 8:01
Máté JuhászMáté Juhász
14.4k63352
14.4k63352
add a comment |
add a comment |
You might have to massage your data a little bit: add a column to pull the day's name (e.g. with formula =text(A1,"ddd"), then use a pivot table, put your dates / times in the row field, put them also in the data field as a Count, then group the dates in the row field to get the data you want.
You can also build a pivot chart using the same principle.
answered Nov 10 '15 at 0:19
nutschnutsch
1,843914
add a comment |
You might have to massage your data a little bit: add a column to pull the day's name (e.g. with formula =text(A1,"ddd"), then use a pivot table, put your dates / times in the row field, put them also in the data field as a Count, then group the dates in the row field to get the data you want.
You can also build a pivot chart using the same principle.
answered Nov 10 '15 at 0:19
nutschnutsch
1,843914
add a comment |
You might have to massage your data a little bit: add a column to pull the day's name (e.g. with formula =text(A1,"ddd"), then use a pivot table, put your dates / times in the row field, put them also in the data field as a Count, then group the dates in the row field to get the data you want.
You can also build a pivot chart using the same principle.
answered Nov 10 '15 at 0:19
nutschnutsch
1,843914
You might have to massage your data a little bit: add a column to pull the day's name (e.g. with formula =text(A1,"ddd"), then use a pivot table, put your dates / times in the row field, put them also in the data field as a Count, then group the dates in the row field to get the data you want.
You can also build a pivot chart using the same principle.
answered Nov 10 '15 at 0:19
nutschnutsch
1,843914
edited Nov 10 '15 at 0:24
answered Nov 10 '15 at 0:19
nutschnutsch
1,843914
answered Nov 10 '15 at 0:19
nutschnutsch
1,843914
answered Nov 10 '15 at 0:19
nutschnutsch
1,843914
1,843914
add a comment |
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%2f998175%2fgathering-data-from-a-list-of-dates%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
This question is a bit open-ended. What have you tried already?
– Kyle
Nov 9 '15 at 17:48