Format Total Time

A

aviator327

I have a spreadsheet to keep track of some part time work. A friend of mine
formatted the reference cell,Total time,Month to date and formatted the
remaining cells as, =sum(D3:D4), =SUM(D3:D5) and so on in the Month to date
column but all cells in that column display a 0 until I imput a time and all
cells will now display the time I entered, as an example it I type in 3.5
hrs in the Time column, 3.5 hours will now be displayed in all cells
including Total Hours. I am new to using Excel and was wondering if there is
a formula I could enter in the reference cell so I won't be showing all
zeros in the cells when I format the remaining cells? There are 31 cells in
the Month to Date column all showing zeros now. Would appreciate any
assistance. Thanks.
 
B

Bob Phillips

You could hide zeroes,

Tools>Options>General and uncheck Zero Values.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
M

Max

One way is to suppress extraneous zeros
from showing in the sheet via:
Tools > Options > View tab > Uncheck "Zero values" > OK
 
A

aviator327

Thanks guys. Did as you suggested by unchecking Zero values and the 0's
disappeared in all cells but as soon as I type in a number in the time
column; ex; 2.5 I now have 2.5 in all the cells in the month to date column.
Can't figure out how to hide the values in the unused cells. Thanks.
 
M

Max

Instead of say, in E5: =SUM(D3:D5)
perhaps try something like:
In E5: =IF(COUNTBLANK(D3:D5)<>0,"",SUM(D3:D5))

E5 will remain "blank" until the the entire range D3:D5 is filled
 
A

aviator327

Max, I applied your formula to the remaining cells in Column E (Month to
Date) and it worked. I now show a blank range in the cell until I put a
number in and all cells remain blank except for the Total Hours at the
bottom which is exactly what I want. Thanks a million for your help.
 
Top