X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Automatically Change Date Vba, Office 2010    
post May 30 2019, 02:36 PM

Posts: 1,360
Joined: 17-November 13
From: Orewa New Zealand

I want to roll over the anniversary date

on my sheet1
In cell F20 I have a start date(Manually Entered)

In F22 I have some VBA that sets the anniversary date for a year from start date so far so good thumbup.gif
Sheet1.Range("F22").Value = DateAdd("d", 365, Range("F20"))

Now in a random cell(F218) I have this formula which works really well "=IF(DATEDIF(TODAY(),F22,"d")=1,DATE(YEAR(F22)+1,MONTH(F22),DAY(F22)),F22)"
what it does is if Today() is 1 day greater than anniversary date then update anniversary date by a year and it gives me the correct answer if it is not it keeps the original anniversary date

Now the question part
I would like to some how get that in vba so anniversary date updates with the new date automatically
So once the anniversary date has been reach the next day update the anniversary date by a year
Go to the top of the page
post May 31 2019, 03:43 AM

Posts: 1,751
Joined: 4-September 02
From: Oxford UK

Use YYYY instead of D to add a year (next year is a leap year)

Sheet1.Range("F22").Value = DateAdd("yyyy", 1, Range("F20"))

Iif(datediff("d",date(),Sheet1.Range("F22"))=1,Sheet1.Range("F22").Value = DateAdd("yyyy", 1, Range("F20")),Sheet1.Range("F22").Value)

Air code, untested

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    6th August 2020 - 08:13 PM