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

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 12-03-2019, 07:18 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: 24
PrincessApril is on a distinguished road
Default


Awesome. I had seen that but forgot that I had added new rows (my first blank cell in the target column is G12, so I changed to that in the code). Thank you so much again. This is so helpful!
Attached Images
File Type: png screen shot clt tracker.png (34.0 KB, 6 views)
Reply With Quote
  #17  
Old 12-03-2019, 09:10 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: 24
PrincessApril is on a distinguished road
Default

So my colleague has informed me of a small issue. Many case managers will be inputting intake dates from several years ago, so the current code will only add one year rather than bring the date to the current year.

Is it possible to make the year update to the current year rather than adding 1 (still keeping the month and day the same, and still only making the change if 12 months have passed)?
Reply With Quote
  #18  
Old 12-03-2019, 09:49 AM
NoSparks NoSparks is online now 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
Code:
                cel = DateAdd("yyyy", 1, cel)
to
Code:
                cel = DateSerial(Year(Date), Month(cel.Value), Day(cel.Value))
Reply With Quote
  #19  
Old 12-03-2019, 09:57 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: 24
PrincessApril is on a distinguished road
Default

Gives me a runtime error (although did seem to work). Not sure how to clear off the runtime error. My columns and rows have shifted again (target column is now E and first blank cell is E4).
Attached Images
File Type: png tracker ss runtime error.png (20.4 KB, 5 views)
Reply With Quote
  #20  
Old 12-03-2019, 12:58 PM
NoSparks NoSparks is online now 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

Are you trying to use that code for the sheet pictured in post #16, where the dates are in column G ?
Reply With Quote
  #21  
Old 12-03-2019, 02:07 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: 24
PrincessApril is on a distinguished road
Default

Quote:
Originally Posted by NoSparks View Post
Are you trying to use that code for the sheet pictured in post #16, where the dates are in column G ?
I've moved things around. Attached is a screenshot of the current working version. Target column is now E and first cell is E6 (right now it doesn't seem to work anymore--no runtime error or anything just doesn't seem to process. I have macros enabled).


Code:
Sub UpdateIntakeDate()
    Dim rng As Range, cel As Range, lr As Long
With Sheets("Client Tracker")
    lr = .Cells(.Rows.Count, "E").End(xlUp).Row
    If lr < 12 Then Exit Sub
    Set rng = .Range("E6:E" & lr)
    For Each cel In rng
        If cel <> "" Then
            If Date >= DateAdd("yyyy", 1, cel) Then
               cel = DateSerial(Year(Date), Month(cel.Value), Day(cel.Value))
            End If
        End If
    Next cel
End With
End Sub
Attached Images
File Type: png tracker ss updated.png (33.3 KB, 4 views)
Reply With Quote
  #22  
Old 12-03-2019, 02:35 PM
NoSparks NoSparks is online now 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

Here's the code, commented line by line for better understanding.
Code:
Sub UpdateIntakeDate()
    Dim rng As Range    'the overall range to work on
    Dim cel As Range    'the one cell to work on at a time
    Dim lr As Long      'the last row with data in the column
    
With Sheets("Client Tracker")

'determine last used row in the column
    lr = .Cells(.Rows.Count, "E").End(xlUp).Row
'if lr is less than first row that would have dates don't go any further
    If lr < 6 Then Exit Sub    '<<<<<<<<<<<<<
    
'set the range to work on
    Set rng = .Range("E6:E" & lr)
    
'work on the cells in the range one at a time
    For Each cel In rng
        'if the cell IS NOT blank
        If cel <> "" Then
'check if today is greater than or equal to the date in the cell plus one year
            If Date >= DateAdd("yyyy", 1, cel) Then
               'if it is then give it the new date
               cel = DateSerial(Year(Date), Month(cel.Value), Day(cel.Value))
            End If
        End If
'move on to the next cel by going back to the start of For Each
    Next cel

End With

End Sub
Reply With Quote
  #23  
Old 12-04-2019, 10:15 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: 24
PrincessApril is on a distinguished road
Default

That's very helpful NoSparks, thank you. This seems to work well, and I appreciate the comments (def helped me understand what was going on a bit better). Thanks for all your assistance throughout this thread!
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 04:00 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