formula help with dates

DW in OKC · May 20th, 2002, 03:03 PM

I'm trying to figure out how to calculate the eligibility date for employees. The data would be the Date of Hire, eligibility starts 30 days follwing date of hire on the first of the following month.

Example:
Date of Hire - 01/15/2002 (this is the constant)
Eligible - 03/01/2002 (this is what the results of the formula should show)

I'd appreciate any help!

DW in OKC

[ This Message was edited by: DW in OKC on 2002-05-20 14:39 ]

Asala42 · May 20th, 2002, 03:21 PM

Hey,

How about:

=EOMONTH(A2,1)+1

(where A2 holds the hiredate)

Adam

PaddyD · May 20th, 2002, 03:21 PM

DW,

Try the following:

=DATEVALUE("01"&"/"&(MONTH(EOMONTH(A1,2)))&"/"&(YEAR(EOMONTH(A1,2))))

Hope this helps,

Paddy

Yogi Anand · May 20th, 2002, 03:39 PM

or how about

=IF((EOMONTH(A3,0)+1-A3)<30,EOMONTH(A3,1)+1,EOMONTH(A3,0)+1)

DW in OKC · May 20th, 2002, 03:57 PM

The winner is Yogi Anand,ANAND Enterprises. Thanks for everyone's help. I tried all three answers and the best fit was Yogi's. This will make a client very happy!

Maybe I can help the next person that needs help!

DW

[ This Message was edited by: DW in OKC on 2002-05-20 14:59 ]

Yogi Anand · May 20th, 2002, 04:42 PM

Hi DW in OKC:

I am glad the solution that we folks at MrExcel developed for you worked out. Please keep in mind, we are not competing here -- all of us complement and feed off each other. When a solution works out, thanks should go to all those who participated in developing the solution. And when a solution works out we are all winners -- not a single individual. So, I hope you will join me in thanking all of the folks who worked with you in developing this solution. So, thanks go to the whole team: DW, Adam, Paddy, and Yogi.

Regards!

Mark W. · May 20th, 2002, 04:58 PM

Okay, but what if the date of hire is 1/30/02? If "eligibility starts 30 days following date of hire on the first of the following month" then ="1/30/02"+30 is 3/1/02, and the "first of the following month" is 4/1/02.

The formula, =EDATE(TEXT(A2+30,"m-yyyy")+0,1), produces 4/1/02.

[ This Message was edited by: Mark W. on 2002-05-20 15:59 ]

Yogi Anand · May 20th, 2002, 05:33 PM

Hi Mark:
That's beautiful ... also short and sweet.

Regards!

May 20th, 2002, 03:39 PM	#4
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	or how about =IF((EOMONTH(A3,0)+1-A3)<30,EOMONTH(A3,1)+1,EOMONTH(A3,0)+1) __________________ Regards! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC www.energyefficientbuild.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 20th, 2002, 03:03 PM	#1
DW in OKC New Member Join Date: May 2002 Posts: 2	I'm trying to figure out how to calculate the eligibility date for employees. The data would be the Date of Hire, eligibility starts 30 days follwing date of hire on the first of the following month. Example: Date of Hire - 01/15/2002 (this is the constant) Eligible - 03/01/2002 (this is what the results of the formula should show) I'd appreciate any help! DW in OKC [ This Message was edited by: DW in OKC on 2002-05-20 14:39 ]

May 20th, 2002, 03:21 PM	#2
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,030	Hey, How about: =EOMONTH(A2,1)+1 (where A2 holds the hiredate) Adam

May 20th, 2002, 03:21 PM	#3
PaddyD MrExcel MVP Join Date: May 2002 Posts: 13,278	DW, Try the following: =DATEVALUE("01"&"/"&(MONTH(EOMONTH(A1,2)))&"/"&(YEAR(EOMONTH(A1,2)))) Hope this helps, Paddy

May 20th, 2002, 03:57 PM	#5
DW in OKC New Member Join Date: May 2002 Posts: 2	The winner is Yogi Anand,ANAND Enterprises. Thanks for everyone's help. I tried all three answers and the best fit was Yogi's. This will make a client very happy! Maybe I can help the next person that needs help! DW [ This Message was edited by: DW in OKC on 2002-05-20 14:59 ]

May 20th, 2002, 04:42 PM	#6
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi DW in OKC: I am glad the solution that we folks at MrExcel developed for you worked out. Please keep in mind, we are not competing here -- all of us complement and feed off each other. When a solution works out, thanks should go to all those who participated in developing the solution. And when a solution works out we are all winners -- not a single individual. So, I hope you will join me in thanking all of the folks who worked with you in developing this solution. So, thanks go to the whole team: DW, Adam, Paddy, and Yogi. Regards!

May 20th, 2002, 04:58 PM	#7
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Okay, but what if the date of hire is 1/30/02? If "eligibility starts 30 days following date of hire on the first of the following month" then ="1/30/02"+30 is 3/1/02, and the "first of the following month" is 4/1/02. The formula, =EDATE(TEXT(A2+30,"m-yyyy")+0,1), produces 4/1/02. [ This Message was edited by: Mark W. on 2002-05-20 15:59 ]

May 20th, 2002, 05:33 PM	#8
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Mark: That's beautiful ... also short and sweet. Regards!