Time calculation problem (URGENT>>>Project due)

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!



--
gman

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


"g6pack" wrote:

> 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!
>
>
>
> --
> gman

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

--
gman


"Ron Coderre" wrote:

> 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
>
>
> "g6pack" wrote:
>
> > 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!
> >
> >
> >
> > --
> > gman

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

--
Regards,
Fred


"g6pack" <g6pack@discussions.microsoft.com> wrote in message
news:5D705A05-796C-46EE-9D8A-651284D5613A@microsoft.com...
>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
>
> --
> gman
>
>
> "Ron Coderre" wrote:
>
>> 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
>>
>>
>> "g6pack" wrote:
>>
>> > 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!
>> >
>> >
>> >
>> > --
>> > gman

Perfect!!! Thanks Fred!
--
gman


"Fred Smith" wrote:

> To display more than 24 hours, use a format of [hh]:mm
>
> --
> Regards,
> Fred
>
>
> "g6pack" <g6pack@discussions.microsoft.com> wrote in message
> news:5D705A05-796C-46EE-9D8A-651284D5613A@microsoft.com...
> >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
> >
> > --
> > gman
> >
> >
> > "Ron Coderre" wrote:
> >
> >> 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
> >>
> >>
> >> "g6pack" wrote:
> >>
> >> > 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!
> >> >
> >> >
> >> >
> >> > --
> >> > gman
>
>
>