Converting time formatted cells

[Vegeta]

Hi,

I would like to convert time/hour formatted cells to days.

For example:

11:47:00
17:12:00

to

1 day, 4 hours, 59 minutes

How can I do this?

Basically I am trying to add up time formatted cells so I can calculat
the total number of days, hours and minutes from, for example cell C
to C31.

I am using Excel 2003 v11.

Thanks :

 

[Jason Morin]

I'm drawing a blank on how to do this with a shorter
formula, but this should do:

=INT(SUM(C2:C31))&" days, "&INT((SUM(C2:C31)-INT(SUM
(C2:C31)))*24)&" hours, "&ROUND(((SUM(C2:C31)-INT(SUM
(C2:C31)))*24-INT((SUM(C2:C31)-INT(SUM(C2:C31)))*24))*60,0)
&" minutes"

HTH
Jason
Atlanta, GA

 

[Peo Sjoblom]

One way

to get the day

=INT(SUM(B1:B2))

to get the time

=SUM(B1:B2)-INT(SUM(B1:B2))

or just use a custom format

dd hh:mm

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Vegeta]

*I'm drawing a blank on how to do this with a shorter
formula, but this should do:

=INT(SUM(C2:C31))&" days, "&INT((SUM(C2:C31)-INT(SUM
(C2:C31)))*24)&" hours, "&ROUND(((SUM(C2:C31)-INT(SUM
(C2:C31)))*24-INT((SUM(C2:C31)-INT(SUM(C2:C31)))*24))*60,0)
&" minutes"

HTH
Jason
Atlanta, GA
*

When adding this to a cell I just get *#NAME?

 

[Dave Peterson]

And I used a custom format of:

d "Day(s)" h "Hour(s)" m "minute(s)"

(if you're not too picky, it might be sufficient)

 

[Dave Peterson]

I think there were some funny characters in the post.

This might work better (same formula):

=INT(SUM(C2:C31))&" days, "&INT((SUM(C2:C31)-INT(SUM(C2:C31)))*24)
&" hours, "&ROUND(((SUM(C2:C31)-INT(SUM(C2:C31)))*24
-INT((SUM(C2:C31)-INT(SUM(C2:C31)))*24))*60,0)&" minutes"

If it doesn't work, try deleting the extra spaces between SUM & (c2:c31
(at each line break)

When I did, Jason's formula worked fine.

 

[Vegeta]

Thanks for the original formula Jason and the fixed formula Dave, i
works :

 

[Guest]

I face this problem before. The solution I used is as follow

A2 = May 14, 2004 11:23:00 P
B2 = May 14, 2004 12:30:00 P
C2 = Total Duration between 2 time

In the Macro
Sub TestData1()
'if your data is text base, then you may change the text to date forma
' Loop to cycle through each cell in the specified range
'For Each x In Range("A2:A30"
For Each x In Range("B2:B30"
x.Value = FormatDateTime(x.Value, vbGeneralDate
Nex
End Su

Step 2:Calculatio
C2 = Text(B2-A2,"d h:mm:ss"
you can get "day hour:minute:second

I hope this will help.. :0


----- Dave Peterson wrote: ----

And I used a custom format of

d "Day(s)" h "Hour(s)" m "minute(s)

(if you're not too picky, it might be sufficient

 

[LouisR]

Simpler way: use Excel's serial numbers for times and dates, and sho
the result in whatever format you see fit.

Input:
11:47:00
17:12:00

In either standard or hh:mm:ss format

On the cell you want your total written, given the hours in A1 and A2:
SUM(A1:A2)

Give this cell this format:
d "day" h "hours" m "minutes"

Done!

Loui