| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How should I get the # of quarters in a year.
Mr./Mrs. Stefi,
thanks for your reply, if I consider E2 as join date (1 sep 05) and D2 as pay date (31 Dec 05) it returns me (-1). I think the contrary of this formula will work as following: =INT((D2-E2)/91)+1 and can't we put 90 instead of 91? and also if we remove the last +1 it doesn't work, cause I have tried 1 Jan as start date and 31 dec 05 as pay date then it returned me 5. Thanks, Daoud "Stefi" wrote: Consider using this formula! It's not as exact as you required (because it assumes 91 days for all quarters) but it is fairly simple. =INT((E2-D2)/91)+1 where E2 join date D2 pay date Regards, Stefi Ra €˛Daoud Fakhry€¯ ezt Ć*rta: Hi all, We are paying the health benefit to our employee on a quarterly basis (calender base). We considered that if an employee joined the company between 1 Jan and 31 Mar we will count it 1 quarter and the same till end of the year. Our employee is eligible for $1,000/year or $250/quarter. I want the following to be calculated in a formula: if the employee hire date is between 1 Jan to 31 Mar it should give me 1st Quarter if the employee hire date is between 1 Apr to 30 Jun it should give me 2nd Quarter if the employee hire date is between 1 July to 31 Aug it should give me 3rd Quarter if the employee hire date is between 1 Sep to 31 Dec it should give me 4th Quarter or it will be better to calculate the # of quarters between the hire date and the benefit payment base date. Or I am currently in December 2006 and want to calculate our employees health benefit to calculate the # of quarters between the employee hire date and 31 dec 06? Thanks for all of your cooperations, Best, Daoud Fakhry |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How should I get the # of quarters in a year.
Sorry, I mixed up column headings: correctly
where D2 join date E2 pay date If you can apply 90 day quarters, then you can use this formula: =CEILING(DAYS360(D2,E2)/90,1) (See XL Help on function DAYS360, American vs European usage) Regards, Mr. Stefi €˛Daoud Fakhry€¯ ezt Ć*rta: Mr./Mrs. Stefi, thanks for your reply, if I consider E2 as join date (1 sep 05) and D2 as pay date (31 Dec 05) it returns me (-1). I think the contrary of this formula will work as following: =INT((D2-E2)/91)+1 and can't we put 90 instead of 91? and also if we remove the last +1 it doesn't work, cause I have tried 1 Jan as start date and 31 dec 05 as pay date then it returned me 5. Thanks, Daoud "Stefi" wrote: Consider using this formula! It's not as exact as you required (because it assumes 91 days for all quarters) but it is fairly simple. =INT((E2-D2)/91)+1 where E2 join date D2 pay date Regards, Stefi Ra €˛Daoud Fakhry€¯ ezt Ć*rta: Hi all, We are paying the health benefit to our employee on a quarterly basis (calender base). We considered that if an employee joined the company between 1 Jan and 31 Mar we will count it 1 quarter and the same till end of the year. Our employee is eligible for $1,000/year or $250/quarter. I want the following to be calculated in a formula: if the employee hire date is between 1 Jan to 31 Mar it should give me 1st Quarter if the employee hire date is between 1 Apr to 30 Jun it should give me 2nd Quarter if the employee hire date is between 1 July to 31 Aug it should give me 3rd Quarter if the employee hire date is between 1 Sep to 31 Dec it should give me 4th Quarter or it will be better to calculate the # of quarters between the hire date and the benefit payment base date. Or I am currently in December 2006 and want to calculate our employees health benefit to calculate the # of quarters between the employee hire date and 31 dec 06? Thanks for all of your cooperations, Best, Daoud Fakhry |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How should I get the # of quarters in a year.
Thanks Mr. Stef,
It works but I should do some dates manually (30 Mar it returns me 3 qtr), anyway I appreciate your efforts replying me. Cheers, Daoud Fakhry "Stefi" wrote: Sorry, I mixed up column headings: correctly where D2 join date E2 pay date If you can apply 90 day quarters, then you can use this formula: =CEILING(DAYS360(D2,E2)/90,1) (See XL Help on function DAYS360, American vs European usage) Regards, Mr. Stefi €˛Daoud Fakhry€¯ ezt Ć*rta: Mr./Mrs. Stefi, thanks for your reply, if I consider E2 as join date (1 sep 05) and D2 as pay date (31 Dec 05) it returns me (-1). I think the contrary of this formula will work as following: =INT((D2-E2)/91)+1 and can't we put 90 instead of 91? and also if we remove the last +1 it doesn't work, cause I have tried 1 Jan as start date and 31 dec 05 as pay date then it returned me 5. Thanks, Daoud "Stefi" wrote: Consider using this formula! It's not as exact as you required (because it assumes 91 days for all quarters) but it is fairly simple. =INT((E2-D2)/91)+1 where E2 join date D2 pay date Regards, Stefi Ra €˛Daoud Fakhry€¯ ezt Ć*rta: Hi all, We are paying the health benefit to our employee on a quarterly basis (calender base). We considered that if an employee joined the company between 1 Jan and 31 Mar we will count it 1 quarter and the same till end of the year. Our employee is eligible for $1,000/year or $250/quarter. I want the following to be calculated in a formula: if the employee hire date is between 1 Jan to 31 Mar it should give me 1st Quarter if the employee hire date is between 1 Apr to 30 Jun it should give me 2nd Quarter if the employee hire date is between 1 July to 31 Aug it should give me 3rd Quarter if the employee hire date is between 1 Sep to 31 Dec it should give me 4th Quarter or it will be better to calculate the # of quarters between the hire date and the benefit payment base date. Or I am currently in December 2006 and want to calculate our employees health benefit to calculate the # of quarters between the employee hire date and 31 dec 06? Thanks for all of your cooperations, Best, Daoud Fakhry |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How should I get the # of quarters in a year.
Hi Daoud
I think Arno's second posting gives you the correct answer, if you modify it by inserting an extra set of brackets. =5-INT((MONTH(D2)-1)/3)+1 should be =5-(INT((MONTH(D2)-1)/3)+1) -- Regards Roger Govier "Daoud Fakhry" wrote in message ... Thanks Mr. Stef, It works but I should do some dates manually (30 Mar it returns me 3 qtr), anyway I appreciate your efforts replying me. Cheers, Daoud Fakhry "Stefi" wrote: Sorry, I mixed up column headings: correctly where D2 join date E2 pay date If you can apply 90 day quarters, then you can use this formula: =CEILING(DAYS360(D2,E2)/90,1) (See XL Help on function DAYS360, American vs European usage) Regards, Mr. Stefi "Daoud Fakhry" ezt ķrta: Mr./Mrs. Stefi, thanks for your reply, if I consider E2 as join date (1 sep 05) and D2 as pay date (31 Dec 05) it returns me (-1). I think the contrary of this formula will work as following: =INT((D2-E2)/91)+1 and can't we put 90 instead of 91? and also if we remove the last +1 it doesn't work, cause I have tried 1 Jan as start date and 31 dec 05 as pay date then it returned me 5. Thanks, Daoud "Stefi" wrote: Consider using this formula! It's not as exact as you required (because it assumes 91 days for all quarters) but it is fairly simple. =INT((E2-D2)/91)+1 where E2 join date D2 pay date Regards, Stefi Ra "Daoud Fakhry" ezt ķrta: Hi all, We are paying the health benefit to our employee on a quarterly basis (calender base). We considered that if an employee joined the company between 1 Jan and 31 Mar we will count it 1 quarter and the same till end of the year. Our employee is eligible for $1,000/year or $250/quarter. I want the following to be calculated in a formula: if the employee hire date is between 1 Jan to 31 Mar it should give me 1st Quarter if the employee hire date is between 1 Apr to 30 Jun it should give me 2nd Quarter if the employee hire date is between 1 July to 31 Aug it should give me 3rd Quarter if the employee hire date is between 1 Sep to 31 Dec it should give me 4th Quarter or it will be better to calculate the # of quarters between the hire date and the benefit payment base date. Or I am currently in December 2006 and want to calculate our employees health benefit to calculate the # of quarters between the employee hire date and 31 dec 06? Thanks for all of your cooperations, Best, Daoud Fakhry |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How should I get the # of quarters in a year.
=5-INT((MONTH(D2)-1)/3)+1
ahhhhhh, sorry should be =5-(INT((MONTH(D2)-1)/3)+1) thanx for correcting :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vacation Time calculation... HELP! | Excel Discussion (Misc queries) | |||
| Lookup returns wrong value | Excel Worksheet Functions | |||
| holiday dates | Excel Worksheet Functions | |||
| how do I convert a dates in a year quarters in a year? | Excel Discussion (Misc queries) | |||
| How do I get Excel to automatically calculate salaries actually received in financial year? | Excel Discussion (Misc queries) | |||
Hybrid Mode
