How to put a date as a variable in a formula in VBA?
In the code, the date is 100% correct. When it goes into the form it comes out "1/0/1900".
It seems to be an issue when using a formula in VBA.
TestDate = DateAdd("m", 1, "9/1/2018")
ActiveCell = TestDate 'output: 10/1/2018
ActiveCell.Offset(1).FormulaR1C1 = "=" & TestDate & "" 'output: 1/0/1900
ActiveCell.Offset(2).Formula = "=" & TestDate & "" 'output: 1/0/1900
excel vba formula
edited Dec 18 '18 at 4:43
Community♦
11
asked Nov 15 '18 at 23:15
imthatonegirlimthatonegirl
276
add a comment |
In the code, the date is 100% correct. When it goes into the form it comes out "1/0/1900".
It seems to be an issue when using a formula in VBA.
TestDate = DateAdd("m", 1, "9/1/2018")
ActiveCell = TestDate 'output: 10/1/2018
ActiveCell.Offset(1).FormulaR1C1 = "=" & TestDate & "" 'output: 1/0/1900
ActiveCell.Offset(2).Formula = "=" & TestDate & "" 'output: 1/0/1900
excel vba formula
edited Dec 18 '18 at 4:43
Community♦
11
asked Nov 15 '18 at 23:15
imthatonegirlimthatonegirl
276
You must put the double quotes around your string; e.g. "=(""" & TestDate & """)"
– GMalc
Nov 15 '18 at 23:48
add a comment |
In the code, the date is 100% correct. When it goes into the form it comes out "1/0/1900".
It seems to be an issue when using a formula in VBA.
TestDate = DateAdd("m", 1, "9/1/2018")
ActiveCell = TestDate 'output: 10/1/2018
ActiveCell.Offset(1).FormulaR1C1 = "=" & TestDate & "" 'output: 1/0/1900
ActiveCell.Offset(2).Formula = "=" & TestDate & "" 'output: 1/0/1900
excel vba formula
edited Dec 18 '18 at 4:43
Community♦
11
asked Nov 15 '18 at 23:15
imthatonegirlimthatonegirl
276
In the code, the date is 100% correct. When it goes into the form it comes out "1/0/1900".
It seems to be an issue when using a formula in VBA.
TestDate = DateAdd("m", 1, "9/1/2018")
ActiveCell = TestDate 'output: 10/1/2018
ActiveCell.Offset(1).FormulaR1C1 = "=" & TestDate & "" 'output: 1/0/1900
ActiveCell.Offset(2).Formula = "=" & TestDate & "" 'output: 1/0/1900
excel vba formula
excel vba formula
edited Dec 18 '18 at 4:43
Community♦
11
asked Nov 15 '18 at 23:15
imthatonegirlimthatonegirl
276
edited Dec 18 '18 at 4:43
Community♦
11
asked Nov 15 '18 at 23:15
imthatonegirlimthatonegirl
276
edited Dec 18 '18 at 4:43
Community♦
11
edited Dec 18 '18 at 4:43
Community♦
11
edited Dec 18 '18 at 4:43
Community♦
11
11
asked Nov 15 '18 at 23:15
imthatonegirlimthatonegirl
276
asked Nov 15 '18 at 23:15
imthatonegirlimthatonegirl
276
asked Nov 15 '18 at 23:15
imthatonegirlimthatonegirl
276
276
You must put the double quotes around your string; e.g. "=(""" & TestDate & """)"
– GMalc
Nov 15 '18 at 23:48
add a comment |
You must put the double quotes around your string; e.g. "=(""" & TestDate & """)"
– GMalc
Nov 15 '18 at 23:48
You must put the double quotes around your string; e.g. "=(""" & TestDate & """)"
– GMalc
Nov 15 '18 at 23:48
You must put the double quotes around your string; e.g. "=(""" & TestDate & """)"
– GMalc
Nov 15 '18 at 23:48
add a comment |
3 Answers
3
active
oldest
votes
Quotation marks are often a pain in a formula. I don't know what result you're aiming for, but these versions work:
ActiveCell.Offset(1).FormulaR1C1 = "=""" & TestDate & """" 'output: ="10/1/2018"
ActiveCell.Offset(2).Formula = "=""" & TestDate & """" 'output: ="10/1/2018"
ActiveCell.Offset(3).FormulaR1C1 = TestDate 'output: 10/1/2018
ActiveCell.Offset(4).Formula = TestDate 'output: 10/1/2018
And here's the official help, Quotation marks in string expressions.
answered Nov 15 '18 at 23:31
scenographyscenography
11314
This is the best answer, it shows the OP how to fix his problem and provide a link to explain why.
– GMalc
Nov 15 '18 at 23:49
add a comment |
That's what happens when you mix dates and strings.. TestDate is stored not as a date, but as a string 9/1/2018. When you paste this string into a cell with your third line ActiveCell.Offset(1).FormulaR1C1 = "=" & TestDate & "" 'output: 1/0/1900, the output is =9/1/2018 (0.00445986124876115). Internally, day 0 is 1/1/1900. You have to convert your TestDate string to a date.
answered Nov 15 '18 at 23:30
Michal RosaMichal Rosa
1,3361815
Thank you for the response! I created a declaration and set it to a date, however, the issue continued. The other post pointed out I was missing the third set of quotation marks... that solved it.
– imthatonegirl
Nov 16 '18 at 0:09
add a comment |
Ah - understood...the only way I can get it to work is to do the following. Hope this will work for you!
Public Sub Test()
Dim formula As String
ThisWorkbook.ActiveSheet.Cells(1, 1).Select
TestDate = DateAdd("m", 1, "9/1/2018")
ActiveCell = TestDate
formula = "=Date(" + CStr(Year(TestDate)) + "," + CStr(Month(TestDate)) + "," + CStr(Day(TestDate)) + ")"
ActiveCell.Offset(1).FormulaR1C1 = formula
ActiveCell.Offset(2).formula = formula
End Sub
answered Nov 15 '18 at 23:35
Jon VoteJon Vote
38710
Thank you for responding! It requires the "=" to make it a formula, it doesn't matter in this example but in the larger equation it does.
– imthatonegirl
Nov 16 '18 at 0:11
You're welcome! I just updated the post - see if this will work for you.
– Jon Vote
Nov 16 '18 at 22:19
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%2f53329199%2fhow-to-put-a-date-as-a-variable-in-a-formula-in-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Quotation marks are often a pain in a formula. I don't know what result you're aiming for, but these versions work:
ActiveCell.Offset(1).FormulaR1C1 = "=""" & TestDate & """" 'output: ="10/1/2018"
ActiveCell.Offset(2).Formula = "=""" & TestDate & """" 'output: ="10/1/2018"
ActiveCell.Offset(3).FormulaR1C1 = TestDate 'output: 10/1/2018
ActiveCell.Offset(4).Formula = TestDate 'output: 10/1/2018
And here's the official help, Quotation marks in string expressions.
answered Nov 15 '18 at 23:31
scenographyscenography
11314
This is the best answer, it shows the OP how to fix his problem and provide a link to explain why.
– GMalc
Nov 15 '18 at 23:49
add a comment |
Quotation marks are often a pain in a formula. I don't know what result you're aiming for, but these versions work:
ActiveCell.Offset(1).FormulaR1C1 = "=""" & TestDate & """" 'output: ="10/1/2018"
ActiveCell.Offset(2).Formula = "=""" & TestDate & """" 'output: ="10/1/2018"
ActiveCell.Offset(3).FormulaR1C1 = TestDate 'output: 10/1/2018
ActiveCell.Offset(4).Formula = TestDate 'output: 10/1/2018
And here's the official help, Quotation marks in string expressions.
answered Nov 15 '18 at 23:31
scenographyscenography
11314
This is the best answer, it shows the OP how to fix his problem and provide a link to explain why.
– GMalc
Nov 15 '18 at 23:49
add a comment |
Quotation marks are often a pain in a formula. I don't know what result you're aiming for, but these versions work:
ActiveCell.Offset(1).FormulaR1C1 = "=""" & TestDate & """" 'output: ="10/1/2018"
ActiveCell.Offset(2).Formula = "=""" & TestDate & """" 'output: ="10/1/2018"
ActiveCell.Offset(3).FormulaR1C1 = TestDate 'output: 10/1/2018
ActiveCell.Offset(4).Formula = TestDate 'output: 10/1/2018
And here's the official help, Quotation marks in string expressions.
answered Nov 15 '18 at 23:31
scenographyscenography
11314
Quotation marks are often a pain in a formula. I don't know what result you're aiming for, but these versions work:
ActiveCell.Offset(1).FormulaR1C1 = "=""" & TestDate & """" 'output: ="10/1/2018"
ActiveCell.Offset(2).Formula = "=""" & TestDate & """" 'output: ="10/1/2018"
ActiveCell.Offset(3).FormulaR1C1 = TestDate 'output: 10/1/2018
ActiveCell.Offset(4).Formula = TestDate 'output: 10/1/2018
And here's the official help, Quotation marks in string expressions.
answered Nov 15 '18 at 23:31
scenographyscenography
11314
answered Nov 15 '18 at 23:31
scenographyscenography
11314
answered Nov 15 '18 at 23:31
scenographyscenography
11314
answered Nov 15 '18 at 23:31
scenographyscenography
11314
11314
This is the best answer, it shows the OP how to fix his problem and provide a link to explain why.
– GMalc
Nov 15 '18 at 23:49
add a comment |
This is the best answer, it shows the OP how to fix his problem and provide a link to explain why.
– GMalc
Nov 15 '18 at 23:49
This is the best answer, it shows the OP how to fix his problem and provide a link to explain why.
– GMalc
Nov 15 '18 at 23:49
This is the best answer, it shows the OP how to fix his problem and provide a link to explain why.
– GMalc
Nov 15 '18 at 23:49
add a comment |
That's what happens when you mix dates and strings.. TestDate is stored not as a date, but as a string 9/1/2018. When you paste this string into a cell with your third line ActiveCell.Offset(1).FormulaR1C1 = "=" & TestDate & "" 'output: 1/0/1900, the output is =9/1/2018 (0.00445986124876115). Internally, day 0 is 1/1/1900. You have to convert your TestDate string to a date.
answered Nov 15 '18 at 23:30
Michal RosaMichal Rosa
1,3361815
Thank you for the response! I created a declaration and set it to a date, however, the issue continued. The other post pointed out I was missing the third set of quotation marks... that solved it.
– imthatonegirl
Nov 16 '18 at 0:09
add a comment |
That's what happens when you mix dates and strings.. TestDate is stored not as a date, but as a string 9/1/2018. When you paste this string into a cell with your third line ActiveCell.Offset(1).FormulaR1C1 = "=" & TestDate & "" 'output: 1/0/1900, the output is =9/1/2018 (0.00445986124876115). Internally, day 0 is 1/1/1900. You have to convert your TestDate string to a date.
answered Nov 15 '18 at 23:30
Michal RosaMichal Rosa
1,3361815
Thank you for the response! I created a declaration and set it to a date, however, the issue continued. The other post pointed out I was missing the third set of quotation marks... that solved it.
– imthatonegirl
Nov 16 '18 at 0:09
add a comment |
That's what happens when you mix dates and strings.. TestDate is stored not as a date, but as a string 9/1/2018. When you paste this string into a cell with your third line ActiveCell.Offset(1).FormulaR1C1 = "=" & TestDate & "" 'output: 1/0/1900, the output is =9/1/2018 (0.00445986124876115). Internally, day 0 is 1/1/1900. You have to convert your TestDate string to a date.
answered Nov 15 '18 at 23:30
Michal RosaMichal Rosa
1,3361815
That's what happens when you mix dates and strings.. TestDate is stored not as a date, but as a string 9/1/2018. When you paste this string into a cell with your third line ActiveCell.Offset(1).FormulaR1C1 = "=" & TestDate & "" 'output: 1/0/1900, the output is =9/1/2018 (0.00445986124876115). Internally, day 0 is 1/1/1900. You have to convert your TestDate string to a date.
answered Nov 15 '18 at 23:30
Michal RosaMichal Rosa
1,3361815
answered Nov 15 '18 at 23:30
Michal RosaMichal Rosa
1,3361815
answered Nov 15 '18 at 23:30
Michal RosaMichal Rosa
1,3361815
answered Nov 15 '18 at 23:30
Michal RosaMichal Rosa
1,3361815
1,3361815
Thank you for the response! I created a declaration and set it to a date, however, the issue continued. The other post pointed out I was missing the third set of quotation marks... that solved it.
– imthatonegirl
Nov 16 '18 at 0:09
add a comment |
Thank you for the response! I created a declaration and set it to a date, however, the issue continued. The other post pointed out I was missing the third set of quotation marks... that solved it.
– imthatonegirl
Nov 16 '18 at 0:09
Thank you for the response! I created a declaration and set it to a date, however, the issue continued. The other post pointed out I was missing the third set of quotation marks... that solved it.
– imthatonegirl
Nov 16 '18 at 0:09
Thank you for the response! I created a declaration and set it to a date, however, the issue continued. The other post pointed out I was missing the third set of quotation marks... that solved it.
– imthatonegirl
Nov 16 '18 at 0:09
add a comment |
Ah - understood...the only way I can get it to work is to do the following. Hope this will work for you!
Public Sub Test()
Dim formula As String
ThisWorkbook.ActiveSheet.Cells(1, 1).Select
TestDate = DateAdd("m", 1, "9/1/2018")
ActiveCell = TestDate
formula = "=Date(" + CStr(Year(TestDate)) + "," + CStr(Month(TestDate)) + "," + CStr(Day(TestDate)) + ")"
ActiveCell.Offset(1).FormulaR1C1 = formula
ActiveCell.Offset(2).formula = formula
End Sub
answered Nov 15 '18 at 23:35
Jon VoteJon Vote
38710
Thank you for responding! It requires the "=" to make it a formula, it doesn't matter in this example but in the larger equation it does.
– imthatonegirl
Nov 16 '18 at 0:11
You're welcome! I just updated the post - see if this will work for you.
– Jon Vote
Nov 16 '18 at 22:19
add a comment |
Ah - understood...the only way I can get it to work is to do the following. Hope this will work for you!
Public Sub Test()
Dim formula As String
ThisWorkbook.ActiveSheet.Cells(1, 1).Select
TestDate = DateAdd("m", 1, "9/1/2018")
ActiveCell = TestDate
formula = "=Date(" + CStr(Year(TestDate)) + "," + CStr(Month(TestDate)) + "," + CStr(Day(TestDate)) + ")"
ActiveCell.Offset(1).FormulaR1C1 = formula
ActiveCell.Offset(2).formula = formula
End Sub
answered Nov 15 '18 at 23:35
Jon VoteJon Vote
38710
Thank you for responding! It requires the "=" to make it a formula, it doesn't matter in this example but in the larger equation it does.
– imthatonegirl
Nov 16 '18 at 0:11
You're welcome! I just updated the post - see if this will work for you.
– Jon Vote
Nov 16 '18 at 22:19
add a comment |
Ah - understood...the only way I can get it to work is to do the following. Hope this will work for you!
Public Sub Test()
Dim formula As String
ThisWorkbook.ActiveSheet.Cells(1, 1).Select
TestDate = DateAdd("m", 1, "9/1/2018")
ActiveCell = TestDate
formula = "=Date(" + CStr(Year(TestDate)) + "," + CStr(Month(TestDate)) + "," + CStr(Day(TestDate)) + ")"
ActiveCell.Offset(1).FormulaR1C1 = formula
ActiveCell.Offset(2).formula = formula
End Sub
answered Nov 15 '18 at 23:35
Jon VoteJon Vote
38710
Ah - understood...the only way I can get it to work is to do the following. Hope this will work for you!
Public Sub Test()
Dim formula As String
ThisWorkbook.ActiveSheet.Cells(1, 1).Select
TestDate = DateAdd("m", 1, "9/1/2018")
ActiveCell = TestDate
formula = "=Date(" + CStr(Year(TestDate)) + "," + CStr(Month(TestDate)) + "," + CStr(Day(TestDate)) + ")"
ActiveCell.Offset(1).FormulaR1C1 = formula
ActiveCell.Offset(2).formula = formula
End Sub
answered Nov 15 '18 at 23:35
Jon VoteJon Vote
38710
edited Nov 16 '18 at 22:18
answered Nov 15 '18 at 23:35
Jon VoteJon Vote
38710
answered Nov 15 '18 at 23:35
Jon VoteJon Vote
38710
answered Nov 15 '18 at 23:35
Jon VoteJon Vote
38710
38710
Thank you for responding! It requires the "=" to make it a formula, it doesn't matter in this example but in the larger equation it does.
– imthatonegirl
Nov 16 '18 at 0:11
You're welcome! I just updated the post - see if this will work for you.
– Jon Vote
Nov 16 '18 at 22:19
add a comment |
Thank you for responding! It requires the "=" to make it a formula, it doesn't matter in this example but in the larger equation it does.
– imthatonegirl
Nov 16 '18 at 0:11
You're welcome! I just updated the post - see if this will work for you.
– Jon Vote
Nov 16 '18 at 22:19
Thank you for responding! It requires the "=" to make it a formula, it doesn't matter in this example but in the larger equation it does.
– imthatonegirl
Nov 16 '18 at 0:11
Thank you for responding! It requires the "=" to make it a formula, it doesn't matter in this example but in the larger equation it does.
– imthatonegirl
Nov 16 '18 at 0:11
You're welcome! I just updated the post - see if this will work for you.
– Jon Vote
Nov 16 '18 at 22:19
You're welcome! I just updated the post - see if this will work for you.
– Jon Vote
Nov 16 '18 at 22:19
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.
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%2f53329199%2fhow-to-put-a-date-as-a-variable-in-a-formula-in-vba%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
You must put the double quotes around your string; e.g. "=(""" & TestDate & """)"
– GMalc
Nov 15 '18 at 23:48