Format Total Time

[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(D34), =SUM(D35) 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.

 

[Bob Phillips]

You could hide zeroes,

Tools>Options>General and uncheck Zero Values.

--

HTH

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

 

[Max]

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

 

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

 

[Max]

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

E5 will remain "blank" until the the entire range D35 is filled

 

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

 

[Max]

Glad to hear you got it to work !
Thanks for the feedback ..