Date Formula to Count Months

[Mark]

Hi,

Can someone help me with a simple date formula?

I need to calculate the number of months an employee has been on the
job. If they started on the 15th of the month or after, we don't
count that month as a month worked. If they leave on the 15th or
later, we DO count that as a month worked.

My result needs to be a whole number and I want to enter the start
date in one cell, the term date in another and have number of months
worked appear in another cell.

 

[Frank Kabel]

Hi
try using DATEDIF. See:
http://www.cpearson.com/excel/datedif.htm

 

[Jason Morin]

Try:

=DATEDIF(EOMONTH(A1,IF(DAY(A1)<=15,-1,0))+1,EOMONTH(B1,IF
(DAY(B1)<=15,-1,0))+1,"m")

Analysis Toolpak must be installed to use EOMONTH.
A1: Start date
B1: Term date

HTH
Jason
Atlanta, GA

 

[RagDyer]

See if this works for you:

=IF(AND(DAY(A2)<15,DAY(B2)<15),DATEDIF(A2,B2,"m")-2,DATEDIF(A2,B2,"m"))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi,

Can someone help me with a simple date formula?

I need to calculate the number of months an employee has been on the
job. If they started on the 15th of the month or after, we don't
count that month as a month worked. If they leave on the 15th or
later, we DO count that as a month worked.

My result needs to be a whole number and I want to enter the start
date in one cell, the term date in another and have number of months
worked appear in another cell.

 

[Ron Rosenfeld]

Hi,

Can someone help me with a simple date formula?

I need to calculate the number of months an employee has been on the
job. If they started on the 15th of the month or after, we don't
count that month as a month worked. If they leave on the 15th or
later, we DO count that as a month worked.

My result needs to be a whole number and I want to enter the start
date in one cell, the term date in another and have number of months
worked appear in another cell.

Try this:

=DATEDIF(DATE(YEAR(HIRE),MONTH(HIRE)+1,1),
DATE(YEAR(TERM),MONTH(TERM),1),"m")+
(DAY(HIRE)<15)+(DAY(TERM)>=15)


--ron

 

[Ron Rosenfeld]

See if this works for you:

=IF(AND(DAY(A2)<15,DAY(B2)<15),DATEDIF(A2,B2,"m")-2,DATEDIF(A2,B2,"m"))
--

1/1/2004
12/31/2004

Your formula gives 11 months. I believe OP wanted 12 months.




--ron

 

[RagDyer]

Hey Ron, didn't you realize that the suggestion was posted at 2:10 in the
afternoon ? ... That's *always* just about the time the 6 Martini lunch
kicks into gear !!!

How about this one?
This is after a 4 or 5 Irish Coffee dinner.

=IF(AND(DAY(A2)<15,DAY(B2)>14),DATEDIF(A2,B2,"m")+1,DATEDIF(A2,B2,"m"))
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

See if this works for you:

=IF(AND(DAY(A2)<15,DAY(B2)<15),DATEDIF(A2,B2,"m")-2,DATEDIF(A2,B2,"m"))
--

1/1/2004
12/31/2004

Your formula gives 11 months. I believe OP wanted 12 months.




--ron

 

[Ron Rosenfeld]

Hey Ron, didn't you realize that the suggestion was posted at 2:10 in the
afternoon ? ... That's *always* just about the time the 6 Martini lunch
kicks into gear !!!

How about this one?
This is after a 4 or 5 Irish Coffee dinner.

=IF(AND(DAY(A2)<15,DAY(B2)>14),DATEDIF(A2,B2,"m")+1,DATEDIF(A2,B2,"m"))

Clearly you do better after Irish Coffee than Martinis ). Maybe it was the
food?

Best,

--ron

 

[RagDyeR]

Food ??? ... What food?

OOoohhh, you mean that stuff that you can't drink.

I only do food on weekends and special occasions!<vbg>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Hey Ron, didn't you realize that the suggestion was posted at 2:10 in the
afternoon ? ... That's *always* just about the time the 6 Martini lunch
kicks into gear !!!

How about this one?
This is after a 4 or 5 Irish Coffee dinner.

=IF(AND(DAY(A2)<15,DAY(B2)>14),DATEDIF(A2,B2,"m")+1,DATEDIF(A2,B2,"m"))

Clearly you do better after Irish Coffee than Martinis ). Maybe it was
the
food?

Best,

--ron