If Present days are Zero then = Zero paid days (which is not working)

[pipsmultan]

Hi,

I have situation here with our salary sheet.
The concerning issue is, if present days are Zero in E14 an Earned day is 1 in J14 as employee has One Leaves
Allowed G14, the Salary Paid showing in AG14 should be zero then, as because present days are Zero in E14. which is not working

Employee has One Leaves Allowed in G14 because of that employee get one day extra wages. But this should not be True if present days are Zero in E14.

But If present days are not zero and Earned day is 1 in J14 then Salary Paid should be Piad by calculating total present days & Earned days

Please see the attach image.

error.JPG

Formula i'm using in AG14:

Please Login or Register  to view this content.

Please help to fix this issue.

Thanks

Best,

[AliGW]

Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[pipsmultan]

Dear, AliGW
Thank you for your reply, and hope you had a great weekend :-)

Please find the request attachment of small sample Excel workbook.

Salary Emp.xlsx

The only thing which we can't find solution is ''if present days are Zero in E14 an Earned day is 1 in J14 as employee has One Leaves
Allowed G14, the Salary Paid showing in AG14 should be zero then (Employee should get paid), as because present days are Zero in E14''. More details please see P#1

Thank you and looking forward.

Best,

[pipsmultan]

Hi,

Looking forward for any help on this.

Thank you.

Best,

[AliGW]

From your description, then this:

=IF(E14=0,0,$AB14-T14-($AD14*$N14)+X14)

[pipsmultan]

Hi Ali,
Thank you for rely.
I tried to put the formula in J14, but it gives an error. Please see the attach image.

Attachment 637009

Best,

[AliGW]

It was not meant for J14.

Try this in E14:

=IF(E14=0;0;E14-$F14+($G14="Yes")+$H14-$I14)

Use semi-colons (not commas) for your European locale.

[pipsmultan]

Hi Ali,

Work like a charm thank you and also thanks for guidance to ''Use semi-colons (not commas)'' for your European locale

Is it possible to make this formula little more amenable like, i.e those employee who has work for 15 or more days get this 'One Leaves Allowed' facility, else who work less then 15 days will get on working days salary NO 'One Leaves Allowed' facility.

Best,

[AliGW]

Yes, I'm sure you can add that to the formula. You will need to refer to the column that holds the days worked and apply an IF statement. Something like this:

Days leave allowed: =IF(days_worked<15;0;calculation_of_leaves_allowed)

Over to you to give it a go. Let us know if you get stuck.

[pipsmultan]

Hi Ali,

Amazing, work as great

I would like to thank you from my heart for helping me out and also making the formula more flexible.

Highly appreciated your time and effort you have put in.

wish you a great day.

Thread marked as Solved & Reputation is added

Best,

[AliGW]

Thanks very much - glad to have helped.