How to I subtract two dates & times to obtain a time

[msbutton27]

Hi folks, I have hopefully what will be a quick and easy question.

I have two colums (A & B) that hold two date and time values, a start and
complete time.
Jul 19 2009 08:30 Jul 19 2009 12:54

I want to be able to find out the actual time difference, for example above
would show 4:24 hours.

In some cases the start and complete dates could be a day or 2 apart,
therefore I would want the output to be displayed in hour format, for example:

Jul 17 2009 08:30 Jul 19 2009 12:54

Column C should be 52:24 hours.

Is there a way to do this.

 

[Mike H]

Hi,

=B1-A1

format as [hh]:mm

Mike

 

[msbutton27]

Thanks for the reply, but the equation below fails. I setup Column A,B and C
as time you suggested below and it gives me the #VALUE! sign

Any other suggestions on what I might be doing wrong.

Hi,

=B1-A1

format as [hh]:mm

Mike

Hi folks, I have hopefully what will be a quick and easy question.

I have two colums (A & B) that hold two date and time values, a start and
complete time.
Jul 19 2009 08:30 Jul 19 2009 12:54

I want to be able to find out the actual time difference, for example above
would show 4:24 hours.

In some cases the start and complete dates could be a day or 2 apart,
therefore I would want the output to be displayed in hour format, for example:

Jul 17 2009 08:30 Jul 19 2009 12:54

Column C should be 52:24 hours.

Is there a way to do this.

 

[Jacob Skaria]

Format the formula cell to [h]:mm so as to get around the 24 hours..

If this post helps click Yes

 

[Per Jessen]

Hi

Format column C as Custom: and in the 'type' field enter: [t]:mm

Now you can simply subtract the two cells with a formula like this:

=B1-A1

Regards,
Per

 

[JoeU2004]

=B1-A1
format as [hh]:mm

I think that should be the custom format [h]:mm to avoid the leading zero in
4:54, per the OP's request.

And that assumes that when the OP wrote Jul 19 2009 08:30, for example, he
meant that the cell contains a true date serial number, perhaps entered as
Jul 19, 2009 8:30 AM, which has the custom format "mmm dd yyyy hh:mm"
(without quotes).

Or does the cell contain text?


----- original message -----

Hi,

=B1-A1

format as [hh]:mm

Mike

Hi folks, I have hopefully what will be a quick and easy question.

I have two colums (A & B) that hold two date and time values, a start and
complete time.
Jul 19 2009 08:30 Jul 19 2009 12:54

I want to be able to find out the actual time difference, for example
above
would show 4:24 hours.

In some cases the start and complete dates could be a day or 2 apart,
therefore I would want the output to be displayed in hour format, for
example:

Jul 17 2009 08:30 Jul 19 2009 12:54

Column C should be 52:24 hours.

Is there a way to do this.

 

[msbutton27]

Thanks but the [t]:mm would produce and error when trying to implement.

 

[msbutton27]

Thanks for the trick, but my equation of B1-A1 still produces and error.

I wonder if there is something in the format for A & B that needs to occur.
The column A contains, Month, Day, Year and Time - does this need to split.

....Mike

Format the formula cell to [h]:mm so as to get around the 24 hours..

If this post helps click Yes
---------------
Jacob Skaria

Hi folks, I have hopefully what will be a quick and easy question.

I have two colums (A & B) that hold two date and time values, a start and
complete time.
Jul 19 2009 08:30 Jul 19 2009 12:54

I want to be able to find out the actual time difference, for example above
would show 4:24 hours.

In some cases the start and complete dates could be a day or 2 apart,
therefore I would want the output to be displayed in hour format, for example:

Jul 17 2009 08:30 Jul 19 2009 12:54

Column C should be 52:24 hours.

Is there a way to do this.

 

[msbutton27]

Sorry what is OP, is it operator?

Column A & B both follow the format:

MONTH DAY YEAR 24TIME
Jul 19 2009 08:30

Do i need to split this out or have a customer format.

=B1-A1
format as [hh]:mm

I think that should be the custom format [h]:mm to avoid the leading zero in
4:54, per the OP's request.

And that assumes that when the OP wrote Jul 19 2009 08:30, for example, he
meant that the cell contains a true date serial number, perhaps entered as
Jul 19, 2009 8:30 AM, which has the custom format "mmm dd yyyy hh:mm"
(without quotes).

Or does the cell contain text?


----- original message -----

Hi,

=B1-A1

format as [hh]:mm

Mike

Hi folks, I have hopefully what will be a quick and easy question.

I have two colums (A & B) that hold two date and time values, a start and
complete time.
Jul 19 2009 08:30 Jul 19 2009 12:54

I want to be able to find out the actual time difference, for example
above
would show 4:24 hours.

In some cases the start and complete dates could be a day or 2 apart,
therefore I would want the output to be displayed in hour format, for
example:

Jul 17 2009 08:30 Jul 19 2009 12:54

Column C should be 52:24 hours.

Is there a way to do this.

 

[Eduardo]

Hi,
use

=+RIGHT(B3,5)-RIGHT(A3,5)

and then format column c as

h:mm

hope this helps

 

[JoeU2004]

it gives me the #VALUE!

I wrote in my response:

And that assumes that when the OP wrote Jul 19 2009 08:30,
for example, he meant that the cell contains a true date serial
number, perhaps entered as Jul 19, 2009 8:30 AM, which has
the custom format "mmm dd yyyy hh:mm" (without quotes).
Or does the cell contain text?

I think your result confirms my suspicion: you have Jul 19 2009 08:30 as
text.

Any other suggestions on what I might be doing wrong.

Ideally, you should enter your date/time data in a form that Excel
recognizes and converts into a date serial number. For example, enter Jul
19, 2009 08:30 (note the comma). You can use the custom format "mmm dd
yyyy hh:mm" (without quotes) to display the date without the comma, if you
wish.

Alternatively, you can convert the text to a numeric value. How you do that
depends on how the date Jul 1 would appear: Jul 01, Jul 1 (two spaces
before 1), or Jul 1 (one space before 1).

If either of the first two cases, then:

=(LEFT(B1,6)&","&RIGHT(B1,11)) -
(LEFT(A1,6)&","&RIGHT(A1,11))

formatted with the custom format [h]:mm.

If the latter (Jul 1, one space before 1), then:

=(LEFT(B1,LEN(B1)-11)&","&RIGHT(B1,11)) -
(LEFT(A1,LEN(A1)-11)&","&RIGHT(A1,11))


----- original message -----

Thanks for the reply, but the equation below fails. I setup Column A,B
and C
as time you suggested below and it gives me the #VALUE! sign

Any other suggestions on what I might be doing wrong.

Hi,

=B1-A1

format as [hh]:mm

Mike

Hi folks, I have hopefully what will be a quick and easy question.

I have two colums (A & B) that hold two date and time values, a start
and
complete time.
Jul 19 2009 08:30 Jul 19 2009 12:54

I want to be able to find out the actual time difference, for example
above
would show 4:24 hours.

In some cases the start and complete dates could be a day or 2 apart,
therefore I would want the output to be displayed in hour format, for
example:

Jul 17 2009 08:30 Jul 19 2009 12:54

Column C should be 52:24 hours.

Is there a way to do this.

 

[msbutton27]

I actually think I know what is going on.

I am opening a TEXT file that contains 2 columns, and 900 rows. So when I
convert it from text to excel for some reason Column A or B are changing to
the correct format, it is getting picked up as TEXT. I can format the columns
manually and it has no affect, the only way I see to do this is to enter them
in manually all over - is there a better way?

....Mike

Hi,
use

=+RIGHT(B3,5)-RIGHT(A3,5)

and then format column c as

h:mm

hope this helps

Thanks but the [t]:mm would produce and error when trying to implement.

 

[JoeU2004]

Sorry what is OP

In this context, "original poster" or "original posting".

Do i need to split this out or have a customer format.

See my response to one of your other postings in this thread.


----- original message -----

Sorry what is OP, is it operator?

Column A & B both follow the format:

MONTH DAY YEAR 24TIME
Jul 19 2009 08:30

Do i need to split this out or have a customer format.

=B1-A1
format as [hh]:mm

I think that should be the custom format [h]:mm to avoid the leading zero
in
4:54, per the OP's request.

And that assumes that when the OP wrote Jul 19 2009 08:30, for example,
he
meant that the cell contains a true date serial number, perhaps entered
as
Jul 19, 2009 8:30 AM, which has the custom format "mmm dd yyyy hh:mm"
(without quotes).

Or does the cell contain text?


----- original message -----

Hi,

=B1-A1

format as [hh]:mm

Mike

:

Hi folks, I have hopefully what will be a quick and easy question.

I have two colums (A & B) that hold two date and time values, a start
and
complete time.
Jul 19 2009 08:30 Jul 19 2009 12:54

I want to be able to find out the actual time difference, for example
above
would show 4:24 hours.

In some cases the start and complete dates could be a day or 2 apart,
therefore I would want the output to be displayed in hour format, for
example:

Jul 17 2009 08:30 Jul 19 2009 12:54

Column C should be 52:24 hours.

Is there a way to do this.

 

[Luke M]

OP = Original Poster (aka, you!)

The information that you have in A & B...do you have it entered as text, or
is it a number? The fact that you are getting the #VALUE! error seems to
indicate that you have text. A quick check would be to do:
=ISNUMBER(A1)
which should return true.

Going with the assumption that you have text, you can quickly convert your
values to true date serials using this method:
Type the number 1 into a blank cell.
Copy that cell.
Select all the cells with dates & times.
Right-click, paste special.
Choose "Multuiply"

All your data should now be numbers, and Mike's formula will work correctly
for you, with a custom format of
[hh]:mm
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Sorry what is OP, is it operator?

Column A & B both follow the format:

MONTH DAY YEAR 24TIME
Jul 19 2009 08:30

Do i need to split this out or have a customer format.

=B1-A1
format as [hh]:mm

I think that should be the custom format [h]:mm to avoid the leading zero in
4:54, per the OP's request.

And that assumes that when the OP wrote Jul 19 2009 08:30, for example, he
meant that the cell contains a true date serial number, perhaps entered as
Jul 19, 2009 8:30 AM, which has the custom format "mmm dd yyyy hh:mm"
(without quotes).

Or does the cell contain text?


----- original message -----

Hi,

=B1-A1

format as [hh]:mm

Mike

:

Hi folks, I have hopefully what will be a quick and easy question.

I have two colums (A & B) that hold two date and time values, a start and
complete time.
Jul 19 2009 08:30 Jul 19 2009 12:54

I want to be able to find out the actual time difference, for example
above
would show 4:24 hours.

In some cases the start and complete dates could be a day or 2 apart,
therefore I would want the output to be displayed in hour format, for
example:

Jul 17 2009 08:30 Jul 19 2009 12:54

Column C should be 52:24 hours.

Is there a way to do this.

 

[Luke M]

msbutton27,

This is my suspicion as well. Please see my other post for a quick, painless
way to convert your text values to date serials.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

I actually think I know what is going on.

I am opening a TEXT file that contains 2 columns, and 900 rows. So when I
convert it from text to excel for some reason Column A or B are changing to
the correct format, it is getting picked up as TEXT. I can format the columns
manually and it has no affect, the only way I see to do this is to enter them
in manually all over - is there a better way?

...Mike

Hi,
use

=+RIGHT(B3,5)-RIGHT(A3,5)

and then format column c as

h:mm

hope this helps

Thanks but the [t]:mm would produce and error when trying to implement.

 

[Luke M]

In re-examining your original post, I see that you have some extra spaces
floating around. It looks like JoeU2004 has offerered sevearl good ideas on
how to extract the info you need.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

OP = Original Poster (aka, you!)

The information that you have in A & B...do you have it entered as text, or
is it a number? The fact that you are getting the #VALUE! error seems to
indicate that you have text. A quick check would be to do:
=ISNUMBER(A1)
which should return true.

Going with the assumption that you have text, you can quickly convert your
values to true date serials using this method:
Type the number 1 into a blank cell.
Copy that cell.
Select all the cells with dates & times.
Right-click, paste special.
Choose "Multuiply"

All your data should now be numbers, and Mike's formula will work correctly
for you, with a custom format of
[hh]:mm
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Sorry what is OP, is it operator?

Column A & B both follow the format:

MONTH DAY YEAR 24TIME
Jul 19 2009 08:30

Do i need to split this out or have a customer format.

=B1-A1
format as [hh]:mm

I think that should be the custom format [h]:mm to avoid the leading zero in
4:54, per the OP's request.

And that assumes that when the OP wrote Jul 19 2009 08:30, for example, he
meant that the cell contains a true date serial number, perhaps entered as
Jul 19, 2009 8:30 AM, which has the custom format "mmm dd yyyy hh:mm"
(without quotes).

Or does the cell contain text?


----- original message -----

Hi,

=B1-A1

format as [hh]:mm

Mike

:

Hi folks, I have hopefully what will be a quick and easy question.

I have two colums (A & B) that hold two date and time values, a start and
complete time.
Jul 19 2009 08:30 Jul 19 2009 12:54

I want to be able to find out the actual time difference, for example
above
would show 4:24 hours.

In some cases the start and complete dates could be a day or 2 apart,
therefore I would want the output to be displayed in hour format, for
example:

Jul 17 2009 08:30 Jul 19 2009 12:54

Column C should be 52:24 hours.

Is there a way to do this.

 

[JoeU2004]

I am opening a TEXT file that contains 2 columns, and 900 rows.
[....]
the only way I see to do this is to enter them
in manually all over - is there a better way?

I don't know of a way to do this directly at import time, nor with an Excel
command after importing.

If no one offers a suggestion along those lines, perhaps the following macro
will be useful.

First, make a copy of the Excel file or worksheet, since the effects of the
macro cannot be undone.

Then select the cells in the 900 rows, then execute the macro:

Sub doit()
Dim cell As Range
For Each cell In Selection
cell = Left(cell, Len(cell) - 11) & "," & Right(cell, 11)
cell.NumberFormat = "mmm dd yyyy hh:mm"
Next cell
End Sub


----- original message -----

I actually think I know what is going on.

I am opening a TEXT file that contains 2 columns, and 900 rows. So when I
convert it from text to excel for some reason Column A or B are changing
to
the correct format, it is getting picked up as TEXT. I can format the
columns
manually and it has no affect, the only way I see to do this is to enter
them
in manually all over - is there a better way?

...Mike

Hi,
use

=+RIGHT(B3,5)-RIGHT(A3,5)

and then format column c as

h:mm

hope this helps

Thanks but the [t]:mm would produce and error when trying to implement.

 

[msbutton27]

Thanks Everyone - I found the root cause, in the field of Column A & B there
were 2 extra spaces in front of the date/time therefore it was not converting
properly. Once I removed those spaces I found the equations worked - thanks
folks.

....Mike

msbutton27,

This is my suspicion as well. Please see my other post for a quick, painless
way to convert your text values to date serials.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

I actually think I know what is going on.

I am opening a TEXT file that contains 2 columns, and 900 rows. So when I
convert it from text to excel for some reason Column A or B are changing to
the correct format, it is getting picked up as TEXT. I can format the columns
manually and it has no affect, the only way I see to do this is to enter them
in manually all over - is there a better way?

...Mike

Hi,
use

=+RIGHT(B3,5)-RIGHT(A3,5)

and then format column c as

h:mm

hope this helps

:


Thanks but the [t]:mm would produce and error when trying to implement.

 

[Jacob Skaria]

The entries in A and B need to be in the excel date/time format..

If this post helps click Yes
---------------
Jacob Skaria

Thanks for the trick, but my equation of B1-A1 still produces and error.

I wonder if there is something in the format for A & B that needs to occur.
The column A contains, Month, Day, Year and Time - does this need to split.

...Mike

Format the formula cell to [h]:mm so as to get around the 24 hours..

If this post helps click Yes
---------------
Jacob Skaria

Hi folks, I have hopefully what will be a quick and easy question.

I have two colums (A & B) that hold two date and time values, a start and
complete time.
Jul 19 2009 08:30 Jul 19 2009 12:54

I want to be able to find out the actual time difference, for example above
would show 4:24 hours.

In some cases the start and complete dates could be a day or 2 apart,
therefore I would want the output to be displayed in hour format, for example:

Jul 17 2009 08:30 Jul 19 2009 12:54

Column C should be 52:24 hours.

Is there a way to do this.

 

[JoeU2004]

Once I removed those spaces I found the equations worked

That surprises me. I thought it was the lack of a comma after the day
number that caused the problem.

For my edification, please post the corrected form of the date/time that you
are using. And please post the value of TYPE(A1), assuming the corrected
timestamps is in A1.


----- original message -----

Thanks Everyone - I found the root cause, in the field of Column A & B
there
were 2 extra spaces in front of the date/time therefore it was not
converting
properly. Once I removed those spaces I found the equations worked -
thanks
folks.

...Mike

msbutton27,

This is my suspicion as well. Please see my other post for a quick,
painless
way to convert your text values to date serials.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

I actually think I know what is going on.

I am opening a TEXT file that contains 2 columns, and 900 rows. So when
I
convert it from text to excel for some reason Column A or B are
changing to
the correct format, it is getting picked up as TEXT. I can format the
columns
manually and it has no affect, the only way I see to do this is to
enter them
in manually all over - is there a better way?

...Mike

:

Hi,
use

=+RIGHT(B3,5)-RIGHT(A3,5)

and then format column c as

h:mm

hope this helps

:


Thanks but the [t]:mm would produce and error when trying to
implement.