Returning Nil

[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

 

[Bernie Deitrick]

=MAX(I6-$H$4,0)

HTH,
Bernie
MS Excel MVP

 

[Gary''s Student]

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

 

[JB]

Nope, that didn't work. Still shows -7.00
J

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

 

[JB]

Yeay!! It worked. Thank you
What does the max mean?
J

 

[Bernie Deitrick]

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

 

[Shane Devenshire]

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

 

[Alan Fisher]

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