We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
COURSE of the MONTH
13 days, 23 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Excel VBA Functions- Perform Calculation if Value is found to be within Specified Range
Posted on 2013-01-03
Hi Experts,
I have a workbook where I need to manage vacation accrual throughout a calendar year.
On the 1st of each month I need the accrued vacation hours amount (stored in column "E") to be added to the "Previous PTO BAL AVAIL" column (" I ") and then place the new amount in the "PTO Bal" column (" J ").
For Ex:
I need the code to work as follows:
**The tricky part, when a new month start I would need the amount stored in the "PTO Bal" column to now be stored in the "Previous PTO Bal Avail" column and then run through steps 1 - 3 again**
I attempted to record a Macro to see if I could figure out how to get this to work but I'm more confused now than before..
Thank you for your time in Advance! :)
SampleVacationAccrual.xlsm
I have a workbook where I need to manage vacation accrual throughout a calendar year.
On the 1st of each month I need the accrued vacation hours amount (stored in column "E") to be added to the "Previous PTO BAL AVAIL" column (" I ") and then place the new amount in the "PTO Bal" column (" J ").
For Ex:
I'll have a PTO Balance column (col "J") which will contain the current vacation balance.
I'll have a cell entitled "TodaysDate" aka "C1" and a named ranged called "RenewalMonth" which will contain the 1st day of each month for the entire year.
I need the code to work as follows:
1.
Find TodaysDate in the "RenewalMonth" range column "AA"2.
Add "hours accrued" (col "E") to the "Previous PTO Bal Avail"3.
Store the new balance to be in "PTO Bal" column "J"**The tricky part, when a new month start I would need the amount stored in the "PTO Bal" column to now be stored in the "Previous PTO Bal Avail" column and then run through steps 1 - 3 again**
I attempted to record a Macro to see if I could figure out how to get this to work but I'm more confused now than before..
Range("J6").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(TodaysDate=R[-2]C[17],R[-1]C[17],RC[17],R[1]C[17],R[2]C[17],R[3]C[17],R[4]C[17],R[5]C[17],R[6]C[17],R[7]C[17],R[8]C[17],R[9]C[17],R[10]C[17],R[11]C[17],R[12]C[17],R[13]C[17],R[14]C[17],R[15]C[17],R[16]C[17],R[17]C[17],R[18]C[17],R[19]C[17],R[20]C[17],R[21]C[17]),SUM(R[-1]C[-5],R[-1]C[-1]),R[-1]C[-1])"
Range("J6").Select
Thank you for your time in Advance! :)
SampleVacationAccrual.xlsm
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5-
5
11 Comments
by: javaftper - my apologies ... the unprotected workbook is located below...
thank you
SampleVacationAccrual.xlsm
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.
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,
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.
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
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.
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.
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
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
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:-
130104-Rules.xlsx
=VLOOKUP(E3,Rules!$E$2:$F$6,2)
130104-Rules.xlsx
The attached project sports the suggested improvement of the formula in column I of your Data worksheet and modified format of the Rules worksheet. BTW, I would name the range rather than referring to it as is, but the following works just as well with your original design.
I haven't used your column A:A. Instead, you will find a value in cell Data!J1. This is the date up to which the accrued hours are included in the PTO Balance (which is equal to the Prev. PTO balance). The date is set by the system and must always be the last day of a month. You can move this cell to another location by changing the value of the constant UpdateRange at the top of the CalcMan module.
Your workbook now has a custom document property (File/Info -> Properties -> Advanced Properties -> Custom. This property records the actual date of the last update. It has no function beyond giving this information. You can change the name of that property by changing the value of the constant UpdateProp at the top of the CalcMan module.
I strongly suggest that you delete the existing code in the code sheet of the Data worksheet because it can't run there. Since it serves no purpose nothing good can come from keeping it where it is, which isn't to say that it might not cause mischief.
You will find a much abbreviated event procedure in the ThisWorkbook code sheet. All remaining code is in the module CalcMan. At the Open event the procedure PTOBalanceUpdate is called. It establishes how many months have passed since the date in Data!J1. As long as the time is less than 1 month no action will be taken. Else, a message will inform you of how many months have lapsed and add the appropriate number of monthly accruals to the totals. The code doing that is largely the same as what you had before.
As I said earlier, you can manipulate the dates. It is essential for the action that the date in J1 should be the last day of any month. If it isn't the update may not occur on the first day of the month after next. If, as on January 1, you never open the workbook the update will run on any day thereafter. So, if you wish to add a few months' worth of updates while setting up your system you can just adjust the date in J1 to the date that matches your B/F data. The date in the property may serve as a check against misuse. Since the program will always set J1 to the ultimo of the previous month you may be able to tell by looking at both dates whether the date in the worksheet has been tempered with.
I hope you like this arrangement. Let me know if you need any changes.
131005-Vacation-Accrual.xlsm
I haven't used your column A:A. Instead, you will find a value in cell Data!J1. This is the date up to which the accrued hours are included in the PTO Balance (which is equal to the Prev. PTO balance). The date is set by the system and must always be the last day of a month. You can move this cell to another location by changing the value of the constant UpdateRange at the top of the CalcMan module.
Your workbook now has a custom document property (File/Info -> Properties -> Advanced Properties -> Custom. This property records the actual date of the last update. It has no function beyond giving this information. You can change the name of that property by changing the value of the constant UpdateProp at the top of the CalcMan module.
I strongly suggest that you delete the existing code in the code sheet of the Data worksheet because it can't run there. Since it serves no purpose nothing good can come from keeping it where it is, which isn't to say that it might not cause mischief.
You will find a much abbreviated event procedure in the ThisWorkbook code sheet. All remaining code is in the module CalcMan. At the Open event the procedure PTOBalanceUpdate is called. It establishes how many months have passed since the date in Data!J1. As long as the time is less than 1 month no action will be taken. Else, a message will inform you of how many months have lapsed and add the appropriate number of monthly accruals to the totals. The code doing that is largely the same as what you had before.
As I said earlier, you can manipulate the dates. It is essential for the action that the date in J1 should be the last day of any month. If it isn't the update may not occur on the first day of the month after next. If, as on January 1, you never open the workbook the update will run on any day thereafter. So, if you wish to add a few months' worth of updates while setting up your system you can just adjust the date in J1 to the date that matches your B/F data. The date in the property may serve as a check against misuse. Since the program will always set J1 to the ultimo of the previous month you may be able to tell by looking at both dates whether the date in the worksheet has been tempered with.
I hope you like this arrangement. Let me know if you need any changes.
131005-Vacation-Accrual.xlsm
Featured Post
Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses
Course of the Month13 days, 23 hours left to enroll
801 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.