[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Open
  • Priority: Low
  • Security: Public
  • Views: 24
  • Last Modified:

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
0
John Sanil
Asked:
John Sanil
2 Comments
 
AlanConsultantCommented:
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

Join & Write a Comment

Featured Post

Get your problem seen by more experts

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now