Date and Time difference problems

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SilverShifter - 21 Jun 2006 00:29 GMT

I am trying to find the difference between two dates/times. For
example:

B2 contains "Jun 12 2006 10:52PM"
C2 contains "Jun 20 2006 10:47AM"

I need to find the time elapsed between the two above. Preferrably I
would like to know the number of hours elapsed, but days/hours would be
fine as well. I cannot figure out how to do this after reading and
searching for weeks now (from what I am seeing I think there may be a
problem with the format of the date/time I am using).

Thanks.

Signature

SilverShifter

Reply to this Message

Franz Verga - 21 Jun 2006 01:01 GMT

*SilverShifter* ha scritto:

> I am trying to find the difference between two dates/times. For
> example:
[quoted text clipped - 9 lines]
>
> Thanks.

Hi SilverShifter,

You have to simply make the difference beetwen C2 and B2, so put in a cell
the formula:

=C2-B2

and format the cell wirh menu Formats, Format Cells, Custom and type: [h]:mm

(I'm not sure of the names of commands, because I'm using an Italian
version of Excel and than translating in English...)

Signature

Hope I helped you.

Ciao

Franz Verga from Italy

Reply to this Message

Ron Rosenfeld - 21 Jun 2006 01:14 GMT

>I am trying to find the difference between two dates/times. For
>example:
[quoted text clipped - 9 lines]
>
>Thanks.

If those values are really Excel dates, all you need to do is subtract one from
the other. The result will be in days and fractions of days. For the example
you give: 7.49652777778 (you must format the result as a number or General).

For the time in hours, merely multiply by 24 --> 179.92 hours

If you are getting error values, it is likely your date-time string is a text
string and not a true Excel date. The simplest solution, if feasible, is to
alter the data entry method so as to ensure an Excel date is entered. For
example, if the above were entered as

12 Jun 2006 10:52 PM

then Excel would automatically convert it to a proper date value. Other entry
methods would also work.

If that is not feasible, then you need text functions to convert the date. If
all of your date/time strings appear in the exact format you show:

3 letter month <space> 2 digit date <space> 4 digit year <space> 7 character
time string (with no spaces)

then

=--(MID(A1,5,3)&LEFT(A1,4)&MID(A1,8,10)&" "&RIGHT(A1,2))

would convert it to an Excel date. The result may appear like a number
(38880.95278) but that is just a formatting issue. You can then subtract the
converted data and obtain your answers.
--ron

Reply to this Message

SilverShifter - 21 Jun 2006 03:54 GMT

Ron,

That helped a lot, thank you.

One question, what is the difference if some time strings are only 6
characters as opposed to 7? THat is the only problem i am running into
now.

Thanks.

Signature

SilverShifter

Reply to this Message

Ron Rosenfeld - 21 Jun 2006 12:49 GMT

>Ron,
>
[quoted text clipped - 5 lines]
>
>Thanks.

The formula can be modified, but give some more examples of the various formats
in which your data may appear.

--ron

Reply to this Message