How should I get the # of quarters in a year.

[Daoud Fakhry]

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

 

[arno]

Hi,

let's assume you have a date in B2, then the following formula will
give you the quarter based on the month of that date:

=INT((MONTH(B2)-1)/3)+1

arno

 

[Stefi]

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:

 

[Daoud Fakhry]

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

 

[arno]

ok, then use

=5-INT((MONTH(B2)-1)/3)+1

I think you should use a "when-to-start-benefit-date" and not the
actual starting date to workaround any rounding problems. eg. from day
1 to 15 you can use the 1st day of the month, if it is between 16-31
then use the first day of the next month. This will workaround problems
like "I started on 12 june and you pay the benefit on 11 december for
the whole year, so why bla bla bla...."

arno

 

[Stefi]

Sorry, I mixed up column headings: correctly

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:

 

[Daoud Fakhry]

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

 

[Roger Govier]

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)

 

[arno]

=5-INT((MONTH(D2)-1)/3)+1
ahhhhhh, sorry

should be
=5-(INT((MONTH(D2)-1)/3)+1)

thanx for correcting

 

[Compben]

i am creating a template and only need it to report a quarter if there is a
date in the cell. how can I change this formula to report 0 if there is no
date?

 

[Peo Sjoblom]

=IF(B2="",0,INT((MONTH(B2)-1)/3)+1)

--


Regards,


Peo Sjoblom

 

[T. Valko]

Try this:

=IF(COUNT(B2),INT((MONTH(B2)+2)/3),0)

 

[compben]

excellent, thank you I have been working on this for days