Add 20 on the hiring date of an employee

Want to add 20 Leaves every year in an individuals account specifically on the date of his hiring in our company in two sets of dates
eg.
Cell A1 Date of of hiring :  01/07/1992 (Date format: dd/mm/yyy)

A1: 01/07/1992

        B2                           C2                             D2                       E2                        F2 (Answer Cell)
       01/01/2017         30/04/2017          01/05/2017      29/06/2017                0
       30/06/2017         30/07/2017          31/07/2017      20/12/2017                20
       21/12/2017         20/06/2018          21/06/2018      01/07/2018                20
       02/07/2018        30/10/2018          31/10/2018      02/07/2019                20

If the hiring date occurs between any of these two sets of date of any year F2 will return 20 or 0

I immensely searched the net for the answer but yet to find a solution for the problem looks unique.  Will be glade if you can give me a solution.

Thanks in advance for the solution offered.

John
John SanilAsked:
Who is Participating?
 
AlanConnect With a Mentor ConsultantCommented:
Hi John,

Not sure I have fully understood what you want, but try entering this into F2, and copy down:

=OR(AND(B2<=DATE(YEAR(C2),MONTH($A$1),DAY($A$1)),C2>=DATE(YEAR(C2),MONTH($A$1),DAY($A$1))),AND(D2<=DATE(YEAR(E2),MONTH($A$1),DAY($A$1)),E2>=DATE(YEAR(E2),MONTH($A$1),DAY($A$1))))*20

Open in new window


Does that give you the correct solution?

If not, please let me know where it goes wrong.

Thanks,

Alan.
0
 
John SanilAuthor Commented:
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
0
 
AlanConsultantCommented:
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.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
John SanilAuthor Commented:
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.
0
 
John SanilAuthor Commented:
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
0
 
AlanConsultantCommented:
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.
0
 
John SanilAuthor Commented:
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
0
 
AlanConsultantCommented:
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
0
 
AlanConsultantCommented:
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.
0
 
John SanilAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.