time again

Ima_learnin'

Active Member
Joined
Dec 14, 2002
Messages
266
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’
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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.
 
Upvote 0
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'
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,269
Messages
6,177,569
Members
452,784
Latest member
talippo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top