Working with Date fields

[stitch]

Hi,

I'm currently using a loop to cycle through my 20,000 records and perform a few checks (eg, find duplicates). While i'm going through this loop I'd also like to populate two columns for the row i'm looking at.

For example, I want to turn:
Column A Column B Column C
12-JAN-03 Empty Empty
20-JAN-04 Empty Empty
15-FEB-04 Empty Empty

Into this:
Column A Column B Column C
12-NOV-03 NOV FY04 Q2
20-JAN-04 JAN FY04 Q3
15-FEB-04 FEB FY04 Q3

So I want the Macro to check the date in column A and insert a three letter abbreviation of the Month into column B. I also want to have an "if then" statement in the macro so that I can say if date between xxxx and yyyy then insert "FY04 Q2" or if date between wwww and zzzz then insert "FY04 Q3" otherwise insert "error".

I hope this explains my problem enough. I look forward to any responses.

Many thanks,
Richard.

 

[xlbo]

Please post the code you are using to loop...

A general construct would be

For each c in selection

c.offset(0,1).value = ucase(format(c.value,"mmm"))
select case c.value
  case is > x and < y
     c.offset(0,2).value = "FY04 Q2"
  case is > a and < b
     c.offset(0,2).value = "FY04 Q3"
  case else
     c.offset(0,2).value = "ERROR"
end select

Next

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[stitch]

Thanks for the quick reply, I will give it a go straight after lunch. Here is a copy of the loop which I have decided to use, it checks to see if column a = column a on the row above and if column b = column b on the row above. If they do then it enters "Duplicate" in column D, on the row which it's checking.

lr = Range("a65536").End(xlUp).Row
For A = lr To 2 Step -1
Range("a" & A).Select
If Range("a" & A).Value = Range("a" & A - 1).Value And Range("b" & A).Value = Range("b" & A - 1).Value Then
ActiveCell.Offset(0, 0).Rows("1:1").EntireRow.Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 1
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "Duplicate"
End If
Next A

 

[xlbo]

This can be much simpified but where would you want the date stuff to go ?? - should it be applied to duplicate lines as well as regular ones ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi stitch,

Is there a particular reason you want code for this? Everything you've posted so far can be done with formulae in the cells.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[stitch]

Tony,

I understand that I could do the "MMM" formatting using format cells but I didn't know it was possible to do the "FY04 Q2" etc.

The reason I want to place this information in a macro is that I have to open these files quite often and insert these new rows, it would just be easier to open the document and press "ctrl+g" for instance.

Geoff,

I would like to update the columns for duplicate rows as well. The main reason is that I don't delete duplicates, I just exclude them later on half of the pivot tables. The other half duplicates are included, and still need to be sorted by Quarter and Month.

PS> I'd like to know how to amend my macro first to accomodate these changes, but I am also interested in finding out easier ways of writing the code.

PPS> In my macro I don't just check columns A and B when looking for duplicates. I check all the way up to R. As you can imagine this line is very long so I didn't write the whole thing in this message chain.

 

[stitch]

One final point.

I require the 3 letter abbreviation Month column to be text and not date. This way I can group records in a pivot table.

Currently the pivot table only groups the records if the date is the same. ie. 17th Jan and not if the 3 letter Month is the same.

Thanks,
Richard.

 

[TonyJollans]

Hi stitch,

Your call, of course, but if you want a formula ...

making an educated guess, based on your example, at your financial year end, this should give you what you show ..

[blue][tt]="FY"&TEXT(DATE(YEAR(A1),MONTH(A1)+6,1),"YY")&" Q"&INT((MOD(MONTH(A1)+5,12))/3)+1[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[xlbo]

for each c in range("A2:A" & cells(65536,1).end(xlup).row
        if c.Value = c.offset(-1,0).Value And c.offset(0,1).Value = c.offset(-1,1).Value Then
            with Rows(c.row).EntireRow
             with .Interior
                    .ColorIndex = 3
                    .Pattern = xlSolid
             End With
              .Font.ColorIndex = 1
            End with
            c.Offset(0, 3).value = "Duplicate"
        End If
        c.offset(0,1).value = ucase(format(c.value,"mmm"))
        'Use Select case here for the Quarters
next

BTW - dates can very easily be grouped in a pivottable - by month OR quarter

Just right click on the date field in the PT and chosse Group & Show Detail>Group

From this box, you can select weeks, months, quarters or years - does this negate the need for the addition of the month field and the quarter field ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[stitch]

Thanks for the replies, they have both been very useful.

The grouping within pivot tables was useful but unfortunately the quarters don't match what I need. For example my Q1 is (JUN, JUL, AUG) and not (JAN, FEB, MAR).

I've been trying to get your SELECT code to work but it won't allow me to enter < after the And. The below code is valid but won't run. Could you please let me know where i'm going wrong?

Select Case Range("p" & a).Value
Case Is > "01-SEP-2003" And "30-NOV-2003"
Range("r" & a).Value = "FY04 Q2"
Case Is > "01-DEC-2003" And "29-FEB-2004"
Range("r" & a).Value = "FY04 Q3"
Case Else
Range("r" & a).Value = "ERROR"
End Select

 

[xlbo]

Select Case Range("p" & a).Value
  Case datevalue("01-SEP-2003") to datevalue("30-NOV-2003")
    Range("r" & a).Value = "FY04 Q2"
  Case datevalue("01-DEC-2003") to datevalue("29-FEB-2004")
    Range("r" & a).Value = "FY04 Q3"
  Case Else
    Range("r" & a).Value = "ERROR"
End Select

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[stitch]

Working now. Thank you very much.

Stitch.