How do you put Days and Hours into total hours

[cozzmo1]

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

 

[JE McGimpsey]

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'

 

[David Biddulph]

=(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

 

[Gary''s Student]

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

 

[T. Valko]

Maybe this:

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

Format as [h]:mm

 

[cozzmo1]

Maybe this:

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

Format as [h]:mm

--
Biff
Microsoft Excel MVP

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

 

[JE McGimpsey]

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.

 

[T. Valko]

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

Maybe this:

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

Format as [h]:mm

--
Biff
Microsoft Excel MVP

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

 

[cozzmo1]

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.

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

 

[T. Valko]

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

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.

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

 

[cozzmo1]

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

Using your original sample data:

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

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.

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

 

[T. Valko]

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

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

Using your original sample data:

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

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.

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