Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 12th 09, 03:38 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 26
Default Benefits Eligibility Dates

I am looking for a formula that helps me define 1st of the month or 1st of
the quarter following 30, 90, and 180 days after hire date.

So, I have the hire date in a cell and the next cell I want to see the date
for 1st of the month following 180 days after hire date.

Thanks in advance,

  #2   Report Post  
Old March 12th 09, 03:54 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2007
Posts: 2,722
Default Benefits Eligibility Dates

=DATE(YEAR(A2+180),MONTH(A2+180)+1,1)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Yvonne" wrote:

I am looking for a formula that helps me define 1st of the month or 1st of
the quarter following 30, 90, and 180 days after hire date.

So, I have the hire date in a cell and the next cell I want to see the date
for 1st of the month following 180 days after hire date.

Thanks in advance,

  #3   Report Post  
Old March 12th 09, 04:16 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 26
Default Benefits Eligibility Dates

Fantastic! Thank you.

Would you mind showing me the formula for 1st of quarter following 180 days
of employment? My 1st of quarter being Jan 1, Apr 1, Jul 1, and Oct 1.

"Luke M" wrote:

=DATE(YEAR(A2+180),MONTH(A2+180)+1,1)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Yvonne" wrote:

I am looking for a formula that helps me define 1st of the month or 1st of
the quarter following 30, 90, and 180 days after hire date.

So, I have the hire date in a cell and the next cell I want to see the date
for 1st of the month following 180 days after hire date.

Thanks in advance,

  #4   Report Post  
Old March 12th 09, 08:58 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2007
Posts: 2,722
Default Benefits Eligibility Dates

Actually, I think there's a small error in my first formula. When the 180 day
falls on the first of a month, it was getting skipped. First formula should
be:
=DATE(YEAR(A2+180),MONTH(A2+180)+IF(DAY(A2+180)=1, 0,1),1)

quarterly formula:
=DATE(YEAR(A2+180),CHOOSE(ROUNDUP((MONTH(A2+180)+I F(DAY(A2+180)=1,0,1)-1)/3,0)+1,1,4,7,10,1),1)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Yvonne" wrote:

Fantastic! Thank you.

Would you mind showing me the formula for 1st of quarter following 180 days
of employment? My 1st of quarter being Jan 1, Apr 1, Jul 1, and Oct 1.

"Luke M" wrote:

=DATE(YEAR(A2+180),MONTH(A2+180)+1,1)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Yvonne" wrote:

I am looking for a formula that helps me define 1st of the month or 1st of
the quarter following 30, 90, and 180 days after hire date.

So, I have the hire date in a cell and the next cell I want to see the date
for 1st of the month following 180 days after hire date.

Thanks in advance,



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate eligibility/enrollment date [email protected][_2_] Excel Worksheet Functions 8 December 23rd 08 08:26 PM
Calculating eligibility dates [email protected] Excel Worksheet Functions 4 July 7th 08 09:16 PM
Eligibility Dates Christy Excel Worksheet Functions 3 June 20th 08 09:22 PM
benefits statements Grace[_2_] Excel Discussion (Misc queries) 0 May 27th 08 10:16 PM
Benefits many people - Question ebgehringer Excel Discussion (Misc queries) 0 October 7th 05 09:22 PM


All times are GMT +1. The time now is 06:22 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017