If Date (range) Then (do this)

[brouz]

Hello,

I've googled for hours.. i need help with Fiscal Month subtotaling..actually, I'm trying to create a procedure (or function) that will identify the Fiscal month based on date range, enter the value in another column (ex. "APRIL"), that way i can subtotal by the new column. This is a 4-4-5 Calender, so i can't just identify the month of the date in each cell.

My dates are in Column E and Column D is blank (where i want to enter the Fiscal month.

Can anyone help me out?

Sub FiscalMonth()
 
Range("E2").Select
 
    If Date >= DateSerial(2008, 3, 31) _
    And Date <= DateSerial(2008, 4, 26) _
    Then ActiveCell.Offset(0, -1).Formula = "=APRIL"
 
    If Date >= DateSerial(2008, 4, 27) _
    And Date <= DateSerial(2008, 5, 24) _
    Then ActiveCell.Offset(0, -1).Formula = "=MAY"
 
End Sub

Thanks!!

 

[Fazza]

Welcome, brouz.

There are probably many ways to do this. A function in VBA might be very good. Very simple though might be to create a table on one worksheet with a list of dates in the first column and the fiscal month in the next column. Now use this lookup table. OK? Regards, Fazza

 

[brouz]

Thank you, Fazza. I might do that. I want to see how that works. But, i would still like to get some vba hardcode that i can put on others computers to assign a date a "fiscal month."

I was playing with the below code. It is NOT working for me though... and, i was wondering if a Select Case method would be better?

Sub GetMonth()
    Dim iLastRow As Integer
    Dim iRow As Integer
    Dim x1 As Integer
    Dim y1 As Integer
 
    x1 = DateSerial(2008, 3, 1)
    y1 = DateSerial(2008, 4, 26)
    On Error Resume Next
 
    iLastRow = Cells(1, 4).End(xlDown).Row
        For iRow = 1 To iLastRow
            If Cells(iRow, 4) >= x1 And Cells(iRow, 1) < y1 Then
                Cells(iRow, 6) = "APRIL"
            ElseIf Cells(iRow, 4) >= x2 And Cells(iRow, 1) < y2 Then
                Cells(iRow, 6) = "MAY"
            End If
        Next
End Sub

 

[Fazza]

Good morning, brouz.

Below is an example of using 'select case'. Obviously the limiting dates are hard coded for year 2008. I did this per your first posting. In reality I assume you want something that was not specific to a single year. If the date ranges are fixed known numbers of weeks from a starting date, the coding could be simplified by determining the difference from the input date to a single start date, converting to weeks maybe and then returning the result. So using weeks instead of fixed calendar dates. Still I offer the code as an example of a UDF and of select case. Please take this example and modify to best suit your requirements.

The custom function is a very good approach & maybe best. The idea of a lookup sheet might still be OK, BTW. Just as another way. I mention this just for information. If you need this for a few people, write an add-in. The add-in is not visible so can have one worksheet with the lookup table.

Regards, Fazza

Function FiscalYear(InputDate) As Variant
  If IsDate(InputDate) Then
    Select Case CDate(InputDate)
      Case DateSerial(2008, 3, 31) To DateSerial(2008, 4, 26): FiscalYear = "APRIL"
      Case DateSerial(2008, 4, 27) To DateSerial(2008, 5, 24): FiscalYear = "MAY"
      Case Else: FiscalYear = CVErr(xlErrNA)
    End Select
  Else
    FiscalYear = CVErr(xlErrNA)
  End If
End Function

 

[Fazza]

PS. The UDF code goes in a code module - not a worksheet or workbook module. F

 

[brouz]

Thank you *so* much for your help!! I used your code and it works perfect! this is exactly what i needed. I don't mind that 2008 is hardcoded. It will work all year

Function FiscalYear(InputDate) As Variant
  If IsDate(InputDate) Then
    Select Case CDate(InputDate)
 
        Case DateSerial(2007, 12, 30) To DateSerial(2008, 1, 26): FiscalYear = "January"
        Case DateSerial(2008, 1, 27) To DateSerial(2008, 2, 23): FiscalYear = "February"
        Case DateSerial(2008, 2, 24) To DateSerial(2008, 3, 29): FiscalYear = "March"
        Case DateSerial(2008, 3, 30) To DateSerial(2008, 4, 26): FiscalYear = "April"
        Case DateSerial(2008, 4, 27) To DateSerial(2008, 5, 24): FiscalYear = "May"
        Case DateSerial(2008, 5, 25) To DateSerial(2008, 6, 28): FiscalYear = "June"
        Case DateSerial(2008, 6, 29) To DateSerial(2008, 7, 26): FiscalYear = "July"
        Case DateSerial(2008, 7, 27) To DateSerial(2008, 8, 23): FiscalYear = "August"
        Case DateSerial(2008, 8, 24) To DateSerial(2008, 9, 27): FiscalYear = "September"
        Case DateSerial(2008, 9, 28) To DateSerial(2008, 10, 25): FiscalYear = "October"
        Case DateSerial(2008, 10, 26) To DateSerial(2008, 11, 22): FiscalYear = "November"
        Case DateSerial(2008, 11, 23) To DateSerial(2008, 12, 27): FiscalYear = "December"
 
      Case Else: FiscalYear = CVErr(xlErrNA)
    End Select
  Else
    FiscalYear = CVErr(xlErrNA)
  End If
End Function

 

[Fazza]

 

[mortgageman]

<little snip> I don't mind that 2008 is hardcoded. It will work all year

<big snip of code>

Then you may want to rename the function to FiscalYear2008