Solved: Excel Help Needed

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

coachdan32

Thread Starter
Joined
Nov 13, 2003
Messages
1,021
I am trying to solve a problem in Excel and I am not sure how to. I have a date in cell F2 and a number in cell G2 that represents the month the data was collected in (1-12). I am trying to come up with a way to compare the month portion of the date to the number in G2 and if they are the same, look at the day portion of the date. If the day is <=15, set cell H2 equal to "30 Days". If it's >15, set H2 equal to "".
The second part of the problem is if the month portion of the date is not the same as G2. If it is one month greater, then again look to the day portion, and if it is <=15, set cell H2 equal to "60 Days". If it's >15, set H2 equal to "30 Days".
The same thing if the month portion is two months greater then G2. If day portion is <=15, set H2 equal to "90 Days". If it's >15, set it equal to "60 Days".
The same thing if the month portion is three months greater then G2. If day portion is <=15, set H2 equal to "". If it's >15, set it equal to "90 Days".
If the month portion is less than the number in G2 OR more than 3 months greater, set H2 equal to "".

The purpose of this is to try to separate records into 30/60/90 day buckets based on the date. The complicated part is the stipulation that if they started their training on before the 15th, it moves them up a level. I would appreciate greatly anyone that can solve the problem.
 
Joined
Oct 21, 2004
Messages
126
Dan,

The formula you want, to put in H2, is:

=IF(OR(AND(G2=MONTH(F2),DAY(F2)>15),G2>MONTH(F2),AND(G2=MONTH(F2)-3,DAY(F2)<16),G2<(MONTH(F2)-4)),"",((DAY(F2)<16)+MONTH(F2)-G2)*30&" days")

This will give you exactly what you asked for. I have attached a little test spreadsheet I developed to prove it works. You can set any date you want in F2 by selecting the day, month, and year in B3, B2, and B4 respectively. Then you select what month you want in G2 and you'll see the results in H2.

The problem, as I see it, is that this doesn't work for dates in January when G2 is October, November, or December. It also doesn't work for dates in February when G2 is November or December and it doesn't work for dates in March when G2 is December. But that's what you specified when you said, "If the month portion is less than the number in G2 OR more than 3 months greater, set H2 equal to ""," so that's what I did. So when you change to a new year, it won't work for 3 months. Now I can fix that if you want. Just let me know.
 

coachdan32

Thread Starter
Joined
Nov 13, 2003
Messages
1,021
ddockstader,
It sounds like you are on the same page as me. I couldn't find your attachment to look at. The date is not something I choose, but rather part of a data dump that is provided for me. I see the problem you are describing with end of year dates - not sure how to approach it. Thought if I gave you a sample sheet of what I have, it might help with the big picture.
 

Attachments

Joined
Oct 21, 2004
Messages
126
Don't know what happened to that attachment. I thought I attached it, but didn't check the message after it posted. I will this time.

Do you want me to work on the year-end problem? I think I can make the formula on the attachment a little more complicated and it will work.
 

Attachments

coachdan32

Thread Starter
Joined
Nov 13, 2003
Messages
1,021
ddockstader,
That would be great. Your right on the button. The only thing I'm not sure of is the way you pulled the date from it's separate components in the top left. Did you look at my sample? With the exception of the column where I tell it what month is being reported and the column where your formula will be entered to show the 30/60/90 day buckets that is how the data comes to me. Will I need to separate the date components of the ClsEndDate as you did on your date? If so, I guess I can do text to columns and then reassemble them as you did.
 
Joined
Oct 21, 2004
Messages
126
In crisis mode right now, but should free up later this afternoon. I'll get back to you then. Thanks.
 
Joined
Oct 21, 2004
Messages
126
Dan,

Well, it took a little longer than I expected (the crises today came in a conga line), but here is your solution. I'm sorry I didn't explain the first sheet I sent very well. No, you don't have to break up the date, as you can see on the second worksheet that I'm sending. That is your text file in Excel with the appropriate formulae. The reason I broke up the date on the first sheet was for testing. It's a lot easier to change the date with a couple of mouse clicks rather than having to enter a full date every time you want to test a different situation (2 mouse clicks as opposed to up to 11 keystrokes). So the break up was just for testing purposes. The best programmers are the laziest, and old programmers are even lazier than the best. This formula works no matter what combination of months you have - even January, February, and March. Now, granted, it's a little complicated, but it works. Good luck. Let me know if you have any problems.
 

Attachments

coachdan32

Thread Starter
Joined
Nov 13, 2003
Messages
1,021
ddockstader,

It works like a charm for the 30 days, but it also has to do 60 and 90. For example, when you change the month to 10 and G2 is 12 - H2 is a null value and should be either 60 days or 90 days, depending on the day value.
You have put a lot of time in on this for me and I thank you. Let me take what you've done and see if I can figure the rest out for a couple of days. If I can't, I'll post back. I don't want you to think that I am looking for someone to do it for me. You have given me a great start and I'll try to run with it. Again, thanks for the start.
 
Joined
Oct 21, 2004
Messages
126
OK, I'm confused. Mine works perfectly. When I change the month of the date to 10 - say 10/14/2004 - and G2 to 12, it's supposed to be blank. In your first post, you said, "If the month portion is less than the number in G2 OR more than 3 months greater, set H2 equal to ""." Now if I change G2 to 10, it comes out "30 days;" G2 to 9 changes it to "60 days;" and G2 to 8 changes it to "90 days." If I've got the sign wrong, I can change it, but I understood you to mean that if G2 was before the date in F2, you calculated the days. And I don't mind working on this. It was fairly challenging to get it to work. :)
 

coachdan32

Thread Starter
Joined
Nov 13, 2003
Messages
1,021
"If the month portion is less than the number in G2 OR more than 3 months greater, set H2 equal to ""."

This was taken directly from my original post. You read correctly and I posted incorrrectly. My fault. I was typing faster than I was thinking. What I meant to say, but mis-stated, was that I am not interested in any results outside the 30/60/90 day range. So, instead of saying if the month portion is less than G2, I should have said that dates that are earlier than the range that constitutes the 90 day parameter should return "". Sorry for the confusion, it was on my end - not yours.

I'm sorry if you took it that I was being critical of your help - that was not my intent. It is a very complex calculation and it was complex for me to set up in words as well. I knew all along that the miscalculation was caused by a misunderstanding of my description and not on your work. My comments were just meant to let you know that I was willing and currently working on this problem too and that I was not just simply posting my work out for someone else to do. Believe me, your efforts are very much appreciated. I will continue, as I have, to try to figure this out and if you would like to (and have time to) work on it more I would be grateful as you obviously know a lot more than I do about these type of formulae.
 
Joined
Oct 21, 2004
Messages
126
Dan,

I didn't think you were being critical. I'm just confused (believe me, that happens a lot). I need you to look over the table below and tell me if the answers are correct.

Date (F) G H (I put in BLANK so you'll know it's blank)
02/14/03 2 30 days
02/16/03 2 BLANK
03/14/03 2 60 days
03/16/03 2 30 days
04/14/03 2 90 days
04/16/03 2 60 days
05/14/03 2 BLANK
05/16/03 2 90 days
01/14/03 12 60 days
01/16/03 12 30 days
02/14/03 12 90 days
02/16/03 12 60 days
03/14/03 12 BLANK
03/16/03 12 90 days
10/14/03 12 BLANK
10/16/03 12 BLANK
11/14/03 12 BLANK
11/16/03 12 BLANK
12/14/03 12 30 days
12/16/03 12 BLANK

Are these correct? If not, please tell me what the answers should be and I'll fix the formula to make it work. I think there are enought examples here that I can understand what the formula is if you give me the right answers. That way we don't have to rely on the inexactness of the English language. Believe me, I've been doing this for 27 years, so I know how confusing putting things into words can be. So I just want to be sure that the G2 month is supposed to be less than (prior to) the month of the date. That's what confused me because, as I understand it, an October date with a December month in G2 should end up being blank, not 60 or 90 days. Don't worry, Dan. I'm in this for the long haul. I think this sort of thing is fun (which is probably why I've been doing it for 27 years. :)
 

coachdan32

Thread Starter
Joined
Nov 13, 2003
Messages
1,021
Date (F) G H (I put in BLANK so you'll know it's blank)
02/14/03 2 30 days
02/16/03 2 BLANK
03/14/03 2 BLANK
03/16/03 2 BLANK
04/14/03 2 BLANK
04/16/03 2 BLANK
05/14/03 2 BLANK
05/16/03 2 BLANK
01/14/03 12 BLANK
01/16/03 12 BLANK
02/14/03 12 BLANK
02/16/03 12 BLANK
03/14/03 12 BLANK
03/16/03 12 BLANK
10/14/03 12 90 Days
10/16/03 12 60 Days
11/14/03 12 60 Days
11/16/03 12 30 Days
12/14/03 12 30 days
12/16/03 12 BLANK

Maybe if I explain better what is going on, it will help.

The date(F) is a date that a student gets out of a training class. The number it is being compared to (G) is the month we are reporting for. (H) is of course how long they have been out of the class. If the student got out of the class before the 15th of the month, we want to count that as a complete month (+30 days). So, if the student got out 8/1/04 (F) and we are collecting for month 8 (G), it would count as 30 days (H) because the 1st of August is less than the 15th. If the student got out on 7/1/04 and we are collecting for month 8, it would be 60 days for the same reason. However, if the student got out on 7/16/04 and we are collecting for month 8, it would only be 30 days because we can't count July because the 16th is greater than 15.

If the month in the date is greater than the month we are collecting for, it would be BLANK - because they didn't get out of class until after the date we are collecting for.
 
Joined
Oct 21, 2004
Messages
126
Dan,
I think this should do it. I just had the signs (of just about everything) reversed. But it should work just as you wanted now. Good luck!
 

Attachments

Joined
Oct 21, 2004
Messages
126
Try this version. The other one still prints out BLANK when the entry is supposed to be blank. This one just leaves it blank. I guess I was just too anxious to send you the working version.
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top