time & attendance 4

**sscssc** · 08-17-2012, 04:04 AM

Hi...
The issues are the same as in time&attendance 3 - but with some added content.

THE ERROR APPEARS WHEN I HAVE NO ATTENDANCE DATA OR WHEN I HAVE ONLY ENTRANCE AND NO EXIT OR ONLY EXIT AND NO ENTRY OR BOTH.
I NEED THE FORMULA TO IGNORE THE ROWS WHERE I HAVE NO ENTRY OR NO EXIT OR BOTH.

This was the issue in attendance 3:
"I NEED THE FORMULA TO IGNORE THE ROWS WHERE I HAVE NO ENTRY OR NO EXIT OR BOTH.
Here I come again - same problem - different data from another control acces system.
I need to extract from the sheet 1 data table - the daily worked time (difference between clock in and out columns) marked in green cells for one month to the sheet 2 table.
I need to match the name and the date and the value from green cell ofcourse!

Thanks to Bob Phillips who suggested this formula:

=SUMPRODUCT(--(Sheet1!$D$2:$D$13=$C10),--(DAY(Sheet1!$E$2:$E$13)=V$9),--(Sheet1!$J$2:$J$13-Sheet1!$I$2:$I$13))

It works great but gives error when i have no exit or no entry or both no exit and no entry (in my original data this kind of error was no present so the formula was designed to work with that particular table data).

**Bmoe** · 08-17-2012, 06:59 AM

My very first thought, and I hate to be that guy, is you should look at a database solution for this. I've seen far too many payroll, inventory and attendance workbooks that get so complicated after a few years of use, you need an excel PhD to work on them.

You can probably insert =ISBLANK() wrapped in an =IF() that will output something that shuts the formula off like a - number. Then you wrap the whole thing in another if to return a blank or a 0 if the number is negative, but it looks convoluted enough as it sits!

**OnlySteveH** · 08-17-2012, 07:07 AM

I'd agree - with Bmoe's suggestion. If we're doing this in the cells, i.e. not using VBA, then testing the cells with an =ISBLANK() seems sensible.

As there's two cells, try:

=IF(OR(ISBLANK(A2), ISBLANK(B2)), "", long forumla here)

So, if either cell is blank, it does nothing, else it calculates the formula from Bob Phillips.

Is that a workable solution for you, sscssc?

HTH.

S.

**sscssc** · 08-17-2012, 08:40 AM

That is great but it does not work...still #value! returns...perhaps because i know so little about excel...
this is the formula i used based on your advice:
=IF(OR(ISBLANK(Sheet1!I2), ISBLANK(Sheet1!J2)), "", SUMPRODUCT(--(Sheet1!$D$2:$D$139=$C10),--(DAY(Sheet1!$E$2:$E$139)=G$9),--(Sheet1!$J$2:$J$139-Sheet1!$I$2:$I$139)))
where do i go wrong?

**sscssc** · 08-17-2012, 09:29 AM

strange...it seems that although there is nothing written in the cell, the cell is not blank ...only when i press delete on that cell i get the true result to isblank formula on the i and j columns!

**kvsrinivasamurthy** · 08-17-2012, 11:11 AM

Pl see the attached file with formula in 45-50 rows.
Tested for blank cells in I & J columns of Sheet1.
(In original file we have to delete the contents in blank cell also?).