Working with Dates in Excel 2007

[Bayliss4902]

I am writing a macro that will look at the dates in a column (D), and then depending on the range of the dates, it will insert a comment in a different column (A). My date format in my spreadsheet is MM/DD/YY. Any help on how to use an if function with determining a range of dates would be greatly appreciated.

 

[Ruddles]

Just remember that the date in your worksheet is a number which only displays as a date. To verify this, enter a date in a worksheet cell and format it as Number.

If you need to check whether A1 (for example) is equal to 8/8/2011, you would have to turn that into a number which can be compared directly to the value in the worksheet:-

if range("a1").value=datevalue("08/08/2011") then

or:-

if range("a1").value=datevalue("08-Aug-2011") then

or:-

if range("a1").value=dateserial(2011,8,8) then

Is that the sort on information you're looking for?

 

[Bayliss4902]

What I am looking for is to determine our company's quarters in the column. I want column a to display:
Q1 FY11 - if the date falls between 11/01/10 and 01/31/11
Q2 FY11 - if the date falls between 02/01/11 and 04/30/11
Q3 FY11 - if the date falls between 05/01/11 and 07/31/11
Q4 FY11 - if the date falls between 08/01/11 and 10/31/11

Currently I have a temporary fix using a formula, however when the new fiscal year starts it will input the incorrect fiscal year, hence the reason I am trying to do this in IF statement within VBA.

Temporary fix:

    Cells(2, 1).FormulaR1C1 = "=""Q""&ROUNDUP(MOD((MONTH(RC[3])+2),12)/3,0)&"" FY11"""
    Range("A2").AutoFill Destination:=Range(Cells(2, 1), Cells(x, 1)), Type:=xlFillDefault

 

[Ruddles]

Try this user-defined function. Paste the code into a new general code module. You feed it a date and it returns a string containing the Q & FY.

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]' Q1 FY11 - if the date falls between 1/nov/10 and 31/jan/11
' Q2 FY11 - if the date falls between 1/feb/11 and 30/apr/11
' Q3 FY11 - if the date falls between 1/may/11 and 31/jul/11
' Q4 FY11 - if the date falls between 1/aug/11 and 31/oct/11[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Public Function GetQuarter(ByVal aDate As Date) As String[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Application.Volatile
  
  Dim iYear As Integer
  Dim iMonth As Integer
  
  iYear = Year(aDate)
  iMonth = Month(aDate)
  
  Select Case iMonth
    Case 11, 12
      GetQuarter = "Q1"
      iYear = iYear + 1
    Case 1
      GetQuarter = "Q1"
    Case 2, 3, 4
      GetQuarter = "Q2"
    Case 5, 6, 7
      GetQuarter = "Q3"
    Case 8, 9, 10
      GetQuarter = "Q4"
  End Select
  
  GetQuarter = GetQuarter & " FY" & Right(CStr(iYear), 2)[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]End Function
[/FONT]

To use in your worksheet:-

=GetQuarter(A1)

(where A1 contains the date).

Check it out and see if it does what you need.