Not rounding when I do not want to display 0's

  • Thread starter Melissa_payroll
  • Start date
M

Melissa_payroll

I have an Excel timesheet that I don't want to display the 0's in the total
hours column when there isn't any time for that day. I did the 0;-0;;@ to
make the 0's go away, but now the total hours round to a whole number when I
need it to read a dismal. Is there a way I can have both?
 
P

Pete_UK

You could apply conditional formatting to the total hours column, such
that if the cell contains zero then use a white foreground colour (on
a white backgound) so that the cell appears empty. You can then apply
a decimal format for numbers that are not zero.

Hope this helps.

Pete
 
M

Melissa_payroll

I put in the time from 8:30-15:45 and instead of it giving me 7.25 it gave me
174:00. Any other suggestions?

Jacob Skaria said:
Try

[h]:mm;;"";@

--
Jacob


Melissa_payroll said:
I have an Excel timesheet that I don't want to display the 0's in the total
hours column when there isn't any time for that day. I did the 0;-0;;@ to
make the 0's go away, but now the total hours round to a whole number when I
need it to read a dismal. Is there a way I can have both?
Click to expand...
Click to expand...
 
M

Melissa_payroll

That sounds like a great idea, however, how do you do conditional formatting?
:)
 
G

Gord Dibben

Have you tried Excel help?

You can access from Help on menu in 2003 or by clicking on the ? in 2007


Gord Dibben MS Excel MVP
 
D

David Biddulph

If you multiplied the time by 24 to get an answer in decimal hours, then you
don't want to format as hours and minutes. You should format it as General
or Number.

If you want to se it as an Excel time in hours and minutes, then don't
multiply it by 24.
--
David Biddulph

Melissa_payroll said:
I put in the time from 8:30-15:45 and instead of it giving me 7.25 it
gave me 174:00. Any other suggestions?

Jacob Skaria said:
Try

[h]:mm;;"";@

--
Jacob


Melissa_payroll said:
I have an Excel timesheet that I don't want to display the 0's in
the total hours column when there isn't any time for that day. I
did the 0;-0;;@ to make the 0's go away, but now the total hours
round to a whole number when I need it to read a dismal. Is there
a way I can have both?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Having a 'not applicable' with a 0 ranking on a rating question 0
How to disable Alt+s in Outlook 365 0
Outlook meeting location auto prompts with a suggestion list - need to disable 0
Text in note pane of "normal" display suddenly much larger 1
Sharepoint not capturing staff name when automation runs 0
My calendar keeps returning to working hours of 08:00h to 17:00h even after I set it to 24h? 1
Two Toggle Buttons to calculate Day(s) Hour(s) Minute(s) between entry time 1
How not to display null values (0's and/or #DIV/0!'s? 2

Top