How to I subtract two dates & times to obtain a time
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!*
"Luke M" wrote:
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!*
"msbutton27" wrote:
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.
"JoeU2004" wrote:
"Mike H" wrote:
=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 -----
"Mike H" wrote in message
...
Hi,
=B1-A1
format as [hh]:mm
Mike
"msbutton27" wrote:
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.
|