I have 5 columns: Employee Name, Start Date, Probation Time, Probation Ends, Benefits Start.
I want the Benefits Start column to populate the name of the month that follows the Probation Ends date.
For example Employee A starts on March 16. Probation time is 90 days. Probation Ends is 6/14/09. The Benefits start date is 7/1/09, however I want this column to just say July.
use the eomonth function
=EOMONTH(A1,4) will give july for any date in march (note format cell as custom MMM)
oh bytheway activate the analysis toolpak add in! to get this function
usually tools/options/add-ins tick the analysis tool pak check box
Last edited by martindwilson; 04-21-2009 at 11:38 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
I get #NAME? when I put that in. What is the '4' represent?
Does this formula work in Excel 2003?
Last edited by excelgrrl; 04-21-2009 at 11:40 AM.
sorry hadnt finished editing you need that toolpack activated.
ok its the endof the calander months 4 months from given date.
your probation finishes 3 months time so that would be in june so 4 gives july
Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.
Moved to Functions Forum
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
[Solved} Re: Month following 90 days from date
Thank you so much! This worked!
roy thats a bit harsh! some people think that functions are programming!
Maybe
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+90)
Format as mmmm
Last edited by royUK; 04-21-2009 at 12:03 PM.
I posted it to let the OP know & then moved it. What's harsh about that? If no-one lets the OPs know then they will continue to make the same mistakes.
i thought you were going to lock it!!!!!!!!!!
and i dont think adding 90 to day would do it as op says it always starts ist day of month after 90 days up
march 2+ 90 gives may 2 but requirement is june as payments always start from the next 1st of month
Last edited by martindwilson; 04-21-2009 at 03:31 PM.
but that isn't the requirement, is it?Originally Posted by martindwilson
If A1 is 1st March then A1+90 is 30th May so shouldn't the formula return "June" in that scenario?
I assume that if A1+90 gives you the 1st of the month that you still want to jump to the following month? If so try
=TEXT((MONTH(A1+90)+1)*29,"mmmm")
hmm you're correct, i just wonder if its 90 days or three months? darn awkward calander we have.
How about:
=DATE(YEAR(A1+90),MONTH(A1+90)+1,1)
Then you could format the cell to "mmmm" or wrap a TEXT() around it.
I realize I lot of time has passed since the last comment, but none of the formulas above worked for me because you cannot just assume putting a "1" for the day is going to work. This formula worked for me, but it is really long. If someone knows how best to shorten it, I would be grateful.
Assuming you put the date of hire in cell A1:
=IF(AND(DAY(DATE(YEAR(A1+90),MONTH(A1+90),DAY(A1+90)))>1),DATE(YEAR(DATE(YEAR(A1+90),MONTH(A1+90),DAY(A1+90))),MONTH(DATE(YEAR(A1+90),MONTH(A1+90),DAY(A1+90)))+1, 1),DATE(YEAR(A1+90),MONTH(A1+90),DAY(A1+90)))
This way, if the 90th day is the first of the month, then the employee's effective date comes out as the first of that month (i.e. DOH = 5/3/2011 - Coverage Start Date = 8/1/2011). If the 90th day is not the first of the month, then the employee's effective date is the first of the following month (i.e. DOH = 5/4/2011 - Coverage Start Date = 9/1/2011).
Try this
=EOMONTH(A1+89,0)+1
Audere est facere
AWESOME excel formula! I deleted the extra space and it worked like a charm. Thank you!!
This was the formula that WORKS! (especially if you want a year in your FOM after 90 days calculation): =IF(AND(DAY(DATE(YEAR(A1+90),MONTH(A1+90),DAY(A1+90)))>1),DATE(YEAR(DATE(YEAR(A1+90),MONTH(A1+90),DAY(A1+90))),MONTH(DATE(YEAR(A1+90),MONTH(A1+90),DAY(A1+90)))+1, 1),DATE(YEAR(A1+90),MONTH(A1+90),DAY(A1+90)))
I copied the formula into Word and did a search/replace to change cell A1 to the cell in my report.
Thanks again to Donnam!! Enjoy!
At the risk of repeating myself...why would you use that formula when this one gives the same result?
=EOMONTH(A1+89,0)+1
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks