Microsoft Office Forums Automatically update user-inputted date after specified time range

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-29-2019, 03:42 AM
PrincessApril PrincessApril is offline Automatically update user-inputted date after specified time range Windows 10 Automatically update user-inputted date after specified time range Office 2019
Novice
Automatically update user-inputted date after specified time range
 
Join Date: Nov 2019
Posts: 23
PrincessApril is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 11-29-2019, 07:41 AM
ArviLaanemets ArviLaanemets is offline Automatically update user-inputted date after specified time range Windows 8 Automatically update user-inputted date after specified time range Office 2016
Expert
 
Join Date: May 2017
Posts: 508
ArviLaanemets has a spectacular aura aboutArviLaanemets has a spectacular aura about
Default

Solution attached
Attached Files
File Type: xlsx UpdateDate.xlsx (8.4 KB, 2 views)
Reply With Quote
  #3  
Old 11-29-2019, 10:50 PM
PrincessApril PrincessApril is offline Automatically update user-inputted date after specified time range Windows 10 Automatically update user-inputted date after specified time range Office 2019
Novice
Automatically update user-inputted date after specified time range
 
Join Date: Nov 2019
Posts: 23
PrincessApril is on a distinguished road
Default

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!.
Reply With Quote
  #4  
Old 11-30-2019, 07:47 AM
ArviLaanemets ArviLaanemets is offline Automatically update user-inputted date after specified time range Windows 8 Automatically update user-inputted date after specified time range Office 2016
Expert
 
Join Date: May 2017
Posts: 508
ArviLaanemets has a spectacular aura aboutArviLaanemets has a spectacular aura about
Default

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)
Reply With Quote
  #5  
Old 11-30-2019, 08:17 AM
PrincessApril PrincessApril is offline Automatically update user-inputted date after specified time range Windows 10 Automatically update user-inputted date after specified time range Office 2019
Novice
Automatically update user-inputted date after specified time range
 
Join Date: Nov 2019
Posts: 23
PrincessApril is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
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!
That's a good point. I thought it would be possible to run a macro a single time when opening the worksheet.

Quote:
Or user simply enters formula instead of date into cell. Something like
Code:
=DATE(YEAR(TODAY(),10,22)
I am actually using that formula elsewhere in the sheet. It is great for many purposes, but it will not work here. Take the example above of June 7. This formula would change June 7, 2019, to June 7, 2020, as soon as today's date hits January 1, 2020. That means the user loses half a year of reminders for their work. Although we do have some dates based on the calendar year (and I handle those with other formulas in my worksheet), several of our reminders are not dependent on the calendar year but on a 12-month cycle that begins with the intake date, which could be any date during the year.

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.
Reply With Quote
  #6  
Old 11-30-2019, 08:37 AM
PrincessApril PrincessApril is offline Automatically update user-inputted date after specified time range Windows 10 Automatically update user-inputted date after specified time range Office 2019
Novice
Automatically update user-inputted date after specified time range
 
Join Date: Nov 2019
Posts: 23
PrincessApril is on a distinguished road
Default

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:
Code:
Private Sub Worksheet_Activate()
   Dim N As Long, r As Range, rng As Range
   Dim d As Date
   N = Cells(Rows.Count, "G").End(xlUp).Row
   Set rng = Range("G3:G" & N)

   For Each r In rng
      If IsDate(r.Value) Then
         d = r.Value
         If d < Date Then
            r.Value = DateSerial(Year(d) + 1, Month(d), Day(d))
         End If
      End If
   Next r
End Sub
Source: microsoft excel - How do I add 1 year to a specific date once that date has passed? - Super User

Any ideas on what the problem with that code might be? I did the following:
  • Saved my workbook as xlsm
  • Hit Alt + 11 and pasted the code
  • Changed G3 to G6 to match the first cell of data in my worksheet
  • Saved and Reopened the workbook.
  • Accepted the "enable content" notification, saved and reopened again, but the old date does not update (I have cell G6 as June 7, 2018, for testing but it does not update).

Last edited by PrincessApril; 11-30-2019 at 11:22 AM.
Reply With Quote
  #7  
Old 11-30-2019, 11:52 AM
NoSparks NoSparks is offline Automatically update user-inputted date after specified time range Windows 7 64bit Automatically update user-inputted date after specified time range Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 751
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Have a look at this
Attached Files
File Type: xlsm DateUpdating_PrincessApril.xlsm (16.6 KB, 1 views)
Reply With Quote
  #8  
Old 11-30-2019, 12:11 PM
PrincessApril PrincessApril is offline Automatically update user-inputted date after specified time range Windows 10 Automatically update user-inputted date after specified time range Office 2019
Novice
Automatically update user-inputted date after specified time range
 
Join Date: Nov 2019
Posts: 23
PrincessApril is on a distinguished road
Talking

Quote:
Originally Posted by NoSparks View Post
Have a look at this
Oh yea...awesome!
Reply With Quote
  #9  
Old 11-30-2019, 12:14 PM
PrincessApril PrincessApril is offline Automatically update user-inputted date after specified time range Windows 10 Automatically update user-inputted date after specified time range Office 2019
Novice
Automatically update user-inputted date after specified time range
 
Join Date: Nov 2019
Posts: 23
PrincessApril is on a distinguished road
Default

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)?
Reply With Quote
  #10  
Old 11-30-2019, 12:48 PM
NoSparks NoSparks is offline Automatically update user-inputted date after specified time range Windows 7 64bit Automatically update user-inputted date after specified time range Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 751
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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.
Reply With Quote
  #11  
Old 11-30-2019, 06:40 PM
PrincessApril PrincessApril is offline Automatically update user-inputted date after specified time range Windows 10 Automatically update user-inputted date after specified time range Office 2019
Novice
Automatically update user-inputted date after specified time range
 
Join Date: Nov 2019
Posts: 23
PrincessApril is on a distinguished road
Default

Thank you for clarifying, and thanks again for the elegant solution!
Reply With Quote
  #12  
Old 12-02-2019, 09:22 AM
PrincessApril PrincessApril is offline Automatically update user-inputted date after specified time range Windows 10 Automatically update user-inputted date after specified time range Office 2019
Novice
Automatically update user-inputted date after specified time range
 
Join Date: Nov 2019
Posts: 23
PrincessApril is on a distinguished road
Default

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
What might we code in to avoid the run-time issue for a blank worksheet?
Reply With Quote
  #13  
Old 12-02-2019, 10:26 AM
NoSparks NoSparks is offline Automatically update user-inputted date after specified time range Windows 7 64bit Automatically update user-inputted date after specified time range Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 751
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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
Reply With Quote
  #14  
Old 12-02-2019, 11:05 AM
PrincessApril PrincessApril is offline Automatically update user-inputted date after specified time range Windows 10 Automatically update user-inputted date after specified time range Office 2019
Novice
Automatically update user-inputted date after specified time range
 
Join Date: Nov 2019
Posts: 23
PrincessApril is on a distinguished road
Default

It looks like I get a run-time error 13 (type mismatch). And it highlights the same row of code.
Reply With Quote
  #15  
Old 12-02-2019, 01:17 PM
NoSparks NoSparks is offline Automatically update user-inputted date after specified time range Windows 7 64bit Automatically update user-inputted date after specified time range Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 751
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

did you see that I had edited for the value of lr ?
changed a 6 to a 5
Reply With Quote
Reply

Tags
automatically, update, year

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatically update user-inputted date after specified time range Auto update excel graph range, ignore data when date reads 00-Month-00 SavGDK Excel 2 06-24-2016 08:05 AM
Automatically update user-inputted date after specified time range 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
Automatically update user-inputted date after specified time range How to update automatically the “file name” as we do for the “Date”? Jamal NUMAN Word 2 01-06-2011 02:43 PM


All times are GMT -7. The time now is 12:23 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft