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

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

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

Dear Arno,
I have received your reply. it is good if you count the # of quarters. But
let's discuss this way.

if an employee's stard date is 15 February (B2) the result should return 4
Quarter, but he formula you have adviced me is howing something like this.

1 sep 05 (B2) = 3 quarter, which I want the contrary for example

if it is between Oct and Dec it should return me 1
if it is between Jul - Sep it should return me 2
if it is between Apr - Jun it should return me 3
if it is between Jan - Mar it should return me 4

sorry for wrong info I wrote in my first message.
if you are online, let's discuss this online by skype (daoudfakhry1985)

Thanks
Daoud

"arno" wrote:

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

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

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

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

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

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-INT((MONTH(D2)-1)/3)+1
ahhhhhh, sorry

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

thanx for correcting :)

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?

"arno" wrote:

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

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

--


Regards,


Peo Sjoblom

"Compben" wrote in message
...
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?

"arno" wrote:

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

Try this:

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



--
Biff
Microsoft Excel MVP


"Compben" wrote in message
...
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?

"arno" wrote:

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

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

"Peo Sjoblom" wrote:

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

--


Regards,


Peo Sjoblom

"Compben" wrote in message
...
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?

"arno" wrote:

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