When using a macro in Excel I get different results than when I do the tasks manually in the same sequence....
I have data from an external source imported into an excel sheet that contains date/time values but in a non standard format eg "25/02/2019 09:35AM"
Please note I am in UK so my default date format is dd-mm-yy not mm-dd-yy
When the data is imported into excel it is by default general format. To get it into usable date/time format I discovered that if I insert a space before the "AM" then the cell format gets changed to "custom" which in actual fact is a custom date time format which works exactly how I need it.
If I do this manually ie just edit the cell and insert a space it works as described above. If I do a search & replace (since I may have a list of 30 such cells with data in this format) and search for "AM" and replace with " AM" then it also works. (I have to do a second search and replace for "PM" of course)
However when I made a macro (with macro recorder) doing just the above described search and replace, the format for the cells gets changed to mm-dd-yy instead of dd-mm-yy and as you can imagine this destroys all my data.
I cannot figure out why this is and would be grateful for any help
Thanks
microsoft-excel macros
asked Feb 16 at 8:15
Alan Choo-KangAlan Choo-Kang
1
add a comment |
I have data from an external source imported into an excel sheet that contains date/time values but in a non standard format eg "25/02/2019 09:35AM"
Please note I am in UK so my default date format is dd-mm-yy not mm-dd-yy
When the data is imported into excel it is by default general format. To get it into usable date/time format I discovered that if I insert a space before the "AM" then the cell format gets changed to "custom" which in actual fact is a custom date time format which works exactly how I need it.
If I do this manually ie just edit the cell and insert a space it works as described above. If I do a search & replace (since I may have a list of 30 such cells with data in this format) and search for "AM" and replace with " AM" then it also works. (I have to do a second search and replace for "PM" of course)
However when I made a macro (with macro recorder) doing just the above described search and replace, the format for the cells gets changed to mm-dd-yy instead of dd-mm-yy and as you can imagine this destroys all my data.
I cannot figure out why this is and would be grateful for any help
Thanks
microsoft-excel macros
asked Feb 16 at 8:15
Alan Choo-KangAlan Choo-Kang
1
You'll need to do the conversion BEFORE the data is actually written to the worksheet. You can do it as part of the import routine, using either VBA or Power Query. Split the parts; create a date; then put the parts back together.
– Ron Rosenfeld
Feb 17 at 2:29
add a comment |
I have data from an external source imported into an excel sheet that contains date/time values but in a non standard format eg "25/02/2019 09:35AM"
Please note I am in UK so my default date format is dd-mm-yy not mm-dd-yy
When the data is imported into excel it is by default general format. To get it into usable date/time format I discovered that if I insert a space before the "AM" then the cell format gets changed to "custom" which in actual fact is a custom date time format which works exactly how I need it.
If I do this manually ie just edit the cell and insert a space it works as described above. If I do a search & replace (since I may have a list of 30 such cells with data in this format) and search for "AM" and replace with " AM" then it also works. (I have to do a second search and replace for "PM" of course)
However when I made a macro (with macro recorder) doing just the above described search and replace, the format for the cells gets changed to mm-dd-yy instead of dd-mm-yy and as you can imagine this destroys all my data.
I cannot figure out why this is and would be grateful for any help
Thanks
microsoft-excel macros
asked Feb 16 at 8:15
Alan Choo-KangAlan Choo-Kang
1
I have data from an external source imported into an excel sheet that contains date/time values but in a non standard format eg "25/02/2019 09:35AM"
Please note I am in UK so my default date format is dd-mm-yy not mm-dd-yy
When the data is imported into excel it is by default general format. To get it into usable date/time format I discovered that if I insert a space before the "AM" then the cell format gets changed to "custom" which in actual fact is a custom date time format which works exactly how I need it.
If I do this manually ie just edit the cell and insert a space it works as described above. If I do a search & replace (since I may have a list of 30 such cells with data in this format) and search for "AM" and replace with " AM" then it also works. (I have to do a second search and replace for "PM" of course)
However when I made a macro (with macro recorder) doing just the above described search and replace, the format for the cells gets changed to mm-dd-yy instead of dd-mm-yy and as you can imagine this destroys all my data.
I cannot figure out why this is and would be grateful for any help
Thanks
microsoft-excel macros
microsoft-excel macros
asked Feb 16 at 8:15
Alan Choo-KangAlan Choo-Kang
1
asked Feb 16 at 8:15
Alan Choo-KangAlan Choo-Kang
1
asked Feb 16 at 8:15
Alan Choo-KangAlan Choo-Kang
1
asked Feb 16 at 8:15
Alan Choo-KangAlan Choo-Kang
1
asked Feb 16 at 8:15
Alan Choo-KangAlan Choo-Kang
1
1
You'll need to do the conversion BEFORE the data is actually written to the worksheet. You can do it as part of the import routine, using either VBA or Power Query. Split the parts; create a date; then put the parts back together.
– Ron Rosenfeld
Feb 17 at 2:29
add a comment |
You'll need to do the conversion BEFORE the data is actually written to the worksheet. You can do it as part of the import routine, using either VBA or Power Query. Split the parts; create a date; then put the parts back together.
– Ron Rosenfeld
Feb 17 at 2:29
You'll need to do the conversion BEFORE the data is actually written to the worksheet. You can do it as part of the import routine, using either VBA or Power Query. Split the parts; create a date; then put the parts back together.
– Ron Rosenfeld
Feb 17 at 2:29
You'll need to do the conversion BEFORE the data is actually written to the worksheet. You can do it as part of the import routine, using either VBA or Power Query. Split the parts; create a date; then put the parts back together.
– Ron Rosenfeld
Feb 17 at 2:29
add a comment |
1 Answer
1
active
oldest
votes
You should post your code. Here is a really simple approach that takes the text in a cell, converts it into a real Excel Date/Time and stores the result to the adjacent cell:
Sub Konverter()
Dim stuff As String
Dim tPart As String, dPart As String
Dim t As Date, d As Date
stuff = ActiveCell.Text
arr = Split(stuff, " ")
dPart = arr(0)
tPart = arr(1)
tPart = Replace(Replace(tPart, "A", " A"), "P", " P")
t = TimeValue(tPart)
arr2 = Split(arr(0), "/")
d = DateSerial(arr2(2), arr2(1), arr2(0))
With ActiveCell.Offset(0, 1)
.NumberFormat = "dd/mm/yyyy hh:mm"
.Value = d + t
End With
End Sub
You will need to take something like this and put it in a loop or make a user defined function out of it.
answered Feb 17 at 13:31
Gary's StudentGary's Student
14.2k31733
add a comment |
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%2f1406398%2fwhen-using-a-macro-in-excel-i-get-different-results-than-when-i-do-the-tasks-man%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
You should post your code. Here is a really simple approach that takes the text in a cell, converts it into a real Excel Date/Time and stores the result to the adjacent cell:
Sub Konverter()
Dim stuff As String
Dim tPart As String, dPart As String
Dim t As Date, d As Date
stuff = ActiveCell.Text
arr = Split(stuff, " ")
dPart = arr(0)
tPart = arr(1)
tPart = Replace(Replace(tPart, "A", " A"), "P", " P")
t = TimeValue(tPart)
arr2 = Split(arr(0), "/")
d = DateSerial(arr2(2), arr2(1), arr2(0))
With ActiveCell.Offset(0, 1)
.NumberFormat = "dd/mm/yyyy hh:mm"
.Value = d + t
End With
End Sub
You will need to take something like this and put it in a loop or make a user defined function out of it.
answered Feb 17 at 13:31
Gary's StudentGary's Student
14.2k31733
add a comment |
You should post your code. Here is a really simple approach that takes the text in a cell, converts it into a real Excel Date/Time and stores the result to the adjacent cell:
Sub Konverter()
Dim stuff As String
Dim tPart As String, dPart As String
Dim t As Date, d As Date
stuff = ActiveCell.Text
arr = Split(stuff, " ")
dPart = arr(0)
tPart = arr(1)
tPart = Replace(Replace(tPart, "A", " A"), "P", " P")
t = TimeValue(tPart)
arr2 = Split(arr(0), "/")
d = DateSerial(arr2(2), arr2(1), arr2(0))
With ActiveCell.Offset(0, 1)
.NumberFormat = "dd/mm/yyyy hh:mm"
.Value = d + t
End With
End Sub
You will need to take something like this and put it in a loop or make a user defined function out of it.
answered Feb 17 at 13:31
Gary's StudentGary's Student
14.2k31733
add a comment |
You should post your code. Here is a really simple approach that takes the text in a cell, converts it into a real Excel Date/Time and stores the result to the adjacent cell:
Sub Konverter()
Dim stuff As String
Dim tPart As String, dPart As String
Dim t As Date, d As Date
stuff = ActiveCell.Text
arr = Split(stuff, " ")
dPart = arr(0)
tPart = arr(1)
tPart = Replace(Replace(tPart, "A", " A"), "P", " P")
t = TimeValue(tPart)
arr2 = Split(arr(0), "/")
d = DateSerial(arr2(2), arr2(1), arr2(0))
With ActiveCell.Offset(0, 1)
.NumberFormat = "dd/mm/yyyy hh:mm"
.Value = d + t
End With
End Sub
You will need to take something like this and put it in a loop or make a user defined function out of it.
answered Feb 17 at 13:31
Gary's StudentGary's Student
14.2k31733
You should post your code. Here is a really simple approach that takes the text in a cell, converts it into a real Excel Date/Time and stores the result to the adjacent cell:
Sub Konverter()
Dim stuff As String
Dim tPart As String, dPart As String
Dim t As Date, d As Date
stuff = ActiveCell.Text
arr = Split(stuff, " ")
dPart = arr(0)
tPart = arr(1)
tPart = Replace(Replace(tPart, "A", " A"), "P", " P")
t = TimeValue(tPart)
arr2 = Split(arr(0), "/")
d = DateSerial(arr2(2), arr2(1), arr2(0))
With ActiveCell.Offset(0, 1)
.NumberFormat = "dd/mm/yyyy hh:mm"
.Value = d + t
End With
End Sub
You will need to take something like this and put it in a loop or make a user defined function out of it.
answered Feb 17 at 13:31
Gary's StudentGary's Student
14.2k31733
answered Feb 17 at 13:31
Gary's StudentGary's Student
14.2k31733
answered Feb 17 at 13:31
Gary's StudentGary's Student
14.2k31733
answered Feb 17 at 13:31
Gary's StudentGary's Student
14.2k31733
14.2k31733
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%2f1406398%2fwhen-using-a-macro-in-excel-i-get-different-results-than-when-i-do-the-tasks-man%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'll need to do the conversion BEFORE the data is actually written to the worksheet. You can do it as part of the import routine, using either VBA or Power Query. Split the parts; create a date; then put the parts back together.
– Ron Rosenfeld
Feb 17 at 2:29