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

On Jan 15, 11:58*am, "T. Valko" wrote:
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

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

Thank you Biff,
Hey that seems to have fixed it.
=(A2+B2)-(A1+B1) didn't seem to work for me the first time I tried
it, sorry.
:Maybe I did forget the "[" "]" on the [h];mm.

anyways, thanks.

now I have this.. with the correct time on my tickets thanks to you.
(I included a little logic in the F columb, also that includes your
formula)
=IF(E6="Opened",0,IF(E5="Opened",(C6+D6)-(C5+D5),IF(E4="Opened",
(C6+D6)-(C4+D4),IF(E3="Opened",(C6+D6)-(C3+D3),"error"))))

a b c d e f
10006 jfpi 6/21/2005 23:47 Opened 0:00
10006 jfpi 6/22/2005 4:13 Picked 4:26
10006 jfpi 6/22/2005 4:15 Approved 4:28
10006 jfpi 6/23/2005 4:39 Complete 28:52
10009 amek 6/22/2005 8:43 Opened 0:00
10009 amek 6/22/2005 8:44 Picked 0:01
10009 amek 6/22/2005 8:49 Approved 0:06
10009 amek 6/22/2005 8:51 Complete 0:08
10010 carp 6/22/2005 8:51 Opened 0:00
10010 carp 6/22/2005 8:59 Picked 0:08
10010 carp 6/22/2005 9:02 Approved 0:11
10010 carp 6/22/2005 9:32 Complete 0:41

If I may dare,,,,
Is there a way I can show the average time for all tickets complete?

Thanks again.

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

Try one of these:

This is an array formula** :

=AVERAGE(IF(E2:E13="complete",F2:F13))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Or, this normally entered formula:

=SUMIF(E2:E13,"complete",F2:F13)/COUNTIF(E2:E13,"complete")

Format either as [h]:mm

--
Biff
Microsoft Excel MVP


wrote in message
...
On Jan 15, 11:58 am, "T. Valko" wrote:
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

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

Thank you Biff,
Hey that seems to have fixed it.
=(A2+B2)-(A1+B1) didn't seem to work for me the first time I tried
it, sorry.
:Maybe I did forget the "[" "]" on the [h];mm.

anyways, thanks.

now I have this.. with the correct time on my tickets thanks to you.
(I included a little logic in the F columb, also that includes your
formula)
=IF(E6="Opened",0,IF(E5="Opened",(C6+D6)-(C5+D5),IF(E4="Opened",
(C6+D6)-(C4+D4),IF(E3="Opened",(C6+D6)-(C3+D3),"error"))))

a b c d e f
10006 jfpi 6/21/2005 23:47 Opened 0:00
10006 jfpi 6/22/2005 4:13 Picked 4:26
10006 jfpi 6/22/2005 4:15 Approved 4:28
10006 jfpi 6/23/2005 4:39 Complete 28:52
10009 amek 6/22/2005 8:43 Opened 0:00
10009 amek 6/22/2005 8:44 Picked 0:01
10009 amek 6/22/2005 8:49 Approved 0:06
10009 amek 6/22/2005 8:51 Complete 0:08
10010 carp 6/22/2005 8:51 Opened 0:00
10010 carp 6/22/2005 8:59 Picked 0:08
10010 carp 6/22/2005 9:02 Approved 0:11
10010 carp 6/22/2005 9:32 Complete 0:41

If I may dare,,,,
Is there a way I can show the average time for all tickets complete?

Thanks again.

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