Change dates using Macro
Hi Guys,
I currently work with the data that need to change the dates day by day/daily, I usually use CTR+H to replace the date.
but I think there is a easier way to change it just by one click using macro code. (I'm using Office 2016)
can you guys help me. I already attach the file
Note: my data is daynow - 1 , so if today is 08 March 2022 in the data should be 07 March 2022
hope you understand
I also learn macro, but its kinda hard to learn by ourself without a friend or a mentor
not clear exactly where you want to update this date, but hopefully you can modify the below to suit your needs.
- the code updates the value in cell D2
- Now() -1 returns the current date/time less 1 day
- the RoundDown function removes the time, to leave only the date
Please Login or Register to view this content.
<<< If you have valued anyone's contributions in this thread, please click * to thank them for their efforts
Sorry I dont explain that.
I want to change in all active date. All dates in Row D automatically change
The code assumed that in the beginning condition of the workbook is something like this :
In column D starting from D2 to the last row with value, there is either empty row or row where the value will be only date ---> it doesn't matter what date they are.
Example :
D2 value 1 Nov 1964
D3 value "blablabla"
D4 to D7 empty
D8 value 1 Jan 2019
D9 value 5 Jul 2001
D10 empty
D11 empty
D12 value 7 Mar 2022
D13 empty
D14 value 1 Dec 1964
D15 value 8 Nov 2071
.
.
.
until the last row of column D value 17 Aug 1945.
So when the workbook is open,
the user see in column D (start from D8) like above.
Now what the user want is, all those rows with date value is changed to yesterday date.
So if the workbook is open on 13 Dec 2094, after the user run the macro,
those rows with date in column D change to 12 Dec 2094.
D2 value 12 Dec 2094
D3 value "blablabla"
D4 to D7 empty
D8 value 12 Dec 2094
D9 value 12 Dec 2094
D10 empty
D11 empty
D12 value 12 Dec 2094
D13 empty
D14 value 12 Dec 2094
D15 value 12 Dec 2094
.
.
.
until the last row of column D value is 12 Dec 2094.
Please Login or Register to view this content.
Last edited by karmapala; 03-08-2022 at 12:00 AM.
this is functional very well, but jud=st in row D2, I want run it in All cloumn with the date in it. Note, that that will empty and fill different column everyday.Originally Posted by AskMeAboutExcel
not clear exactly where you want to update this date, but hopefully you can modify the below to suit your needs.
- the code updates the value in cell D2
- Now() -1 returns the current date/time less 1 day
- the RoundDown function removes the time, to leave only the date
Please Login or Register to view this content.
try
Please Login or Register to view this content.
this is work very well. I also add this first answer to fill my data in D2. thanks Alltry
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks