Hi, I have been trying to remove zeros from a timesheet I have compiled in excel 2010 without using the predefined option to do this under Options. I therefore want to use a formula to do this which for one or two for the columns in the SS I have been successful. However, for the remaining columns I get the response "too many arguments". I have configured the SS to show regular hours or less and then any OT worked in a separate column, but I still get the zeros problem for any preceding empty values. Here is an example of the function I have tried to use where $I$8 is for a cell showing 8 hrs only, d10-c10 is for 17.00-08.00 for example and f10 is the result (I hoped). This works for a numerical value but also returns zeros in the empty cells of the F column.

Any help with this formula would be much appreciated.

Thanks

=IF($E10>$I$8,$I$8,IF($D10-$C10<8,$D10-$C10,IF($F10<1,"")))

message edited by batar

Something like this might work: =IF($E10<$I$8,"",IF($E10>$I$8,$I$8,IF($D10-$C10<8,$D10-$C10,IF(F10<1,""))))

It appears to be the I8 cell that is returning the zero, but without seeing what your sheets looks like, it is difficult.

If you could post a small sample of how your sheet looks

like this:C D E F G I 1) Heading Heading Heading Heading Heading Heading 7) 8) ?? 9) 10) ?? ?? ??So we get a better understanding of what your doing, it

would help.Read this HOW-TO which explains the use of the < PRE > tags

to align your data like the above.http://www.computing.net/howtos/sho...

MIKE

Hi Mike, I have included column letters and row numbers and also a greater idea of how the sheet looks. As you can see it is returning zeros and "value" which I haven't any idea about. I originally followed a video tutorial for calculating overtime against regulars hours in which the guy suggested for column F to use the 8.00 hr "regular" hours (or less) using a single cell with 8.00 inputted (as previously mentioned, the $I$8 cell). Hence, the F column looking like it does. I have altered the code to remove zeros etc. Hope this helps.

http://www.bing.com/videos/search?q...

Hrs = [=IF($D18-$C18,$D18-$C18,"")]

Reg = [=IF($E10>$I$8,$I$8,IF($D10-$C10<8,$D10-$C10,IF($F10<1,"")))]

Extra Hrs = [=IF($E10>$F10,$E10-$F10,"")]

B C D E F G 10 0:00 11 0:00 12 0:00 13 0:00 14 30/10/2015 0:00 15 31/10/2015 0:00 16 01/11/2015 0:00 17 0:00 18 02/11/2015 08:50:00 17:05:00 8:15 8:00 0:15 19 03/11/2015 08:45:00 17:00:00 8:15 8:00 0:15 20 04/11/2015 09:10:00 17:00:00 7:50 7:50 21 05/11/2015 09:30:00 17:00:00 7:30 7:30 22 06/11/2015 09:00:00 17:00:00 8:00 8:00 23 07/11/2015 8:00 #VALUE! 24 08/11/2015 8:00 #VALUE! 8:00 #VALUE!Richard

message edited by batar

You did not put in Column Letter or Row Numbers, so not sure what goes where....... MIKE

Just looking over your formulas and I have a few questions. On this formula: =IF($D18-$C18,$D18-$C18,"")

The structure of an IF function is:

=IF(ConditionToTest , ActionIfTrue , ActionIfFalse)

Since your Condition to Test is a math equation,

it will ALWAYS return TRUE

because D18-C18 is always True,

it can never be false,

so it will always perform the ActionIfTrue or D18-C18Was your intent to make sure the Ending Time was greater than the Start Time?

MIKE

Take a look at this page, it is very similar to what your doing and is an excellent tutorial on working with timesheets. http://www.cpearson.com/Excel/overt...

MIKE

Note on the Pearson page the 8 Hours is placed, once, on Row 2 along with the Overtime Rate.

Also , note at the bottom you can download the example sheet.MIKE

message edited by mmcconaghy

It was simply so I could return the value of the equation if TRUE and to return a value of "nothing" if FALSE, hence "". I'm not sure what your last question is asking. If you could clarify this I would be most grateful. N.B. Column C=start time, D=finish time, E=hrs, F=reg hrs, G=OT

Thanks

'm not sure what your last question is asking.In many time sheets, it is possible to work beyond 24 Hours,

IE Start Time of 8 PM with a End Time of 4 AM the following day.MIKE

Great, just clarifying. Yes this was the intention as shifts will not extend much beyond the 8 hour working day.

After you have studied the Pearson sheet, if have any further question please ask. MIKE

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History