Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recognizing specific dates with formula
Dear all,
I have a list of employees with a hire date and I need to calculated thier benefit considering thier hire date. I need the formula to return me the # of months from thier hire date to a specific date that I assign for it. The tricky part is that I want to have the # of months in rounded fractions, for example if an employee hire date is 22 Dec 06 and we pay the benefit 22 July 2007 then it should return me 7 months and if an employee hire date is 6 Jan 07 it should return me 6.5 months. See below the month start date to mid month and from mid month to month end. conditions are below: If the hire dates are in the first half it should return me full month and if the hire date is in the second half of the month (second part) then it should return me 0.5 momth. month first part month second part 22Dec06-5Jan07 6Jan07-20Jan07 21Jan-4Feb 5Feb-19Feb 20Feb-6mar 7Mar-20Mar 21Mar-4Apr 5Apr-20Apr 21Apr-5Jun 6Jun-21Jun 22Jun-6Jul 7Jul-22Jul Is there any master to help me out. thanks in advance. Let me know if you need more clarification. Daoud Fakhry |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recognizing specific dates with formula
Dear Daoud,
You could try this: -----A1---------B1--------------C1-----Formula in C 22-Dec-06 -----21-Jul-07------7.0---- C1=ROUND(DAYS360(A2,C2)/30,1) 06-Jan-07 ------21-Jul-07------6.5---- C2=ROUND(DAYS360(A3,C3)/30,1) 06-Jan-07 ------20-Jan-07------0.5 C3=ROUND(DAYS360(A4,C4)/30,1) Hope this helps!!! "Daoud Fakhry" wrote: Dear all, I have a list of employees with a hire date and I need to calculated thier benefit considering thier hire date. I need the formula to return me the # of months from thier hire date to a specific date that I assign for it. The tricky part is that I want to have the # of months in rounded fractions, for example if an employee hire date is 22 Dec 06 and we pay the benefit 22 July 2007 then it should return me 7 months and if an employee hire date is 6 Jan 07 it should return me 6.5 months. See below the month start date to mid month and from mid month to month end. conditions are below: If the hire dates are in the first half it should return me full month and if the hire date is in the second half of the month (second part) then it should return me 0.5 momth. month first part month second part 22Dec06-5Jan07 6Jan07-20Jan07 21Jan-4Feb 5Feb-19Feb 20Feb-6mar 7Mar-20Mar 21Mar-4Apr 5Apr-20Apr 21Apr-5Jun 6Jun-21Jun 22Jun-6Jul 7Jul-22Jul Is there any master to help me out. thanks in advance. Let me know if you need more clarification. Daoud Fakhry |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recognizing specific dates with formula
Well, it is not something that I am looking for. I need a formula to return
me specific # of months considering the hire date and check all the arguements and return the correct number, maybe there we can use IF with OR, AND or some other functions to get this, is there any master who can really help me? thank. Daoud " wrote: Dear Daoud, You could try this: -----A1---------B1--------------C1-----Formula in C 22-Dec-06 -----21-Jul-07------7.0---- C1=ROUND(DAYS360(A2,C2)/30,1) 06-Jan-07 ------21-Jul-07------6.5---- C2=ROUND(DAYS360(A3,C3)/30,1) 06-Jan-07 ------20-Jan-07------0.5 C3=ROUND(DAYS360(A4,C4)/30,1) Hope this helps!!! "Daoud Fakhry" wrote: Dear all, I have a list of employees with a hire date and I need to calculated thier benefit considering thier hire date. I need the formula to return me the # of months from thier hire date to a specific date that I assign for it. The tricky part is that I want to have the # of months in rounded fractions, for example if an employee hire date is 22 Dec 06 and we pay the benefit 22 July 2007 then it should return me 7 months and if an employee hire date is 6 Jan 07 it should return me 6.5 months. See below the month start date to mid month and from mid month to month end. conditions are below: If the hire dates are in the first half it should return me full month and if the hire date is in the second half of the month (second part) then it should return me 0.5 momth. month first part month second part 22Dec06-5Jan07 6Jan07-20Jan07 21Jan-4Feb 5Feb-19Feb 20Feb-6mar 7Mar-20Mar 21Mar-4Apr 5Apr-20Apr 21Apr-5Jun 6Jun-21Jun 22Jun-6Jul 7Jul-22Jul Is there any master to help me out. thanks in advance. Let me know if you need more clarification. Daoud Fakhry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel not recognizing dates as dates | Excel Discussion (Misc queries) | |||
Recognizing Cell Formatting To Use In A Formula Or Conditional For | Excel Worksheet Functions | |||
formula for specific dates based on another date in worksheet | Excel Discussion (Misc queries) | |||
recognizing dates past End of Month | Excel Discussion (Misc queries) | |||
Excel not recognizing Dates properly. | Excel Discussion (Misc queries) |