Need help creating a formula based on VBA function!

[DushiPunda]

Hey all,
I'm working on creating a new schedule for my job, to include a sheet for various stats. One of the stats I'm looking at is how many months a given worker was available based on their certification date ("cd") and whether or not they have transferred from/otherwise left the job ("td"). To do this, I've created a function MAvail (Months Available) to calculate how many months a worker was available based on a few conditions/logic. However, it seems my work network is not a fan of macros, so it only works as long as I don't close the file after creating the macro. I'm wondering if there is a formula that can help me calculate the months available.

Here is the code for the function I've created (to be clear, it does work, my work network is not cooperating):

Function MAvail(cd As Range, td As Range) As Variant
Dim ma As Integer 'ma = month's available
For i = 1 To 12
    If cd = "" Then 'If cert date (cd) is blank, then they are not available for any months.
        ma = 0
    ElseIf Year(td) < Year(Now()) Then 'Else if the transfer date (td) year is before the current year, then they are not available (Shouldn't happen ever, but just in case)
        ma = 0
    ElseIf Worksheets(i).Cells(1, 1).Value = True Then 'Else if A1=True (based on checkbox/linked cell on each month's tab) then,
        If Year(cd) < Year(Now()) And td = "" Then 'If year of cert date is prior to current year and transfer date is blank then,
            ma = ma + 1 'Add one available month
        ElseIf Year(cd) < Year(Now()) And Month(td) >= Month(Worksheets(i).Cells(3, 3).Value) Then 'Else if cert date year is less than the current year AND transfer date month is AFTER (or is) the current sheet's month then,
            ma = ma + 1 'Add one available month
        ElseIf Month(Worksheets(i).Cells(3, 3).Value) >= Month(cd) And td = "" Then 'Else if the current sheet's month is after the cert date month AND transfer date is blank then,
            ma = ma + 1 'Add one available month
        End If
    End If
Next i
MAvail = ma
End Function

I should explain a little of my set up:
- There are 13 tabs/worksheets in the workbook. 12 of those are schedules for each month. One of them is a "Stats" sheet.
- Cell A1 (.Cells(1, 1)) on each sheet is linked to a checkbox, and therefore has a value of "TRUE" or "FALSE" based on whether or not the checkbox is checked.
- Cell C3 (.Cells(3, 3)) on each sheet is a date cell with the first of the month (i.e. on the Jan sheet, it is 1/1/2024).

Essentially I need the following conditions to be met for a month to be considered "available":
1. Cert Date (Column B) is not blank
2. Worksheet checkbox must be checked (aka Worksheet Cell A1 must be "TRUE")
3. Year (Cert Date) is before current year and transfer date (Column C) is blank or,
4. Year (Cert Date) is before current year and month(transfer date) is after worksheet month or,
5. Worksheet month is after the month (cert date) and the transfer date is blank

Hopefully that makes sense.

Here's a screenshot of my "Stats" sheet:

Here's a screenshot of one of the month's pages (showing A1 (currently hidden) and C3 = first day of the month. This is the same on every sheet):

I originally created this function back in the 2015-2018 time range, likely because I couldn't figure out the logic for a formula, if it's even possible with so many sheets and conditions. Hopefully one of you Excel Wizards can figure this one out for me.

Thank you and please let me know if you have any questions/need clarification!

 

[jkpieterse]

It shouldn't be that hard to replace this with formula logic. Can you please provide us with a bit of relevant data (input) for the function and the expected outcome? A link to a workbook containing both the VBA function and the sample data would be most beneficial.

 

[Trixterz]

I don't know what column do you reference the worksheet month at so I have name that column as "?WorksheetMonth?" and I don't know what you argument is going to do when the statement is true or false. But hopefully this code below can help you on your way there.

=IF(
AND(TRIM(B1)<>"",A1=TRUE,
OR(
AND(TEXT(B1,"yyyy")<TEXT(NOW(),"yyyy"),TRIM(C1<>"")),
AND(TEXT(B1,"yyyy")<TEXT(NOW(),"yyyy"),TEXT(C1,"m")>TEXT("?WorksheetMonth?","m"),
AND(TEXT("?WorksheetMonth?","m")<TEXT(C1,"m"),TRIM(C1)="")))),
"Answer is True", "Anser is False")

 

[DushiPunda]

@jkpieterse I actually agree. I think I was making this more complicated than it needed to be. I'm sure there was some I created that function, but until I figure that out, I've come up with the following formula:

=IF(B2="",0,IF(C2<>"",MONTH(C2),IF(YEAR(B2)<YEAR(NOW()),MONTH(NOW()),IF(AND(YEAR(B2)=YEAR(NOW()),MONTH(B2)=MONTH(NOW())),1,IF(AND(YEAR(B2)=YEAR(NOW()),MONTH(B2)<>MONTH(NOW())),MONTH(NOW())-MONTH(B2),IF(B2="",0,0))))))

Thank you @Trixterz - I didn't get a chance to test if it worked or not as I came up with the above before I saw your post!

 

[jkpieterse]

Please ignore this message

 

[Trixterz]

@jkpieterse I actually agree. I think I was making this more complicated than it needed to be. I'm sure there was some I created that function, but until I figure that out, I've come up with the following formula:

=IF(B2="",0,IF(C2<>"",MONTH(C2),IF(YEAR(B2)<YEAR(NOW()),MONTH(NOW()),IF(AND(YEAR(B2)=YEAR(NOW()),MONTH(B2)=MONTH(NOW())),1,IF(AND(YEAR(B2)=YEAR(NOW()),MONTH(B2)<>MONTH(NOW())),MONTH(NOW())-MONTH(B2),IF(B2="",0,0))))))

Thank you @Trixterz - I didn't get a chance to test if it worked or not as I came up with the above before I saw your post!

Its okay, I basically wrote the formula just based on your description

Essentially I need the following conditions to be met for a month to be considered "available":
1. Cert Date (Column B) is not blank
2. Worksheet checkbox must be checked (aka Worksheet Cell A1 must be "TRUE")
3. Year (Cert Date) is before current year and transfer date (Column C) is blank or,
4. Year (Cert Date) is before current year and month(transfer date) is after worksheet month or,
5. Worksheet month is after the month (cert date) and the transfer date is blank

=IF(
AND(TRIM(B2)<>"",A1=TRUE,
OR(
AND(TEXT(B2,"yyyy")<TEXT(NOW(),"yyyy"),TRIM(C2<>"")),
AND(TEXT(B2,"yyyy")<TEXT(NOW(),"yyyy"),TEXT(C2,"m")>TEXT(B2,"m")),
AND(TEXT(B2,"m")<TEXT(C2,"m"),TRIM(C2)=""))),
"Do Something When Answer is TRUE", "Do Something When Answer is FALSE")