Error showing with Formula

[Shazz]

Can anyone help with the attached, I am getting ############## when I input Contracted Hours on the attached sheet, is there any way to stop this from happening??

Shazz
x

 

[Ashhu]

Its because all cells are not filled, E, F, L, M. Its time function so ends up with ###

 

[Shazz]

Is there any way of stopping it though, as I need to send this spreadsheet to people and it does not look very nice with it in there.

Shazz

 

[Khalid NGO]

Hi,

This happen when the cell is formatted to date / time, and having values in minus.

Try the custom format of:
h:mm;;h:mm

Note: Cell will contain the minus values, this will only hide to display the minus values.

or use the formula:
=IF(SUM(F13-(E12+L12+M12))<=0,"",SUM(F13-(E12+L12+M12)))

Regards,

Edit: SUM not necessary here
=IF((F13-(E12+L12+M12))<=0,"",(F13-(E12+L12+M12)))

If you want to display the minus values, try:
=IF((F13-(E12+L12+M12))<0,TEXT(((E12+L12+M12)-F13),"- hh:mm"),(F13-(E12+L12+M12)))

 

[Shazz]

None of the above suggestions worked.

Please see current sheet attached, please can someone advise how to make the cells calculate properly were the hours worked are less than the contracted hours or where a lunch has not been taken.

Thanks

Shazz

 

[NARAYANK991]

Hi ,

Why not use something like :

G12 : =MAX(0,F13-(E12+M12+N12))

Narayan

 

[Shazz]

Thanks Narayan that worked a treat.

 

[Shazz]

Narayan,

The overtime is not calculating correctly on the spreadsheet, are you able to advise at all?

Shazz

 

[NARAYANK991]

Hi ,

Are you talking about the individual entries or the total hours claimed ?

If it is the latter , it is just a matter of using the correct cell format , which for hours which may exceed 24 , should be :

[hh]:mm:ss

and not just :

hh:mm:ss

Without the square brackets , Excel will show only those hours which remain when the value is divided by 24.

Narayan