#1
|
|||
|
|||
Automatically update user-inputted date after specified time range
Hi all,
I have a column where the user inputs a date of client intake, and several conditional reminders across the row are based on that date. After 12 months go by, I need the intake date to automatically update the year (but keep the day and month the same) so that a fresh set of conditional-highlighting reminders will be activated. If it's possible to do that without macros, great, but I'm guessing I have to use a macro, perhaps each time the worksheet is opened? Unfortunately I don't know the first thing here. Is there a way to check the dates in the column and update any that are a year old? (for my purposes, the column is G and the dates begin in row 5--the other rows are headers and such) (If it's easy to account for leap years, that's great--if not, I could just instruct users never to enter Feb 29 or create a data validation rule that prevents it) Thank you in advance for any assistance you can provide. Happy holidays for anyone celebrating. |
#2
|
|||
|
|||
Solution attached
|
#3
|
|||
|
|||
Hi Arvil,
That solution is really cool--thank you! The only issue I have now, though, is that all of my conditional-highlighting reminders in the rest of the row point to the original column (the user-inputted date). So I actually need the date to update within the same cell rather than a new cell. I don't quite see how to apply your solution to the same cell (I'm still wondering if it will be necessary to use an event macro to achieve that?). So let's say my user-inputted dates are in the G column, and one of those dates is June 7, 2019. That date should stay the same for 12 months. When the current day is June 7, 2020 (or after) and the user opens the worksheet, the date should automatically update to June 7, 2020 in the same cell. Then, all of my conditional-highlighting reminders will update for the user for the next 12 months. Then the date would automatically update again on June 7, 2021 (or whenever the user opens the worksheet on or after that date). Please let me know if that needs further clarification, and thank you again for your time and expertise!. |
#4
|
|||
|
|||
No formula can refer to same cell where it is. So yes, in case you want to overwrite the value without storing original one somewhere, then VBA is only solution.
When you want really to do this, then consider a procedure which is run manually or is called by open event of workbook at start of every year. When you use e.g. workseet change event, then the code is run every time you edit anything in any cell on worksheet. This may slow your workbook down considerably! Or user simply enters formula instead of date into cell. Something like Code:
=DATE(YEAR(TODAY(),10,22) |
#5
|
|||
|
|||
Quote:
Quote:
Updating the year in the target cell is a way of allowing the user to continue using the worksheet the next year without having to manually update the dates. As an alternative, I could turn the cell red when the year has passed so that the user knows to manually update, but feedback in my department is that they would like the dates to update automatically. |
#6
|
|||
|
|||
I found a proposed solution on another forum, but the code does not work for me (I don't see any reaction after I open the workbook...perhaps it needs updating or fixing):
Quote:
Any ideas on what the problem with that code might be? I did the following:
Last edited by PrincessApril; 11-30-2019 at 11:22 AM. |
#7
|
|||
|
|||
Have a look at this
|
#8
|
|||
|
|||
|
#9
|
|||
|
|||
Also, as I was tweaking the sheet name etc. in your solution to match my workbook, I realized that I did not do that for the previous one, so theirs probably works too.
Out of curiosity, could you let me know the difference between your approach and the one posted above from the other site? (Like does yours account for some nuance or difference or vice versa)? |
#10
|
|||
|
|||
My approach checks if todays date is greater than or equal to the G cell date plus one year.
The other checks if the date in the G cell is less than todays date. There is also no guarantee the other will execute, if the workbook was saved and closed with that particular sheet being the active sheet then the Sheet_Activate event does not trigger when the workbook is next opened, and if that's the only worksheet in the workbook it will never trigger, where as the Workbook_Open event triggers every time the workbook opens. |
#11
|
|||
|
|||
Thank you for clarifying, and thanks again for the elegant solution!
|
#12
|
|||
|
|||
One issue that I just realized is that if the worksheet is new (user has not yet input any dates in the target column), it gives a run-time error upon opening the worksheet. When I hit DeBug it highlights the following line:
Code:
If Date >= DateAdd("yyyy", 1, cel) Then |
#13
|
|||
|
|||
Try changing the macro to this
Code:
Sub UpdateIntakeDate() Dim rng As Range, cel As Range, lr As Long With Sheets("Sheet1") lr = .Cells(.Rows.Count, "G").End(xlUp).Row If lr < 5 Then Exit Sub Set rng = .Range("G5:G" & lr) For Each cel In rng If cel <> "" Then If Date >= DateAdd("yyyy", 1, cel) Then cel = DateAdd("yyyy", 1, cel) End If End If Next cel End With End Sub Last edited by NoSparks; 12-02-2019 at 10:45 AM. Reason: changed lr to < 5 |
#14
|
|||
|
|||
It looks like I get a run-time error 13 (type mismatch). And it highlights the same row of code.
|
#15
|
|||
|
|||
did you see that I had edited for the value of lr ?
changed a 6 to a 5 |
Tags |
automatically, update, year |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Auto update excel graph range, ignore data when date reads 00-Month-00 | SavGDK | Excel | 2 | 06-24-2016 08:05 AM |
Auto populating dates on timesheet for week from one inputted date | ohi | Word VBA | 3 | 12-04-2014 12:11 PM |
Date/Time Formula for Entry level XL user | talon1driver | Excel | 2 | 09-18-2014 02:32 PM |
Mail merge with filter using data inputted by user | Tornado70 | Mail Merge | 6 | 04-09-2013 07:16 AM |
How to update automatically the “file name” as we do for the “Date”? | Jamal NUMAN | Word | 2 | 01-06-2011 02:43 PM |