VBA: 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!

[mdmackillop]

This is a 4-4-5 Calender

Hi Brouz,
Welcome to VBAX
What does this mean?

[mdmackillop]

From http://support.microsoft.com/kb/210249


'1. Create a module and type the following lines in the Declarations section:Option Explicit
Const FMonthStart = 6 ' Numeric value representing the first month
' of the fiscal year.
Const FDayStart = 16 ' Numeric value representing the first day of
' the fiscal year.
Const FYearOffset = -1 ' 0 means the fiscal year starts in the
' current calendar year.
' -1 means the fiscal year starts in the
' previous calendar year.


'2. Type the following two procedures:
Function GetFiscalYear(ByVal x As Variant)

If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalYear = Year(x) - FYearOffset - 1
Else
GetFiscalYear = Year(x) - FYearOffset
End If
End Function

Function GetFiscalMonth(ByVal x As Variant)
Dim m
m = Month(x) - FMonthStart + 1
If Day(x) < FDayStart Then m = m - 1
If m < 1 Then m = m + 12
GetFiscalMonth = m
End Function


'3. To test these functions, type each of the following lines in the
'Immediate window, and then press ENTER after each one: ?GetFiscalYear(#7/1/1999#)

'Note that this line returns the year 2000. ?GetFiscalMonth(#8/1/1999#)

[brouz]

testing it now. Thank you for the reply!

4-4-5 calendar is the type of financial fiscal calendar my company uses.. means months are split up by 4 weeks, 4 weeks, 5 weeks, repeat... so, January has 4 weeks, Feb 4 weeks, March 5 weeks, ...makes things difficult for me!

[mdmackillop]

Too simplistic?

Sub test()
MsgBox FiscalMonth(Date)
End Sub

Function FiscalMonth(Dat)
Dim m as String
Select Case Application.WeekNum(Dat)
Case Is <= 4
m = "Jan"
Case Is <= 8
m = "Feb"
Case Is <= 13
m = "Mar"
Case Is <= 17
m = "Apr"
Case Is <= 21
m = "May"
Case Is <= 26
m = "Jun"
Case Is <= 30
m = "Jul"
Case Is <= 34
m = "Aug"
Case Is <= 39
m = "Sep"
Case Is <= 43
m = "Oct"
Case Is <= 47
m = "Nov"
Case Is <= 52
m = "Dec"
End Select
FiscalMonth = m

End Function