Returning Nil

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

Hello
I have a timesheet in excel, I would like the final column to work out the
overtime per day which is over 7 hours.

I've entered the formula: =I6-$H$4
I6 is the cell that has the total hours worked per day, in decimals. ie 7.75
H4 contains Number 7.
And this works ok.
BUT if the timesheet hasn't been filled the formula returns -7

What is the formula that returns nil or null if the row hasn't been filled?

BTW in I6 there is the formula: =HOUR(H8)+(INT((MINUTE(H8)+7)/15))/4

Thank you
Jen
 
J,

MAX is an Excel function that returns the larger of two (or maore values). In this case, it returns
0 when no overtime is worked, or the actual overtime (which is a number larger than zero) when
overtime is worked.

HTH,
Bernie
MS Excel MVP
 
Hi,

You can round you times to the nearest 15 minutes using

=MROUND(H8,1/96) (this replace the I6 formula)

The MROUND function is found in the Analysis ToolPak - choose Tools, Add-in,
and check Analysis ToolPak.

You can leave the entire calculation in time if you want by entering 7:00 in
H4. Or if you want to work with decimals

=24*(MROUND(I6,1/96)-H4)

If this suggestions help, please click the Yes button.

Cheers,
Shane Devenshire
 
Use a logic test:-

=IF(I6-$H$4=-7,"",I6-$H$4)

Basically, if the result is -7 then display nothing"" otherwise do the
formula.

Cheers,
Alan
 
Back
Top