Reply With QuoteI don't understand how you got the results for Group 2? Can you explain?
Also, if you have crossposted to other forums, please indicate them here.
I am way over my head on this, but it seems to me my problem could be solved w/ either a formula or look-up of some sort. In either case, it's beyond my skill set. Easier to visualize than explain so I have attached the sheet. Thanks for any thoughts on the problem.
I don't understand how you got the results for Group 2? Can you explain?
Also, if you have crossposted to other forums, please indicate them here.
Unless I screwed something up in my sample, a person in Group two has to register twice a year; once during the birth month and the other 6 months later. Assuming it's January, for a person born in Jan the next date would be Jul and if born in Feb, the next registration date would be Aug. (and yes, I see that I screwed up the group 2 people) See that's why I need to automate it. I'll revise the sample. Thanks.
Oh, and I have a similar post on a Google sheets forum, but it's going to be a different solution and I'm not getting very far w/ it. Thanks.
Last edited by wmpwi; 2014-01-30 at 03:58 PM.
Another way to look at it is Annual, Semi Annual, and Quarterly with the start of each registration period equaling the birth Month. Born in Jan as a Group 3 means traditional quarterly months, but born in Feb means an offset by 1 month so they're going to be Feb, May, Aug, Nov.
Group 2 people born in Jan = Jan & Jul, born in Feb = Feb & Aug, Mar = Mar & Sep and so on.
and Group 1's are annual so that one is easy.
The whole thing would be easy if it were 15 - 20 people, but is over 100 and they come an go so anything that automates it (as you can see from my earlier error) would help.
**** and I see I screwed up group 3. I must have moved something around and messed up all the expected results in col D
If I'm to incompetent to ask the question, I would certainly understand disqualifying my request. (so embarrassed)
Last edited by wmpwi; 2014-01-30 at 04:14 PM. Reason: I screwed up the sample
This is what I was thinking it was.. that is why I was confused about Group 2 results you showed. Maybe this can be done without tables. Is it only those 3 groups?Originally Posted by wmpwi
Also, if you can repost the expected results to make sure the solution gives the same results.. .that would be good.
As an initial go at it, this is my thinking about using just a formula... try this formula and let me know where it is going wrong, if at all..
=IF(B6=1,TEXT(A6,"mmm"),IF(B6=2,TEXT(EDATE(A6,6),"mmm"),TEXT(EDATE(A6,3),"mmm")))
which can be shortened to:
=TEXT(EDATE(A6,CHOOSE(B6,0,6,3)),"mmm")
I think we're getting somewhere, but my problem is this is a dynamic sheet that references A2 (today's date) such that when the date advances, the next registration month changes. Today it's Jan so a Jan student in Group 3 should show Jan in Col D, but in a few days it's Feb so the Jan students in Group 3 should show March in Col D. Hope that's starting to clear up my problem. Thanks.
How about this then?
=IF(MONTH(EDATE(A6,CHOOSE(B6,0,6,3)))<MONTH($A$2),TEXT(EDATE(A6,CHOOSE(B6,1,12,6)),"mmm"),TEXT(EDATE(A6,CHOOSE(B6,0,6,3)),"mmm"))
I'll have to play w/ it a bit, but it looks very good and an even more elegant solution than look-up tables. Thanks a bunch and I'll be back shortly.
Yup, that seems to be working very well. So much so that it also works in Google sheets as well. I'll be closing the thread there and making sure you're sited as the solution. Thanks a bunch.
Posting Permissions
Bookmarks