Comparing 2 Dates

[coachdan]

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.

coachdan32

 

[xlbo]

you need to convert to text and then use th eVALUE function to convert certain bits back to numbers eg

=TEXT(A2,"dd/mm/yyyy")

will convert a date in A2 to pure text

=MONTH(A2) will give you the month number

=VALUE(LEFT(TEXT(A2,"dd/mm/yyyy"),2))

will give you the day number of the month

The rest is just logic

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[bluedragon2]

What I did was to make an array that is 1 thru 12 Down and 12 Columns over giving me th day count for greater then 15 days.

I then used the formula:

=IF(DAY(F2)<15,VLOOKUP(MONTH(F2),A4:M15,G2+2,0),VLOOKUP(MONTH(F2),A4:M15,G2+1,0))




[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[coachdan]

xlbo,

I had a similar solution by just doing text-to-columns, but that still leaves a big nasty nested if statement that uses conditions - and that is where I got into trouble.

coachdan32

 

[coachdan]

If I split the date into three cells (I2=mm, J2=dd, K2=yyyy), isn't it possible to do this with an If statement somehow?

=If(G2<I2,"",If(G2=I2,"30 Days",If(G2=I2+1,"60 Days",If(G2=I2+2,"90 Days","Out of Scope"))))

The above seems like it would work, but does not take into consideration if the day is <=15. I cannot picture in my mind what the statement would look like to test for the day. Surely one of you Excel genius's can figure this out. I would be eternally grateful and would issue a star.

coachdan32

 

[bluedragon2]

What happens with the above statement when you are adding 1 to Dec? and comparing it to Jan?

Since dates are actually just numbers, you can even have a simpler lookup table/IF function for breaks in the differance in the serial date.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[coachdan]

Blue,

You are right. It would look at 12 (Dec) and compare it to 1, which is less and set it equal to "" - and at the end of the year that would be a problem.

I am not sure how I could use a lookup table to solve the problem. I don't have much experience with them. Is it possible to setup a separate table with month, day and possibly a 30/60/90 day criteria? Then have the original table look at the month and day from the lookup table, find a match and then use the 30/60/90 day criteria from the lookup table to populate the appropriate cell in the original table?

coachdan32

 

[bluedragon2]

The array I sent you works, not sure why it won't work for you. Just a simple grid that uses your criteria. If I have time later, I will look into using the serial number aspect.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[coachdan]

I'm sure it is on my end bluedragon, I have never done anything like that before and do not know how to set it up. I am very unfamiliar with using arrays in Excel.

coachdan32