Add 20 on the hiring date of an employee

Dear Alan,

First of all let me say Thank you for the marvellous work you've done.  It has given me lot of relief.
Sorry Alan I could not elaborate my question and being so dumb.  Hope you understand.
Let me explain.
A1 is the hiring date of the employee where every year 20 leaves will be added to his/her credit on their joining date in the company.
B2 and C2 is the duty period he gives in to his service and D2 and E2 is the leave period. Since his joining anniversary in our company can reflect in between B2 and C2 (the duty period) or D2 and E2 (the leave period) leave have to be added accordingly.  There are certain time
an employee continues to be on leave by intimating later so that period will not be reflected in the duty period and due to this B2 and C2 will show (-).  Hope I am more clear now :)

I have  just one more question before I could set this for the 70 employees in my organisation. Hope I am not asking for more (definitely you have a right to ask me for glass full of Jack Daniel  :) .
 
Previously I have given 4 possibilities to add the 20 leaves, there are two more possibilities which I tried to solve with Iferror but could not make it.
If you could solve this it would be great.  The first D2 and E2 could have (-) since I have to submit the leaves to all the employees their leave
balance as on 31st December of every year (for eg. B2 will have the last duty period say 20/06/2018 and C2 will have 31/12/2018 and hence D2 and E2 will have only (-) and not blank or 0).  Second possibility the employee continues to be on leave so the duty period (B2 & C2) will have (-) into it and not blank or 0.

A1: 01/07/1992 (the joining period)

      - The Duty period -                           - The Leave Period -
 B2                           C2                             D2                       E2                        F2 (Answer Cell)
29/06/2018        31/12/2018                   -                           -                          20
or
           -                       -                         01/01/2019        02/07/2019             20

Its fantastic to be working with you on this problem.  If you could give me your email, would send you a sample file for your Idea and observation.  

Warm Regards,

John

Hi John,

Rather than emailing to me, you can attach it here.

If there is confidential info, such as names, just delete it (or change) and leave the part where you have the calculation you need.

I take it that the formula I gave above does not work for you?

Alan.

Hi Allan,

Let me tell you, you have already answered my question so you can take it that you have already completed the task of answering my question. Since I overlooked on my requirement, I raised the second question and included an explanation how to go about it. The formula you sent me is already 95% complete, just I needed the dashes into it which you did not knew.  Shortly I will attach the edited sample file for your view.

Thanks for your prompt response.

Regards,

John.

Hi Alan,

Been a while, was not in touch with you.  Was working on a perfect file to be attached so that you understand what I wanted.

The file is attached for your view and understanding.

Let me know about it and sorry for the delay.

Regards,

John
ZZZ-Add---20-HPL-Formula.xlsx

Hi John,

Please can you confirm which cell(s) in your workbook are not working as you would like, and what they should be doing in instead?

Thanks,

Alan.

Dear Alan,
I've re-attached the file now including the formula which you sent me. The formula lies in Cels G16 to G25.
Except for the following issues, your formula works marvelously.
1. CellG16 shows an error if any of Cell B16 through C16 and I16 through J16 contains a "-".
2. In our organisation 20 HPL credit is given to an employee only if he has completed 1 year in service so G16 (in yellow) should show "0".  20 HPL is provided on the anniversary date of the employee joining our company. (Alan it does not matter if you are not able to solve this, I will directly add a 20 in this cell, since I feel I am already taxing your brains). Sorry about it.
3. I want the cell G16 to show 20 or 0 instead of " #value " since the corresponding cells are depended on this row G.  Cells G16 immediately shows 20 only if B16,C16  and Cells I16,J16 has some value. If these cells have "-" then G16 shows error.  Also Cell G17 (in orange) should show "0" instead of "20" since 20 HPL has already been provided at Cell G16.  
4. Cells G19, G20, G22, G24 and G25 (in blue colour) has worked in perfection.  Thanks.

Would request you to solve this issue

John.
ZZZ-Add---20-HPL-Formula---1.xlsx

Hi John,

Apologies for not responding more quickly.

I've re-attached the file now including the formula which you sent me. The formula lies in Cels G16 to G25.
Except for the following issues, your formula works marvelously.
1. CellG16 shows an error if any of Cell B16 through C16 and I16 through J16 contains a "-".

I am not sure what scenario would see any of those cells contain a "-" as they are all dates?

However, I have amended G16 (and the cells below it in Column G) to show nothing if any of those cells (or the corresponding rows) have a zero in them (I am guessing you mean zero rather than dash?)

2. In our organisation 20 HPL credit is given to an employee only if he has completed 1 year in service so G16 (in yellow) should show "0".  20 HPL is provided on the anniversary date of the employee joining our company. (Alan it does not matter if you are not able to solve this, I will directly add a 20 in this cell, since I feel I am already taxing your brains). Sorry about it.

I have amended so that if the employee has not worked for at least one year, then they get a zero 'HPL'.

3. I want the cell G16 to show 20 or 0 instead of " #value " since the corresponding cells are depended on this row G.  Cells G16 immediately shows 20 only if B16,C16  and Cells I16,J16 has some value. If these cells have "-" then G16 shows error.  

Done

Also Cell G17 (in orange) should show "0" instead of "20" since 20 HPL has already been provided at Cell G16.  

I don't understand this one - why should it be zero?  It looks like it should be 20 to me, but I guess I am misunderstanding?

4. Cells G19, G20, G22, G24 and G25 (in blue colour) has worked in perfection.  Thanks.

Thanks,

Alan.
EE-29083764-Version4-ZZZ-Add---20-H.xlsx

Author indicated that this solved their posted question.  No further response on follow-up, but if there is further assistance required, by all means post a new question, and reference this one if necessary.

Dear Alan,

Sorry, very sorry, I responded so late.
got little busy in a family function could not attend your question.

I went through the file, you were right the 20 should be there in G17 cell but now I find the blue highlighted cells where 20 should be shown is
now "0" instead of 20.  I do not know what went wrong, your formulas are there in its place. I enclose the file which you sent me.  I think ones
this is done this case will be resolved.  Thank you very much for your attention and sorry ones again for the delay.

Warm Regards,

John.
Final---EE-29083764-Version4-ZZZ-Ad.xlsx