Google sheets query returning incorrect date
I have a got a query where I'm trying to pull in the next 14 days events and previous 14 days events
For some reason, I'm getting very dates added which are in the past or way into the future
=QUERY(Sheet1!A2:H200,"select A,B,G where dateDiff(now(), G) <14 and G is not null")
ABC 1 15 Feb 2019
ABC 1 1 Nov 2018
DFG 1 11 Nov 2018
ABC 1 2 Nov 2018
Next is the previous 14 days
=QUERY(Sheet1!A:G,"select A,B,G where dateDiff(now(), G) >14 and G is not null")
ABC 1 20 Oct 2018
ABC 1 20 Oct 2018
I'm doing something wrong with the query
https://docs.google.com/spreadsheets/d/1WI-FS0XFGi09d2wO005S3kOV_L2s9eR3ILxST6I1nVU/edit?usp=sharing
google-sheets google-query-language
asked Nov 12 at 10:54
Grimlockz
1,03741931
add a comment |
I have a got a query where I'm trying to pull in the next 14 days events and previous 14 days events
For some reason, I'm getting very dates added which are in the past or way into the future
=QUERY(Sheet1!A2:H200,"select A,B,G where dateDiff(now(), G) <14 and G is not null")
ABC 1 15 Feb 2019
ABC 1 1 Nov 2018
DFG 1 11 Nov 2018
ABC 1 2 Nov 2018
Next is the previous 14 days
=QUERY(Sheet1!A:G,"select A,B,G where dateDiff(now(), G) >14 and G is not null")
ABC 1 20 Oct 2018
ABC 1 20 Oct 2018
I'm doing something wrong with the query
https://docs.google.com/spreadsheets/d/1WI-FS0XFGi09d2wO005S3kOV_L2s9eR3ILxST6I1nVU/edit?usp=sharing
google-sheets google-query-language
asked Nov 12 at 10:54
Grimlockz
1,03741931
share your sheet
– TheMaster
Nov 12 at 16:04
Updated with link to the sheet
– Grimlockz
Nov 12 at 18:36
add a comment |
I have a got a query where I'm trying to pull in the next 14 days events and previous 14 days events
For some reason, I'm getting very dates added which are in the past or way into the future
=QUERY(Sheet1!A2:H200,"select A,B,G where dateDiff(now(), G) <14 and G is not null")
ABC 1 15 Feb 2019
ABC 1 1 Nov 2018
DFG 1 11 Nov 2018
ABC 1 2 Nov 2018
Next is the previous 14 days
=QUERY(Sheet1!A:G,"select A,B,G where dateDiff(now(), G) >14 and G is not null")
ABC 1 20 Oct 2018
ABC 1 20 Oct 2018
I'm doing something wrong with the query
https://docs.google.com/spreadsheets/d/1WI-FS0XFGi09d2wO005S3kOV_L2s9eR3ILxST6I1nVU/edit?usp=sharing
google-sheets google-query-language
asked Nov 12 at 10:54
Grimlockz
1,03741931
I have a got a query where I'm trying to pull in the next 14 days events and previous 14 days events
For some reason, I'm getting very dates added which are in the past or way into the future
=QUERY(Sheet1!A2:H200,"select A,B,G where dateDiff(now(), G) <14 and G is not null")
ABC 1 15 Feb 2019
ABC 1 1 Nov 2018
DFG 1 11 Nov 2018
ABC 1 2 Nov 2018
Next is the previous 14 days
=QUERY(Sheet1!A:G,"select A,B,G where dateDiff(now(), G) >14 and G is not null")
ABC 1 20 Oct 2018
ABC 1 20 Oct 2018
I'm doing something wrong with the query
https://docs.google.com/spreadsheets/d/1WI-FS0XFGi09d2wO005S3kOV_L2s9eR3ILxST6I1nVU/edit?usp=sharing
google-sheets google-query-language
google-sheets google-query-language
asked Nov 12 at 10:54
Grimlockz
1,03741931
asked Nov 12 at 10:54
Grimlockz
1,03741931
edited Nov 12 at 18:36
asked Nov 12 at 10:54
Grimlockz
1,03741931
asked Nov 12 at 10:54
Grimlockz
1,03741931
asked Nov 12 at 10:54
Grimlockz
1,03741931
1,03741931
share your sheet
– TheMaster
Nov 12 at 16:04
Updated with link to the sheet
– Grimlockz
Nov 12 at 18:36
add a comment |
share your sheet
– TheMaster
Nov 12 at 16:04
Updated with link to the sheet
– Grimlockz
Nov 12 at 18:36
share your sheet
– TheMaster
Nov 12 at 16:04
share your sheet
– TheMaster
Nov 12 at 16:04
Updated with link to the sheet
– Grimlockz
Nov 12 at 18:36
Updated with link to the sheet
– Grimlockz
Nov 12 at 18:36
add a comment |
2 Answers
2
active
oldest
votes
If you wanted to do it all using datediff, it would be
=query(A:C,"select A,B,C where datediff(C,now())<14 and datediff(C,now())>0")
for dates in next fortnight (fourteen nights or 2 weeks) and
=query(A:C,"select A,B,C where datediff(now(),C)<14 and datediff(now(),C)>0")
for previous fortnight.
You might want to put <= and >=, depending whether you want to include today's date and date 14 days before/after today.
answered Nov 12 at 22:32
Tom Sharpe
12k31224
1
Could you clarify the length of time a fortnight is (just for those of us who don't use the term and for future viewers)?
– connectyourcharger
Nov 12 at 22:36
Thanks, that works great but <= and => doesn't seem to work
– Grimlockz
Nov 13 at 7:40
Thanks - <= and >= seem to work for me, if you can give me an example that doesn't work I'll investigate further.
– Tom Sharpe
Nov 13 at 12:13
add a comment |
When you use less than 14, Future dates are also included because datediff returns a negative number. So, add a another condition to exclude future dates like:
=QUERY(A:C,"select A,B,C where dateDiff(now(), C) <14 and C<now() and C is not null")
answered Nov 12 at 19:02
TheMaster
9,3943731
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53260636%2fgoogle-sheets-query-returning-incorrect-date%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
If you wanted to do it all using datediff, it would be
=query(A:C,"select A,B,C where datediff(C,now())<14 and datediff(C,now())>0")
for dates in next fortnight (fourteen nights or 2 weeks) and
=query(A:C,"select A,B,C where datediff(now(),C)<14 and datediff(now(),C)>0")
for previous fortnight.
You might want to put <= and >=, depending whether you want to include today's date and date 14 days before/after today.
answered Nov 12 at 22:32
Tom Sharpe
12k31224
1
Could you clarify the length of time a fortnight is (just for those of us who don't use the term and for future viewers)?
– connectyourcharger
Nov 12 at 22:36
Thanks, that works great but <= and => doesn't seem to work
– Grimlockz
Nov 13 at 7:40
Thanks - <= and >= seem to work for me, if you can give me an example that doesn't work I'll investigate further.
– Tom Sharpe
Nov 13 at 12:13
add a comment |
If you wanted to do it all using datediff, it would be
=query(A:C,"select A,B,C where datediff(C,now())<14 and datediff(C,now())>0")
for dates in next fortnight (fourteen nights or 2 weeks) and
=query(A:C,"select A,B,C where datediff(now(),C)<14 and datediff(now(),C)>0")
for previous fortnight.
You might want to put <= and >=, depending whether you want to include today's date and date 14 days before/after today.
answered Nov 12 at 22:32
Tom Sharpe
12k31224
1
Could you clarify the length of time a fortnight is (just for those of us who don't use the term and for future viewers)?
– connectyourcharger
Nov 12 at 22:36
Thanks, that works great but <= and => doesn't seem to work
– Grimlockz
Nov 13 at 7:40
Thanks - <= and >= seem to work for me, if you can give me an example that doesn't work I'll investigate further.
– Tom Sharpe
Nov 13 at 12:13
add a comment |
If you wanted to do it all using datediff, it would be
=query(A:C,"select A,B,C where datediff(C,now())<14 and datediff(C,now())>0")
for dates in next fortnight (fourteen nights or 2 weeks) and
=query(A:C,"select A,B,C where datediff(now(),C)<14 and datediff(now(),C)>0")
for previous fortnight.
You might want to put <= and >=, depending whether you want to include today's date and date 14 days before/after today.
answered Nov 12 at 22:32
Tom Sharpe
12k31224
If you wanted to do it all using datediff, it would be
=query(A:C,"select A,B,C where datediff(C,now())<14 and datediff(C,now())>0")
for dates in next fortnight (fourteen nights or 2 weeks) and
=query(A:C,"select A,B,C where datediff(now(),C)<14 and datediff(now(),C)>0")
for previous fortnight.
You might want to put <= and >=, depending whether you want to include today's date and date 14 days before/after today.
answered Nov 12 at 22:32
Tom Sharpe
12k31224
edited Nov 13 at 11:43
answered Nov 12 at 22:32
Tom Sharpe
12k31224
answered Nov 12 at 22:32
Tom Sharpe
12k31224
answered Nov 12 at 22:32
Tom Sharpe
12k31224
12k31224
1
Could you clarify the length of time a fortnight is (just for those of us who don't use the term and for future viewers)?
– connectyourcharger
Nov 12 at 22:36
Thanks, that works great but <= and => doesn't seem to work
– Grimlockz
Nov 13 at 7:40
Thanks - <= and >= seem to work for me, if you can give me an example that doesn't work I'll investigate further.
– Tom Sharpe
Nov 13 at 12:13
add a comment |
1
Could you clarify the length of time a fortnight is (just for those of us who don't use the term and for future viewers)?
– connectyourcharger
Nov 12 at 22:36
Thanks, that works great but <= and => doesn't seem to work
– Grimlockz
Nov 13 at 7:40
Thanks - <= and >= seem to work for me, if you can give me an example that doesn't work I'll investigate further.
– Tom Sharpe
Nov 13 at 12:13
1
1
Could you clarify the length of time a fortnight is (just for those of us who don't use the term and for future viewers)?
– connectyourcharger
Nov 12 at 22:36
Could you clarify the length of time a fortnight is (just for those of us who don't use the term and for future viewers)?
– connectyourcharger
Nov 12 at 22:36
Thanks, that works great but <= and => doesn't seem to work
– Grimlockz
Nov 13 at 7:40
Thanks, that works great but <= and => doesn't seem to work
– Grimlockz
Nov 13 at 7:40
Thanks - <= and >= seem to work for me, if you can give me an example that doesn't work I'll investigate further.
– Tom Sharpe
Nov 13 at 12:13
Thanks - <= and >= seem to work for me, if you can give me an example that doesn't work I'll investigate further.
– Tom Sharpe
Nov 13 at 12:13
add a comment |
When you use less than 14, Future dates are also included because datediff returns a negative number. So, add a another condition to exclude future dates like:
=QUERY(A:C,"select A,B,C where dateDiff(now(), C) <14 and C<now() and C is not null")
answered Nov 12 at 19:02
TheMaster
9,3943731
add a comment |
When you use less than 14, Future dates are also included because datediff returns a negative number. So, add a another condition to exclude future dates like:
=QUERY(A:C,"select A,B,C where dateDiff(now(), C) <14 and C<now() and C is not null")
answered Nov 12 at 19:02
TheMaster
9,3943731
add a comment |
When you use less than 14, Future dates are also included because datediff returns a negative number. So, add a another condition to exclude future dates like:
=QUERY(A:C,"select A,B,C where dateDiff(now(), C) <14 and C<now() and C is not null")
answered Nov 12 at 19:02
TheMaster
9,3943731
When you use less than 14, Future dates are also included because datediff returns a negative number. So, add a another condition to exclude future dates like:
=QUERY(A:C,"select A,B,C where dateDiff(now(), C) <14 and C<now() and C is not null")
answered Nov 12 at 19:02
TheMaster
9,3943731
answered Nov 12 at 19:02
TheMaster
9,3943731
answered Nov 12 at 19:02
TheMaster
9,3943731
answered Nov 12 at 19:02
TheMaster
9,3943731
9,3943731
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2fstackoverflow.com%2fquestions%2f53260636%2fgoogle-sheets-query-returning-incorrect-date%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
share your sheet
– TheMaster
Nov 12 at 16:04
Updated with link to the sheet
– Grimlockz
Nov 12 at 18:36