Change Code from Numeric to Date Function

[Divinedar]

I have the following function that reads cells F23 thru Q38 but based on numbers 1 thru 12, 13 thru 24, 25 thru 36, 37 thru 48, or 49 thru 60 (5 year period) in F22 thru Q22 on many worksheets. Management changed from numbers, 1 thru 12, etc... to dates now based on fiscal year. Our fiscal year starts in August of each year.


What it does is that if you put a date in cell G8 (date based on the worksheet), cell F22 starts as that date for 12 months to Q22 per worksheet.


I need for this function to read dates (format mmm-yy) instead of numbers and I can't figure out. Can anyone help?


The function is:

[pre]

Function HrsByMonth(strField As String, MonthNum As Integer) As Long
'Application.Calculation = xlCalculationManual 

Dim sht As Worksheet, i As Long, j As Integer 

HrsByMonth = 0 

For Each sht In ActiveWorkbook.Sheets
If IsNumeric(Left(sht.Name, 2)) And IsNumeric(Right(sht.Name, 3)) And Mid(sht.Name, 3, 1) = "." Then
For j = 6 To 17
If CInt(sht.Cells(22, j)) = MonthNum Then
For i = 23 To 38 'update with max row
If sht.Cells(i, 5) = strField Then
HrsByMonth = HrsByMonth + CLng(sht.Cells(i, j))
GoTo nxtSht
End If
Next i
End If
Next j
End If 

nxtSht: Next
'Application.Calculation = xlCalculationAutomatic 

End Function[/pre]
 The code is in a separate worksheet that gathers the information from each worksheet.

=(IF(ISBLANK($A2),0,(IF(ISBLANK(B$1),0,hrsbymonth($A 2,RIGHT(B$1,LEN(B$1)-6))))))

The above code is in each cell up to 60 months on a worksheet called HrsByMth. Because the fiscal year starts in August of each year, the first cell which is cell B1 of HrsByMth would be the start date of the project; so it won't be August all the time, it could be any date.


So I need to get "MonthNum " to match the heading dates on the worksheet HrsByMth.


If you have any other questions please let me know.

 

[Luke M]

I think we should just be able to change the function's input to a date variable, and then convert that to a month (1-12)

[pre]

Function HrsByMonth(strField As String, MyMonth As Date) As Long
'Application.Calculation = xlCalculationManual

Dim sht As Worksheet, i As Long, j As Integer

'New code
Dim MonthNum As Integer
MonthNum = Month(MyMonth)

HrsByMonth = 0

For Each sht In ActiveWorkbook.Sheets
If IsNumeric(Left(sht.Name, 2)) And IsNumeric(Right(sht.Name, 3)) And Mid(sht.Name, 3, 1) = "." Then
For j = 6 To 17
If CInt(sht.Cells(22, j)) = MonthNum Then
For i = 23 To 38 'update with max row
If sht.Cells(i, 5) = strField Then
HrsByMonth = HrsByMonth + CLng(sht.Cells(i, j))
GoTo nxtSht
End If
Next i
End If
Next j
End If

nxtSht:
Next sht

'Application.Calculation = xlCalculationAutomatic

End Function

[/pre]

 

[Divinedar]

I think I know what my problem is. When I evaluate the formula on this code:


=(IF(ISBLANK($A2),0,(IF(ISBLANK(B$1),0,hrsbymonth($A 2,RIGHT(B$1,LEN(B$1)-6))))))


B1 is a date that needs to match with the dates on worksheets but it gives me the format 41122; which is the same date but integer. No matter if the I change the data type this is what the code is giving me for B1.


What can I do?

 

[SirJB7]

Hi, Divinedar!

Tried changing B$1 by TEXT(B$1,"mmm-yy")?

Regards!