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