Time calculation problem (URGENT>>>Project due)

[Guest]

I'm trying to find the difference in date and time witht the respective input
data in four columns (see below). I have attempted to combine the contents
of each respective date with its respective times to get the following
results with the differene of the combinations in cell G1. I have formatted
cells a1, c1, wih mm/dd/yy, and b1, d1 as general. Cells e1,f1 are formatted
with time format: mm/dd/yy 00:00. The method to combine the data fields was:
=A1&" "&B1 (same for C1, D1).

Why am I not getting the time difference in G1? Why is the data combination
results not showing in defined format?
Are there any other ways to get the time differences? I will have dates
that go past 24 hours, so any methods to incorporate that as well is much
appreciated.

A1 B1 C1 D1 E1
F1 G1
1/2/05 2000 1/4/05 2200 38354 2000 38356 2200 #VALUE!

THANKS MUCH!

 

[Guest]

This should get you in the right direction:
Excel counts TIME as a decimal fraction of a DAY.
So 1:00 AM is 1/24 or 0.0416666666666667
In your case, 2200 needs to be 2200/2400
What you need to do is:
cell E1: =A1+(B1/2400)

Does that help?

***********
Regards,
Ron

 

[Guest]

I actually found another post that guided me to convert the time (b1, d1) in
text format to a time format by using: =--TEXT(A1,"00\:00\"). I then added
the new time columns to the old date columns to get new date/time columns.
AFter subtracting the new respective date/time columns, I actualy got an
hourly difference; however, it did not compensate for the differnce in date.
How do I incoporate going over 24 hours for a date/time difference

 

[Fred Smith]

To display more than 24 hours, use a format of [hh]:mm

 

[Guest]

Perfect!!! Thanks Fred!
--
gman

To display more than 24 hours, use a format of [hh]:mm