Excel VBA Functions- Perform Calculation if Value is found to be within Specified Range

What is the password to access the sample spreadsheet?

by: javaftper -  my apologies ... the unprotected workbook is located below...

thank you
SampleVacationAccrual.xlsm

It appears to me like you still have a problem with your work flow.
1. Creating a running total of Balance C/F (Previous PTO Bal Avail) plus Current Additions (hours accrued) can easily be achieved with a worksheet function.
2. Adding the current additions to the balance C/F at the end of the month requires that ..
    a) Balance C/F must be a constant value (can't be a formula), and
    b) Current Additions must be reset to zero at that time.
Whereas the above seems to be without problem in your plan your named ranges RenewalMonth, probably also TodaysDate, appear to be part of a solution that doesn't work. They should be scrapped and substituted with a proper description of the intended work flow.
It seems that you would like the changes described in step 2 above executed at the change of the month. That is no problem. Imagine it as a program that carries out the modifications described in step 2 above. Your work flow must decide how this program is activated.
You appear to have visualized some sort of automation. Note that the process of automation is quite separate from that of effecting the changes. Therefore, you would, first of all, require a procedure that executes the changes. You can call that procedure whenever you want.
In the next step you arrange for it to be called automatically. The way to do this would be to run the automation procedure every time you open the workbook. That procedure would check when the month change was last done. If the current date isn't within the same month as the last update a new update would be run, else, nothing is done. This method would ensure that the update is run even if the first day of the month is a holiday. Modifications would be made whenever the workbook is first opened in a new month.
The problem is that you must keep a record of when the last update was run. This record can be made accessible to the user or hidden from him but, when all chips are down, it can be manipulated. If you are the only user being able to manipulate is an advantage. In other cases it may be not that. There never is any danger of gross falsification because the sum isn't changed, but you must look at your own circumstances to decide whether automation is really in your best interest as compared to manual updates.
Either way, the first step would be to get the code to do the modifications. I'll be happy to do that for you if you confirm that I understood your intentions correctly.
If this is the case there might be a problem with resetting the Current Additions (hours accrued) to zero. If this value is calculated using the worksheet formula setting it to zero would imply altering data at the source. You haven't shown us where that source might be.

@Faustulus
Yes, you understood my intentions and I appreciate your assistance in sorting through the spaghetti.  :)

So checking to see if today's date is within the Renewal range in order to trigger the PTO Accrual formula wouldn't work?

I was looking to have it work as follows...

The formula/ code would check today's date to see if it's within column AA
(formula ie: IF( AND( TodaysDate >= {AA2} "1/1/13" , < {AA3} "2/1/13" ))

**THEN**

IF "True"  update PTO balance by summing Accrued Hrs & Previous PTO Balance
IF "False" don't perform any calcuation.

This should be ran only once so I would need the ability to check to see if the Accrued PTO hours were added for the current month.

Kindest Regards,

I don't see the advantage of your suggestion. You need a range (say, January) and I don't understand where the row numbers for column AA should come from that I need in order to define January. Then, you need a record of that an update was done. This record must probably be specific to the January update, meaning you might have to keep 12 records.
My suggestion only has the date of the last update. A comparison is made between today's date and the last update date. If it isn't in the same month and update is made, the date of the last update is reset and will be within the same month until next month. I think this is very simple and foolproof. However, it is the method of automation we are discussing.
I had raised concerns that automation may not be in your best interest. I had also pointed out that you need a program that you can run automatically or manually. Discussing the best way to run it is bound to be fruitless while it doesn't exist.
I have pointed out that the program will require the accumulated hours to be reset and that no idea has been put forward on how that might be done. While that issue remains unresolved you won't ever have a program. I urge you to focus on getting the program first.

Ok faustulus.. Have you had an opportunity to look at the attached z sample workbook to see how column AA is setup?

I'm seeking a formula to check to see if a new month has started if so sum 2 numbers if not do nothing...

With an automated feature to check to see if the update was done...  Since it should be done once a month

Your column AA is at the core of your suggestion how to determine whether an update is needed or not.  How and where do you suggest to record whether it has been done already?

I see your point ... I guess if I'm using VBA I could have a code to run on the 1st of every month

I pieced together the following to run when the workbook is opened.  It'll sum the Accrued Hours with the PTO Balance if it's the 1st of the month.

Option Explicit

Private Sub Workbook_Open()

On Error Resume Next

If Day(Now()) = 1 Then
 Call PTOAccrual
End If

End Sub

Private Sub PTOAccrual()
 
  Dim LastRow       As Long
  Dim iRow          As Long
  Dim iCol          As Integer
  LastRow = 0

  
  'sum code
    Workbooks("SampleVacationAccrual2.xlsm").Sheets("data").Select
    
  'Find last row
  For iCol = 8 To 9       'Columns H:I
     iRow = Cells(65536, iCol).End(xlUp).Row
     If iRow > LastRow Then LastRow = iRow
  Next iCol
  
  With Application.WorksheetFunction
  
     'Place row totals in column E
     For iRow = 1 To LastRow
        Cells(iRow, "J") = .Sum(Range(Cells(iRow, "H"), Cells(iRow, "I")))
     Next iRow
     
     'Place column totals in row after current last row
     For iCol = 8 To 9
        Cells(LastRow + 1, iCol) = .Sum(Range(Cells(1, iCol), Cells(LastRow, iCol)))
     Next iCol
  
  End With
  
'copy current PTO balance and paste into "Previous PTO balance" field
    Workbooks("SampleVacationAccrual2.xlsm").Sheets("data").Select
    Range("J3:J52").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("H3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  
 Workbooks("SampleVacationAccrual2.xlsm").Save

End Sub

Select allOpen in new window

This will only run if it's the 1st of the month preventing it from being ran multiple times during a month.  I tested it by changing the "1" into a "5" and stepping through it.

Private Sub Workbook_Open()

On Error Resume Next

If Day(Now()) = 5 Then
 Call PTOAccrual
End If

End Sub

Select allOpen in new window

SampleVacationAccrual2.xlsm

As an aside, I suggest that you amend the table you have in your Rules worksheet to look like the amended table you find in the attachment. This would enable you to use this formula in column I of your Data Sheet:-

=VLOOKUP(E3,Rules!$E$2:$F$6,2)

Select allOpen in new window

If you like the idea you can just copy the entire worksheet to your project.
130104-Rules.xlsx

Hi Faustulus...  I apologize about you not receiving your points as I thought this was accepted days ago when I 1st reviewed your recommendation.

I appreciate your time and the work you did to get me going!  

Kindest Regards,

Tonya~