time again

[Ima_learnin']

Hello All,
This is bugging me….I have two cells that have data that represents an amount of hours and minutes, but the cells need to be formatted as “General”….the contents of the cells appear as follows D10 has 0003972:12 and D11 has 0003563:31. All I need to do is sum the two cells as a decimal equivalent and divide by 8

I tried this just to see if I could sum the two cells, but it’s not working.
=TEXT(SUM(D10:D11),"0.00")

thanks, Ima Learnin’

 

[fairwinds]

Hi,

=DOLLARDE(SUBSTITUTE(D10,":","."),60)+DOLLARDE(SUBSTITUTE(D11,":","."),60)

Will sum them as decimal values.

To have a time value divide with 24 and format as time.

 

[Ima_learnin']

Whoa! Thanks...I've seen the Dollarde function before..thanks again!!
Ima Learnin'

 

[Ima_learnin']

Sorry to bother you, but is there a way to do a range of cells without doing the individual steps for each cell?

thanks, Ima Learnin'

 

[fairwinds]

I could not get DOLLARDE to work in an array but you could do:

=SUM(MOD(SUBSTITUTE(A1:A10,":","."),1)/0.6+INT(SUBSTITUTE(A1:A10,":",".")))

Ctrl + Shift + Enter

or:


=SUMPRODUCT(MOD(SUBSTITUTE(A1:A10,":","."),1)/0.6+INT(SUBSTITUTE(A1:A10,":",".")))

Normal Enter

 

[Ima_learnin']

whoa...amazing!

thanks!!