leave salary due finding with formula

[Dackson]

Dear Sir,
I want to find leave salary due date by formula. Leave salary calculation will be based on 365 working days, means from last leave salary pay date to add 365 days. But if any leave taken from last leave pay date to due date that will add in leave days and due will be jump. (means last pay date + 365+from last leave pay date to due date leave will be add that is my due date). My leave data from 2010 will arranged based monthly format, means maximum leave days will be end of month days. But leave posted quantity can be one or more.
My issue is leave salary due month if the employee will take one or more leave or multiple leave before leave due days and after due date in a same month. How to find due date after considering leave days before due?

Please find attachment.
Thanks & regards

Dackson Jose

MOD EDIT: Thread moved to proper section of forum.

 

[David Evans]

Good Morning Dackson -

Can you clarify "Leave Due days" and "Leave Due Date". Also what is the significance of Column N? Do you expect the answer in Column M (blue) to appear in Column K (Yellow)?

 

[Dackson]

Good Morning Dackson -

Can you clarify "Leave Due days" and "Leave Due Date". Also what is the significance of Column N? Do you expect the answer in Column M (blue) to appear in Column K (Yellow)?

Hi,
Thanks for your response
I prepared an example for you. Please look in to the attachment.


Thanks and regards

Dackson Jose

 

[Faseeh]

Can you provide an example of how you do calculation with reference to this sheet with correct answer you are expecting. ?? This will really help understand your problem.

 

[Dackson]

Hi,
Kindly open above TEST2.xls Attachment for example.

Thanks for your response

 

[David Evans]

Sorry Dackson, but there's something in the logic of Column U that I can't follow.
It's a convoluted calculation, and I don't understand the "WORKRD DAYS" - do employees work every day of a month, which is what it looks like ...

 

[Dackson]

Hi,
Column U taken leave days in a month
NIL Means That concern month the employee not take any leave that's way its 0.

Column U Worked days
Its Means January month he didn't take any leave worked days will be 31 days like that....

Monthly Holidays Saturdays and Sundays treating as a worked days.

Thanks for your response

 

[David Evans]

OK - I'm beginning to find a pattern in your calculation. Am I correct in divining that the 19 in Cell V23 becomes your Leave Salary Due Date? (01/19/2016)
Is there a pattern for deriving the Leave salary due Date for, say, April 2015? (Cell W23 based on the value in V23?)

 

[Dackson]

My Dear David,
My language not too well, but i think you can understand, Thanks for your co-operation
Which i mentioned in the example, one option for how to get the answer for solve this problem.
You can think any alternative way, but ultimately Answer will be 19-Jan-2016.

Kindly try other option now you can go this pattern for deriving the due date.
# Last leave salary pay date +365 + monthly taken leave days
# Now you will get approximately the leave salary due month.
# An employee had how many leaves in leave salary due month you can check
by row wise one by one from leave data.
# One more checking the employee leave taken in the same month before due
date or after leave due date?
# If leave taken before due date then count the leave days and add the leave
days, automatically due date will jump that days more.
# Like wise you can check one by one leave days.
# If leave taken after due date then not considered for any calculation.

I hope you are now in right track and the solution will get soon.

Thanks and regards,
Dackson