How do you put the Num-Days and Hours into total hours

on Excel, I have 2 fields.
one (number format cell) has the number of days.
another (time format cell) has hh:mm is there a way to make another
cell to have days/hours?

OR, if you would rather.

12/17/2008 13:23
12/19/2008 15:35
given these "date and time" fields, is there a way to derive the total
number of hours between the two?

Thanks,
Crzzy1

One way:

A1: 12/17/2008 13:23
B1: 12/19/2008 15:35

then

C1: B1-A1

Format C1 as '[hh]:mm' to display '50:12'

Or if you want integral hours (since XL stores times as fractional
days):

C1: =ROUND((B1-A1)*24,0)

to display '50'

In article
,
wrote:

How do you put the Num-Days and Hours into total hours

on Excel, I have 2 fields.
one (number format cell) has the number of days.
another (time format cell) has hh:mm is there a way to make another
cell to have days/hours?

OR, if you would rather.

12/17/2008 13:23
12/19/2008 15:35
given these "date and time" fields, is there a way to derive the total
number of hours between the two?

Thanks,
Crzzy1

=(A2+B2)-(A1+B1) and format the result as [h]:mm if you want it as Excel
time in hours and minutes
or
=((A2+B2)-(A1+B1))*24 and format as number or general if you want decimal
hours
--
David Biddulph

wrote in message
...
How do you put the Num-Days and Hours into total hours

on Excel, I have 2 fields.
one (number format cell) has the number of days.
another (time format cell) has hh:mm is there a way to make another
cell to have days/hours?

OR, if you would rather.

12/17/2008 13:23
12/19/2008 15:35
given these "date and time" fields, is there a way to derive the total
number of hours between the two?

Thanks,
Crzzy1

With your data in A1 thru B2, In C1 enter =A1+B1
In C2 enter =A2+B2
in C3 enter =C2-C1 and format as [hh]:ss to display

50:12

--
Gary''s Student - gsnu200826


" wrote:

How do you put the Num-Days and Hours into total hours

on Excel, I have 2 fields.
one (number format cell) has the number of days.
another (time format cell) has hh:mm is there a way to make another
cell to have days/hours?

OR, if you would rather.

12/17/2008 13:23
12/19/2008 15:35
given these "date and time" fields, is there a way to derive the total
number of hours between the two?

Thanks,
Crzzy1

Maybe this:

=(A2+B2)-(A1+B1)

Format as [h]:mm

--
Biff
Microsoft Excel MVP


wrote in message
...
How do you put the Num-Days and Hours into total hours

on Excel, I have 2 fields.
one (number format cell) has the number of days.
another (time format cell) has hh:mm is there a way to make another
cell to have days/hours?

OR, if you would rather.

12/17/2008 13:23
12/19/2008 15:35
given these "date and time" fields, is there a way to derive the total
number of hours between the two?

Thanks,
Crzzy1

On Jan 13, 1:07*pm, "T. Valko" wrote:
Maybe this:

=(A2+B2)-(A1+B1)

Format as [h]:mm

--
Biff
Microsoft Excel MVP

wrote in message

...

How do you put the Num-Days and Hours into total hours

on Excel, I have 2 fields.
one (number format cell) has the number of days.
another (time format cell) has hh:mm is there a way to make another
cell to have days/hours?

OR, if you would rather.

12/17/2008 13:23
12/19/2008 15:35
given these "date and time" fields, is there a way to derive the total
number of hours between the two?

Thanks,
Crzzy1

--------------------

None of the above really work.
I have 2 ways of doing this.

Option 1: (for some reason this is the only option anyone has tried
to solve)
A is formatted as Date, and B is formatted as Time.
A1: 12/17/2008 B1: 13:13
A2: 12/17/2008 B2: 13:22

Option 2: (this looks like it should be the simpler approach)
C1: 0 D1: 0:00
C2: 2 D2: 2:12
I would like to have E2 = 50:12
The C columb is days, and D is hours.
Somehow I cannot demonstrate a simple 2*24 + 2:12 = 50:12
Maybe due to the fact that the C is a number cell and D is time
formatted.

-----------
To address the proposed solutions thus far:

=ROUND(((A2+B2)-(A1+B1))*24,0)
gives me 50.0 if I format as number. and 0:00 if I format as HH:MM.

=(A2+B2)-(A1+B1) gives me 2:22 if formatted as h:mm (Or 2.10 if
formatted as a number).

=((A2+B2)-(A1+B1))*24
gives me 8:48 if h:mm
or 50.37 if generall number. (This is the closest solution yet, but I
don't know how 50:12 looks like 50.37, as the 37 is not minuts or
100's)

Thanks,
Crzzy1
---------

Format E2 as [h]:mm (note the brackets - they keep XL from rolling over
the display every 24 hours) and you'll see 50:22 instead.


In article
,
wrote:

=(A2+B2)-(A1+B1) gives me 2:22 if formatted as h:mm (Or 2.10 if
formatted as a number).

Using your original sample data:

..............A..................B....
1...12/17/2008........13:23
2...12/19/2008........15:35

Tell us what results you expect and in what cell(s) should the results
appear.

--
Biff
Microsoft Excel MVP


wrote in message
...
On Jan 13, 1:07 pm, "T. Valko" wrote:
Maybe this:

=(A2+B2)-(A1+B1)

Format as [h]:mm

--
Biff
Microsoft Excel MVP

wrote in message

...

How do you put the Num-Days and Hours into total hours

on Excel, I have 2 fields.
one (number format cell) has the number of days.
another (time format cell) has hh:mm is there a way to make another
cell to have days/hours?

OR, if you would rather.

12/17/2008 13:23
12/19/2008 15:35
given these "date and time" fields, is there a way to derive the total
number of hours between the two?

Thanks,
Crzzy1

--------------------

None of the above really work.
I have 2 ways of doing this.

Option 1: (for some reason this is the only option anyone has tried
to solve)
A is formatted as Date, and B is formatted as Time.
A1: 12/17/2008 B1: 13:13
A2: 12/17/2008 B2: 13:22

Option 2: (this looks like it should be the simpler approach)
C1: 0 D1: 0:00
C2: 2 D2: 2:12
I would like to have E2 = 50:12
The C columb is days, and D is hours.
Somehow I cannot demonstrate a simple 2*24 + 2:12 = 50:12
Maybe due to the fact that the C is a number cell and D is time
formatted.

-----------
To address the proposed solutions thus far:

=ROUND(((A2+B2)-(A1+B1))*24,0)
gives me 50.0 if I format as number. and 0:00 if I format as HH:MM.

=(A2+B2)-(A1+B1) gives me 2:22 if formatted as h:mm (Or 2.10 if
formatted as a number).

=((A2+B2)-(A1+B1))*24
gives me 8:48 if h:mm
or 50.37 if generall number. (This is the closest solution yet, but I
don't know how 50:12 looks like 50.37, as the 37 is not minuts or
100's)

Thanks,
Crzzy1
---------

On Jan 14, 12:55*pm, "T. Valko" wrote:
Using your original sample data:

.............A..................B....
1...12/17/2008........13:23
2...12/19/2008........15:35

Tell us what results you expect and in what cell(s) should the results
appear.

--
Biff
Microsoft Excel MVP



Good point, that looks much clearer.
Everything on my chart is given with the exception of "E"
C is the number of 24 hour periods,
D is the hours beyond that that are less than 24 hours.
for line one, the ticket is opened at time "0".

E is the output I am looking for.

.............A..................B.........C....... ...D.............E
1...12/17/2008........13:23.......""..........0:00 .........0
2...12/19/2008........15:35.......2...........2:12 ........50:12

Thanks.
Crzzy1

Ok, try these:

C2: Format as General or Number

=INT((A2+B2)-(A1+B1))

D2: Format as Custom h:mm

=MOD(B2-B1,1)

E2: Format as Custom [h]:mm (note the [ ] around the hours)

=(A2+B2)-(A1+B1)

--
Biff
Microsoft Excel MVP


wrote in message
...
On Jan 14, 12:55 pm, "T. Valko" wrote:
Using your original sample data:

.............A..................B....
1...12/17/2008........13:23
2...12/19/2008........15:35

Tell us what results you expect and in what cell(s) should the results
appear.

--
Biff
Microsoft Excel MVP



Good point, that looks much clearer.
Everything on my chart is given with the exception of "E"
C is the number of 24 hour periods,
D is the hours beyond that that are less than 24 hours.
for line one, the ticket is opened at time "0".

E is the output I am looking for.

.............A..................B.........C....... ...D.............E
1...12/17/2008........13:23.......""..........0:00 .........0
2...12/19/2008........15:35.......2...........2:12 ........50:12

Thanks.
Crzzy1