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 '18 at 10:54
GrimlockzGrimlockz
1,03341931
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 '18 at 10:54
GrimlockzGrimlockz
1,03341931
share your sheet
– TheMaster
Nov 12 '18 at 16:04
Updated with link to the sheet
– Grimlockz
Nov 12 '18 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 '18 at 10:54
GrimlockzGrimlockz
1,03341931
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 '18 at 10:54
GrimlockzGrimlockz
1,03341931
asked Nov 12 '18 at 10:54
GrimlockzGrimlockz
1,03341931
edited Nov 12 '18 at 18:36
Grimlockz
asked Nov 12 '18 at 10:54
GrimlockzGrimlockz
1,03341931
asked Nov 12 '18 at 10:54
GrimlockzGrimlockz
1,03341931
asked Nov 12 '18 at 10:54
GrimlockzGrimlockz
1,03341931
1,03341931
share your sheet
– TheMaster
Nov 12 '18 at 16:04
Updated with link to the sheet
– Grimlockz
Nov 12 '18 at 18:36
add a comment |
share your sheet
– TheMaster
Nov 12 '18 at 16:04
Updated with link to the sheet
– Grimlockz
Nov 12 '18 at 18:36
share your sheet
– TheMaster
Nov 12 '18 at 16:04
share your sheet
– TheMaster
Nov 12 '18 at 16:04
Updated with link to the sheet
– Grimlockz
Nov 12 '18 at 18:36
Updated with link to the sheet
– Grimlockz
Nov 12 '18 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 '18 at 22:32
Tom SharpeTom Sharpe
12.2k31224
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 '18 at 22:36
Thanks, that works great but <= and => doesn't seem to work
– Grimlockz
Nov 13 '18 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 '18 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 '18 at 19:02
TheMasterTheMaster
9,5403731
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 '18 at 22:32
Tom SharpeTom Sharpe
12.2k31224
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 '18 at 22:36
Thanks, that works great but <= and => doesn't seem to work
– Grimlockz
Nov 13 '18 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 '18 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 '18 at 22:32
Tom SharpeTom Sharpe
12.2k31224
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 '18 at 22:36
Thanks, that works great but <= and => doesn't seem to work
– Grimlockz
Nov 13 '18 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 '18 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 '18 at 22:32
Tom SharpeTom Sharpe
12.2k31224
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 '18 at 22:32
Tom SharpeTom Sharpe
12.2k31224
edited Nov 13 '18 at 11:43
answered Nov 12 '18 at 22:32
Tom SharpeTom Sharpe
12.2k31224
answered Nov 12 '18 at 22:32
Tom SharpeTom Sharpe
12.2k31224
answered Nov 12 '18 at 22:32
Tom SharpeTom Sharpe
12.2k31224
12.2k31224
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 '18 at 22:36
Thanks, that works great but <= and => doesn't seem to work
– Grimlockz
Nov 13 '18 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 '18 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 '18 at 22:36
Thanks, that works great but <= and => doesn't seem to work
– Grimlockz
Nov 13 '18 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 '18 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 '18 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 '18 at 22:36
Thanks, that works great but <= and => doesn't seem to work
– Grimlockz
Nov 13 '18 at 7:40
Thanks, that works great but <= and => doesn't seem to work
– Grimlockz
Nov 13 '18 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 '18 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 '18 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 '18 at 19:02
TheMasterTheMaster
9,5403731
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 '18 at 19:02
TheMasterTheMaster
9,5403731
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 '18 at 19:02
TheMasterTheMaster
9,5403731
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 '18 at 19:02
TheMasterTheMaster
9,5403731
answered Nov 12 '18 at 19:02
TheMasterTheMaster
9,5403731
answered Nov 12 '18 at 19:02
TheMasterTheMaster
9,5403731
answered Nov 12 '18 at 19:02
TheMasterTheMaster
9,5403731
9,5403731
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 '18 at 16:04
Updated with link to the sheet
– Grimlockz
Nov 12 '18 at 18:36