Dating formula auto cell update based on the date

June 13, 2013 at 04:27:58
Specs: Windows 7 Enterprise
I know this is an advanced question. So I will be specific.

A61 =TODAY()

Now, based on date, I want to add 2.5 to the numerical value in cell B22. So, when the first of the month hits, I want the value in B22 to have 2.5 more added to it.

I know it would be something like, WHEN A61 VALUE=1stOFTHEMONTH;THEN B22VALUE+2.5()

I know what I want it to do, however not how to apply it. If anyone can help me out, please, and thanks for even entertaining me. Just to give more information, this is a military tracker for leaves and passes. I want it automated.

See More: Dating formula auto cell update based on the date

June 13, 2013 at 05:42:29
Possibly a macro would even be helpful.

Say, for instance, the value in B22 is 30. We all now that the next value would be 32.5. I just wan to know how to do it so I'm not number plugging for 45-60 people every month.

Report •

June 13, 2013 at 08:50:18
I do believe that this will require a macro, but I have a couple of questions:

1 - You specifically mentioned B22, then you said "45-60 people every month". Does this mean that you need to update the value in 45-60 cells, 45-60 worksheets, or something different.

Since a macro needs specific instructions about which cells to change, we need to know something about those 45-60 other cells.

2 - Is the value in all 45-60 cells the same or is the value specific to each cell and the only consistency is the addition of the 2.5?

The other issue I see is that using the TODAY function might make this a little difficult. What ever triggers the checking of the date can only happen once a day. If the date is checked every time the sheet calculates, the 2.5 will be added multiple times per day which would not be a good thing.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

June 13, 2013 at 16:18:18
OK, I thought about it and came up with something that seems to work.

What this code does is use a "flag" in Sheet1!A1 to determine if it should add 2.5 to B22 based on 2 criteria:

1 - Is it the 1st of the month?
2 - Is this the 1st time that the workbook has been opened on the 1st of the month?

Remember, we only want to add 2.5 once, not every time you open the workbook on the 1st of the month.

Note: This code does not use the TODAY function in A61 to determine the Date. It determines the Date by checking the system date directly within VBA.

Let's assume it's the last day of the month and you open the workbook. Sheet1!A1 will be set to "No" by the first instruction. In fact, Sheet1!A1 will be set to "No" every time time you open the workbook as long as it is not the first of the month.

The code will then check both the Day of the month and the contents of Sheet1!A1. If the Day = 1 and Sheet1!A1 = "No", then it must be the first time that the sheet was opened on the 1st of the month. If both of those conditions are TRUE then
Sheet1!A1 will be set to "Done" and it will add 2.5 to B22.

For the rest of the day (on the 1st) Sheet1!A1 will be "Done", so both If's will fail and the code will simply exit. On the 2nd of the month (and every day of that month) Sheet1!A1 will be set to "No", so the the If section that adds 2.5 will be FALSE and the code won't do anything else.

The way to test this code is to set your system date to various dates, including the 1st of a month and see if it does what you want. One key note: If you have anything on your system that monitors the system date, such as a calendar app, it's going to react to the changes you make to the system date, so be aware.

To use the code...

1 - Open your workbook and press Alt-F11 to open the VBA editor.
2 - In the right hand pane look for a module called ThisWorkbook.
3 - Double click ThisWorkbook to open the module
4 - Paste the following code into the pane
5 - Save the workbork as an xlsm or xlsb file so that the macro can run each time you open the workbook.

Private Sub Workbook_Open()

'If it's not the 1st of the Month, set Sheet1!A1 to "No"
   If Day(Date) <> 1 Then Sheets(1).Range("A1") = "No"

'If it's the 1st of the Month and it's the first time that the
'workbook has been opened that day, add 2.5 to B22 and
'set Sheet1!A1 to "Done" so that no more additions will be done that day.
  If Day(Date) = 1 And Sheets(1).Range("A1") = "No" Then
   Sheets(1).Range("B22") = Sheets(1).Range("B22") + 2.5
   Sheets(1).Range("A1") = "Done"
  End If

End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question