How to locate cells with different date format, then delete them in Excel?
I have data with two different date formats (Fig.1), one format is "yyyy/m/d", the other is "mm/dd/yyyy hh:mm".
I want to delete data whose date format is "mm/dd/yyyy hh:mm" (yellow in Fig.1), because I only need the daily data. The expected result is listed as below:
Date Collected Value
2016/1/1 2.1
2016/1/2 0.6
2016/1/3 0.01
2016/1/4 0.9
2016/1/5 3
2016/1/6 1.9
2016/1/7 0.5
2016/1/8 1.1
2016/1/9 0
Could someone help me figure it out? The example can be downloaed in here in Google Drive
excel
asked Nov 21 at 4:53
T X
71116
add a comment |
I have data with two different date formats (Fig.1), one format is "yyyy/m/d", the other is "mm/dd/yyyy hh:mm".
I want to delete data whose date format is "mm/dd/yyyy hh:mm" (yellow in Fig.1), because I only need the daily data. The expected result is listed as below:
Date Collected Value
2016/1/1 2.1
2016/1/2 0.6
2016/1/3 0.01
2016/1/4 0.9
2016/1/5 3
2016/1/6 1.9
2016/1/7 0.5
2016/1/8 1.1
2016/1/9 0
Could someone help me figure it out? The example can be downloaed in here in Google Drive
excel
asked Nov 21 at 4:53
T X
71116
add a comment |
I have data with two different date formats (Fig.1), one format is "yyyy/m/d", the other is "mm/dd/yyyy hh:mm".
I want to delete data whose date format is "mm/dd/yyyy hh:mm" (yellow in Fig.1), because I only need the daily data. The expected result is listed as below:
Date Collected Value
2016/1/1 2.1
2016/1/2 0.6
2016/1/3 0.01
2016/1/4 0.9
2016/1/5 3
2016/1/6 1.9
2016/1/7 0.5
2016/1/8 1.1
2016/1/9 0
Could someone help me figure it out? The example can be downloaed in here in Google Drive
excel
asked Nov 21 at 4:53
T X
71116
I have data with two different date formats (Fig.1), one format is "yyyy/m/d", the other is "mm/dd/yyyy hh:mm".
I want to delete data whose date format is "mm/dd/yyyy hh:mm" (yellow in Fig.1), because I only need the daily data. The expected result is listed as below:
Date Collected Value
2016/1/1 2.1
2016/1/2 0.6
2016/1/3 0.01
2016/1/4 0.9
2016/1/5 3
2016/1/6 1.9
2016/1/7 0.5
2016/1/8 1.1
2016/1/9 0
Could someone help me figure it out? The example can be downloaed in here in Google Drive
excel
excel
asked Nov 21 at 4:53
T X
71116
asked Nov 21 at 4:53
T X
71116
asked Nov 21 at 4:53
T X
71116
asked Nov 21 at 4:53
T X
71116
asked Nov 21 at 4:53
T X
71116
71116
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
In Excel, a date is a whole number, and the time is a decimal. You can subtract the Int of the cell from itself to see if the cell returns a value, if it does - then it contains a time. This is what you want to delete.
Add the data you want to delete to a special range, in this example: delRng, then delete delRng once you have finished looping.
Sub delData()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
Dim r As Long, delRng As Range
With ws
For r = 2 To LastRow(ws)
If .Cells(r, 1) - Int(.Cells(r, 1)) > 0 Then
If delRng Is Nothing Then
Set delRng = .Range(.Cells(r, 1), .Cells(r, 2))
Else
Set delRng = Union(delRng, .Range(.Cells(r, 1), .Cells(r, 2)))
End If
End If
Next r
End With
If Not delRng Is Nothing Then delRng.Delete
End Sub
Function LastRow(ByVal ws As Worksheet, Optional ByVal col As Variant = 1) As Long
With ws
LastRow = .Cells(.Rows.Count, col).End(xlUp).Row
End With
End Function
answered Nov 21 at 5:02
K.Dᴀᴠɪs
6,892112139
1
Your idea to distinct them by "whether or not Interger" is truly awesome! I now can use the formula "=IF(INT(D2)=D2,"Yes","No")", and then fliter "No".
– T X
Nov 21 at 6:53
That's certainly a much simpler method :). Glad you got it figured out!
– K.Dᴀᴠɪs
Nov 21 at 6:54
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%2f53405465%2fhow-to-locate-cells-with-different-date-format-then-delete-them-in-excel%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
In Excel, a date is a whole number, and the time is a decimal. You can subtract the Int of the cell from itself to see if the cell returns a value, if it does - then it contains a time. This is what you want to delete.
Add the data you want to delete to a special range, in this example: delRng, then delete delRng once you have finished looping.
Sub delData()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
Dim r As Long, delRng As Range
With ws
For r = 2 To LastRow(ws)
If .Cells(r, 1) - Int(.Cells(r, 1)) > 0 Then
If delRng Is Nothing Then
Set delRng = .Range(.Cells(r, 1), .Cells(r, 2))
Else
Set delRng = Union(delRng, .Range(.Cells(r, 1), .Cells(r, 2)))
End If
End If
Next r
End With
If Not delRng Is Nothing Then delRng.Delete
End Sub
Function LastRow(ByVal ws As Worksheet, Optional ByVal col As Variant = 1) As Long
With ws
LastRow = .Cells(.Rows.Count, col).End(xlUp).Row
End With
End Function
answered Nov 21 at 5:02
K.Dᴀᴠɪs
6,892112139
1
Your idea to distinct them by "whether or not Interger" is truly awesome! I now can use the formula "=IF(INT(D2)=D2,"Yes","No")", and then fliter "No".
– T X
Nov 21 at 6:53
That's certainly a much simpler method :). Glad you got it figured out!
– K.Dᴀᴠɪs
Nov 21 at 6:54
add a comment |
In Excel, a date is a whole number, and the time is a decimal. You can subtract the Int of the cell from itself to see if the cell returns a value, if it does - then it contains a time. This is what you want to delete.
Add the data you want to delete to a special range, in this example: delRng, then delete delRng once you have finished looping.
Sub delData()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
Dim r As Long, delRng As Range
With ws
For r = 2 To LastRow(ws)
If .Cells(r, 1) - Int(.Cells(r, 1)) > 0 Then
If delRng Is Nothing Then
Set delRng = .Range(.Cells(r, 1), .Cells(r, 2))
Else
Set delRng = Union(delRng, .Range(.Cells(r, 1), .Cells(r, 2)))
End If
End If
Next r
End With
If Not delRng Is Nothing Then delRng.Delete
End Sub
Function LastRow(ByVal ws As Worksheet, Optional ByVal col As Variant = 1) As Long
With ws
LastRow = .Cells(.Rows.Count, col).End(xlUp).Row
End With
End Function
answered Nov 21 at 5:02
K.Dᴀᴠɪs
6,892112139
1
Your idea to distinct them by "whether or not Interger" is truly awesome! I now can use the formula "=IF(INT(D2)=D2,"Yes","No")", and then fliter "No".
– T X
Nov 21 at 6:53
That's certainly a much simpler method :). Glad you got it figured out!
– K.Dᴀᴠɪs
Nov 21 at 6:54
add a comment |
In Excel, a date is a whole number, and the time is a decimal. You can subtract the Int of the cell from itself to see if the cell returns a value, if it does - then it contains a time. This is what you want to delete.
Add the data you want to delete to a special range, in this example: delRng, then delete delRng once you have finished looping.
Sub delData()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
Dim r As Long, delRng As Range
With ws
For r = 2 To LastRow(ws)
If .Cells(r, 1) - Int(.Cells(r, 1)) > 0 Then
If delRng Is Nothing Then
Set delRng = .Range(.Cells(r, 1), .Cells(r, 2))
Else
Set delRng = Union(delRng, .Range(.Cells(r, 1), .Cells(r, 2)))
End If
End If
Next r
End With
If Not delRng Is Nothing Then delRng.Delete
End Sub
Function LastRow(ByVal ws As Worksheet, Optional ByVal col As Variant = 1) As Long
With ws
LastRow = .Cells(.Rows.Count, col).End(xlUp).Row
End With
End Function
answered Nov 21 at 5:02
K.Dᴀᴠɪs
6,892112139
In Excel, a date is a whole number, and the time is a decimal. You can subtract the Int of the cell from itself to see if the cell returns a value, if it does - then it contains a time. This is what you want to delete.
Add the data you want to delete to a special range, in this example: delRng, then delete delRng once you have finished looping.
Sub delData()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
Dim r As Long, delRng As Range
With ws
For r = 2 To LastRow(ws)
If .Cells(r, 1) - Int(.Cells(r, 1)) > 0 Then
If delRng Is Nothing Then
Set delRng = .Range(.Cells(r, 1), .Cells(r, 2))
Else
Set delRng = Union(delRng, .Range(.Cells(r, 1), .Cells(r, 2)))
End If
End If
Next r
End With
If Not delRng Is Nothing Then delRng.Delete
End Sub
Function LastRow(ByVal ws As Worksheet, Optional ByVal col As Variant = 1) As Long
With ws
LastRow = .Cells(.Rows.Count, col).End(xlUp).Row
End With
End Function
answered Nov 21 at 5:02
K.Dᴀᴠɪs
6,892112139
edited Nov 21 at 5:14
answered Nov 21 at 5:02
K.Dᴀᴠɪs
6,892112139
answered Nov 21 at 5:02
K.Dᴀᴠɪs
6,892112139
answered Nov 21 at 5:02
K.Dᴀᴠɪs
6,892112139
6,892112139
1
Your idea to distinct them by "whether or not Interger" is truly awesome! I now can use the formula "=IF(INT(D2)=D2,"Yes","No")", and then fliter "No".
– T X
Nov 21 at 6:53
That's certainly a much simpler method :). Glad you got it figured out!
– K.Dᴀᴠɪs
Nov 21 at 6:54
add a comment |
1
Your idea to distinct them by "whether or not Interger" is truly awesome! I now can use the formula "=IF(INT(D2)=D2,"Yes","No")", and then fliter "No".
– T X
Nov 21 at 6:53
That's certainly a much simpler method :). Glad you got it figured out!
– K.Dᴀᴠɪs
Nov 21 at 6:54
1
1
Your idea to distinct them by "whether or not Interger" is truly awesome! I now can use the formula "=IF(INT(D2)=D2,"Yes","No")", and then fliter "No".
– T X
Nov 21 at 6:53
Your idea to distinct them by "whether or not Interger" is truly awesome! I now can use the formula "=IF(INT(D2)=D2,"Yes","No")", and then fliter "No".
– T X
Nov 21 at 6:53
That's certainly a much simpler method :). Glad you got it figured out!
– K.Dᴀᴠɪs
Nov 21 at 6:54
That's certainly a much simpler method :). Glad you got it figured out!
– K.Dᴀᴠɪs
Nov 21 at 6:54
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%2f53405465%2fhow-to-locate-cells-with-different-date-format-then-delete-them-in-excel%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
