You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

- Thread starter PendekarMakao
- Start date

So you attach a MASSIVE workbook that takes ages to open and then expect us to reverse engineer a hugely long and complex formula with no explanation from you as to what it is meant to be doing? You're having a laugh! I don't think so.

1. Provide a SMALL sample workbook that does not require zipping (copy and paste relevant material into a new workbook and share that).

2. Explain the logic of the formula and the calculation it is meant to be making.

3. Give us some expected results (manually calculated).

Actually the problem is coming from this formula only

AND(TEXT(L$1;"dddd")="Jumat";VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!O4)-COLUMN(Schedule!$I4))+3;0)="out";

I am trying to calculate working hours spent by the employees, exclude overtime work. The working schedule sheet shows entry (column In) and leave time (column Out), sometimes it also includes overtime work carried out by the employees. That is why I also need to check the OT column to see whether they have overtime work at the beginning of their shift (column OT=out) or at the end of their shift (column OT=in).

On Friday, if the employee comes or leaves for work at certain times (indicated on sheet "In Out"), the working hours spent will be added for another 30 minutes (sheet Friday). For example, this formula should return with 08:30 value.

I have put all of the logic that I want to test, but the value returning keep shows #N/A, especially on Friday.

Could you please tell me where the problem is?

Thank you

Hi Ali, as I said. Manually calculated, it should return with 8:30 value. --> sorry my mistake, it should return with 9.30.

Because AND(TEXT(L$1;"dddd")="Jumat" is true.

VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!O4)-COLUMN(Schedule!$I4))+3;0)="out" is also true.

**AND(VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!N4)-COLUMN(Schedule!$H4))+2;0)=8 is true.**

VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!L4)-COLUMN(Schedule!$F4));0)=VLOOKUP(VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!L4)-COLUMN(Schedule!$F4));0);'In Out'!$A$3:$B$17;2;0)))) is also true.

Thank you.

Because AND(TEXT(L$1;"dddd")="Jumat" is true.

VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!O4)-COLUMN(Schedule!$I4))+3;0)="out" is also true.

VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!L4)-COLUMN(Schedule!$F4));0)=VLOOKUP(VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!L4)-COLUMN(Schedule!$F4));0);'In Out'!$A$3:$B$17;2;0)))) is also true.

Thank you.

Last edited:

One of my logical test is resulting with #N/A that is why the whole value returning with #N/A.

Thank you for your suggestion.

Regards