How to compare time between 2 columns in Excel
I have 2 columns of date in my Excel spreadsheet, and I want to calculate the time (in sec) between the 2 dates.
Time Time1
2017-08-22 19:45:22.2327928 2017-08-22 19:45:20.9915171
2017-08-22 19:45:40.3645187 2017-08-22 19:45:21.4451237
2017-08-22 19:45:25.2337426 2017-08-22 19:45:24.3347192
2017-08-22 19:45:20.8958264 2017-08-22 19:45:27.1250265
2017-08-22 19:45:29.5987311 2017-08-22 19:45:27.9014672
I went thru this article, and I tried using these formulas:
=TEXT(D2-B2, "hh:mm:ss")
=TEXT(D2-B2, "yyyy-mm-dd hh:mm:ss")
But none of them works.
Can you please tell me how can I get the time difference between 2 dates in Excel?
microsoft-excel date-time
edited Aug 23 '17 at 9:05
Glorfindel
1,38241220
asked Aug 22 '17 at 21:00
n179911n179911
1,44072831
add a comment |
I have 2 columns of date in my Excel spreadsheet, and I want to calculate the time (in sec) between the 2 dates.
Time Time1
2017-08-22 19:45:22.2327928 2017-08-22 19:45:20.9915171
2017-08-22 19:45:40.3645187 2017-08-22 19:45:21.4451237
2017-08-22 19:45:25.2337426 2017-08-22 19:45:24.3347192
2017-08-22 19:45:20.8958264 2017-08-22 19:45:27.1250265
2017-08-22 19:45:29.5987311 2017-08-22 19:45:27.9014672
I went thru this article, and I tried using these formulas:
=TEXT(D2-B2, "hh:mm:ss")
=TEXT(D2-B2, "yyyy-mm-dd hh:mm:ss")
But none of them works.
Can you please tell me how can I get the time difference between 2 dates in Excel?
microsoft-excel date-time
edited Aug 23 '17 at 9:05
Glorfindel
1,38241220
asked Aug 22 '17 at 21:00
n179911n179911
1,44072831
2
=(D2-B2)*24*60*60if that does not work then your date time is not a number but a string that looks like date/time. If that is the case you will need to parse the data to turn it into a true date/time.
– Scott Craner
Aug 22 '17 at 21:05
add a comment |
I have 2 columns of date in my Excel spreadsheet, and I want to calculate the time (in sec) between the 2 dates.
Time Time1
2017-08-22 19:45:22.2327928 2017-08-22 19:45:20.9915171
2017-08-22 19:45:40.3645187 2017-08-22 19:45:21.4451237
2017-08-22 19:45:25.2337426 2017-08-22 19:45:24.3347192
2017-08-22 19:45:20.8958264 2017-08-22 19:45:27.1250265
2017-08-22 19:45:29.5987311 2017-08-22 19:45:27.9014672
I went thru this article, and I tried using these formulas:
=TEXT(D2-B2, "hh:mm:ss")
=TEXT(D2-B2, "yyyy-mm-dd hh:mm:ss")
But none of them works.
Can you please tell me how can I get the time difference between 2 dates in Excel?
microsoft-excel date-time
edited Aug 23 '17 at 9:05
Glorfindel
1,38241220
asked Aug 22 '17 at 21:00
n179911n179911
1,44072831
I have 2 columns of date in my Excel spreadsheet, and I want to calculate the time (in sec) between the 2 dates.
Time Time1
2017-08-22 19:45:22.2327928 2017-08-22 19:45:20.9915171
2017-08-22 19:45:40.3645187 2017-08-22 19:45:21.4451237
2017-08-22 19:45:25.2337426 2017-08-22 19:45:24.3347192
2017-08-22 19:45:20.8958264 2017-08-22 19:45:27.1250265
2017-08-22 19:45:29.5987311 2017-08-22 19:45:27.9014672
I went thru this article, and I tried using these formulas:
=TEXT(D2-B2, "hh:mm:ss")
=TEXT(D2-B2, "yyyy-mm-dd hh:mm:ss")
But none of them works.
Can you please tell me how can I get the time difference between 2 dates in Excel?
microsoft-excel date-time
microsoft-excel date-time
edited Aug 23 '17 at 9:05
Glorfindel
1,38241220
asked Aug 22 '17 at 21:00
n179911n179911
1,44072831
edited Aug 23 '17 at 9:05
Glorfindel
1,38241220
asked Aug 22 '17 at 21:00
n179911n179911
1,44072831
edited Aug 23 '17 at 9:05
Glorfindel
1,38241220
edited Aug 23 '17 at 9:05
Glorfindel
1,38241220
edited Aug 23 '17 at 9:05
Glorfindel
1,38241220
1,38241220
asked Aug 22 '17 at 21:00
n179911n179911
1,44072831
asked Aug 22 '17 at 21:00
n179911n179911
1,44072831
asked Aug 22 '17 at 21:00
n179911n179911
1,44072831
1,44072831
2
=(D2-B2)*24*60*60if that does not work then your date time is not a number but a string that looks like date/time. If that is the case you will need to parse the data to turn it into a true date/time.
– Scott Craner
Aug 22 '17 at 21:05
add a comment |
2
=(D2-B2)*24*60*60if that does not work then your date time is not a number but a string that looks like date/time. If that is the case you will need to parse the data to turn it into a true date/time.
– Scott Craner
Aug 22 '17 at 21:05
2
2
=(D2-B2)*24*60*60 if that does not work then your date time is not a number but a string that looks like date/time. If that is the case you will need to parse the data to turn it into a true date/time.– Scott Craner
Aug 22 '17 at 21:05
=(D2-B2)*24*60*60 if that does not work then your date time is not a number but a string that looks like date/time. If that is the case you will need to parse the data to turn it into a true date/time.– Scott Craner
Aug 22 '17 at 21:05
add a comment |
2 Answers
2
active
oldest
votes
These are not time values, they are text. You can convert them to time values with
=DATEVALUE(LEFT(A2,10))+RIGHT(A2,16)
Copy down and across.
After that, you can subtract one value from the other.
answered Aug 23 '17 at 5:07
teylynteylyn
17.3k22539
add a comment |
This formula will work:
=(DATEVALUE(D2)+TIMEVALUE(D2)-(DATEVALUE(B2)+TIMEVALUE(B2)))*24*60*60
Real datetime values are stored internally in Excel as numbers. (More specifically, the date part is stored as the integer part of the number and the time part is stored as the fraction part.)
You can also store a representation of a datetime as a string. This is what your values actually are.
To get the difference between two of your datetimes you first need to convert them to numbers. This is what DATEVALUE(D2)+TIMEVALUE(D2) does to D2.
Then, after calculating the difference, you need to could convert it to seconds. Remembering that a datetime (and thus a difference between datetimes) is stored as a number where 1 is a whole day, multiplying the difference by 24*60*60 converts it to seconds.
answered Aug 23 '17 at 7:04
robinCTSrobinCTS
4,01741527
add a comment |
Your Answer
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%2f1243425%2fhow-to-compare-time-between-2-columns-in-excel%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
These are not time values, they are text. You can convert them to time values with
=DATEVALUE(LEFT(A2,10))+RIGHT(A2,16)
Copy down and across.
After that, you can subtract one value from the other.
answered Aug 23 '17 at 5:07
teylynteylyn
17.3k22539
add a comment |
These are not time values, they are text. You can convert them to time values with
=DATEVALUE(LEFT(A2,10))+RIGHT(A2,16)
Copy down and across.
After that, you can subtract one value from the other.
answered Aug 23 '17 at 5:07
teylynteylyn
17.3k22539
add a comment |
These are not time values, they are text. You can convert them to time values with
=DATEVALUE(LEFT(A2,10))+RIGHT(A2,16)
Copy down and across.
After that, you can subtract one value from the other.
answered Aug 23 '17 at 5:07
teylynteylyn
17.3k22539
These are not time values, they are text. You can convert them to time values with
=DATEVALUE(LEFT(A2,10))+RIGHT(A2,16)
Copy down and across.
After that, you can subtract one value from the other.
answered Aug 23 '17 at 5:07
teylynteylyn
17.3k22539
answered Aug 23 '17 at 5:07
teylynteylyn
17.3k22539
answered Aug 23 '17 at 5:07
teylynteylyn
17.3k22539
answered Aug 23 '17 at 5:07
teylynteylyn
17.3k22539
17.3k22539
add a comment |
add a comment |
This formula will work:
=(DATEVALUE(D2)+TIMEVALUE(D2)-(DATEVALUE(B2)+TIMEVALUE(B2)))*24*60*60
Real datetime values are stored internally in Excel as numbers. (More specifically, the date part is stored as the integer part of the number and the time part is stored as the fraction part.)
You can also store a representation of a datetime as a string. This is what your values actually are.
To get the difference between two of your datetimes you first need to convert them to numbers. This is what DATEVALUE(D2)+TIMEVALUE(D2) does to D2.
Then, after calculating the difference, you need to could convert it to seconds. Remembering that a datetime (and thus a difference between datetimes) is stored as a number where 1 is a whole day, multiplying the difference by 24*60*60 converts it to seconds.
answered Aug 23 '17 at 7:04
robinCTSrobinCTS
4,01741527
add a comment |
This formula will work:
=(DATEVALUE(D2)+TIMEVALUE(D2)-(DATEVALUE(B2)+TIMEVALUE(B2)))*24*60*60
Real datetime values are stored internally in Excel as numbers. (More specifically, the date part is stored as the integer part of the number and the time part is stored as the fraction part.)
You can also store a representation of a datetime as a string. This is what your values actually are.
To get the difference between two of your datetimes you first need to convert them to numbers. This is what DATEVALUE(D2)+TIMEVALUE(D2) does to D2.
Then, after calculating the difference, you need to could convert it to seconds. Remembering that a datetime (and thus a difference between datetimes) is stored as a number where 1 is a whole day, multiplying the difference by 24*60*60 converts it to seconds.
answered Aug 23 '17 at 7:04
robinCTSrobinCTS
4,01741527
add a comment |
This formula will work:
=(DATEVALUE(D2)+TIMEVALUE(D2)-(DATEVALUE(B2)+TIMEVALUE(B2)))*24*60*60
Real datetime values are stored internally in Excel as numbers. (More specifically, the date part is stored as the integer part of the number and the time part is stored as the fraction part.)
You can also store a representation of a datetime as a string. This is what your values actually are.
To get the difference between two of your datetimes you first need to convert them to numbers. This is what DATEVALUE(D2)+TIMEVALUE(D2) does to D2.
Then, after calculating the difference, you need to could convert it to seconds. Remembering that a datetime (and thus a difference between datetimes) is stored as a number where 1 is a whole day, multiplying the difference by 24*60*60 converts it to seconds.
answered Aug 23 '17 at 7:04
robinCTSrobinCTS
4,01741527
This formula will work:
=(DATEVALUE(D2)+TIMEVALUE(D2)-(DATEVALUE(B2)+TIMEVALUE(B2)))*24*60*60
Real datetime values are stored internally in Excel as numbers. (More specifically, the date part is stored as the integer part of the number and the time part is stored as the fraction part.)
You can also store a representation of a datetime as a string. This is what your values actually are.
To get the difference between two of your datetimes you first need to convert them to numbers. This is what DATEVALUE(D2)+TIMEVALUE(D2) does to D2.
Then, after calculating the difference, you need to could convert it to seconds. Remembering that a datetime (and thus a difference between datetimes) is stored as a number where 1 is a whole day, multiplying the difference by 24*60*60 converts it to seconds.
answered Aug 23 '17 at 7:04
robinCTSrobinCTS
4,01741527
edited Aug 24 '17 at 6:12
answered Aug 23 '17 at 7:04
robinCTSrobinCTS
4,01741527
answered Aug 23 '17 at 7:04
robinCTSrobinCTS
4,01741527
answered Aug 23 '17 at 7:04
robinCTSrobinCTS
4,01741527
4,01741527
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%2f1243425%2fhow-to-compare-time-between-2-columns-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
2
=(D2-B2)*24*60*60if that does not work then your date time is not a number but a string that looks like date/time. If that is the case you will need to parse the data to turn it into a true date/time.– Scott Craner
Aug 22 '17 at 21:05