Date and Time difference problems

S

SilverShifter

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.
 
F

Franz Verga

Nel post *SilverShifter* ha scritto:
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.
Click to expand...

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...)

--
Hope I helped you.

Ciao

Franz Verga from Italy
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
S

SilverShifter

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.
 
R

Ron Rosenfeld

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.
Click to expand...

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


--ron
 
Top