I have a sheet I use to schedule service for clients on a weekly basis. Once the schedule date is passed, I'm having to re-enter the new scheduled date for service. I'm looking to auto update the next service date, 7 days from the previous scheduled date. Any idea on a formula to use to auto update the next scheduled service date?
Maybe use another column to calculate.
C4
=IF(A4>$A$2,A4,A4+7*ROUNDUP(($A$2-A4)/7,0))
copied down.
Thank you for the reply. I see how that formula will work for finding the next scheduled service date. I see that it uses the original date in A4. I don't see that the date in C4 will auto update once the current date passes 7/17, or the date in C7 will update once the current date passes 7/21. Any ideas?? Thanks.
I set the A2 to 2023/7/17 18:41,C4 becomes to 2023/7/24 14:00. It's right?
Could you upload a sample file to describe these problem?
No, A2 needs to stay the same. See the new attached workbook.Originally Posted by windknife
My solution freezes A4:A7 as starting date.
C4:C7 is various date according to A2.
You can see attachment as detail.
If you want to let A4:A7 auto cacluate, I think you need VBA.
Another way:
=IF(A4>$A$2,A4,A4+CEILING($A$2-A4,7))
I think you are right. I have no idea how to do that. Much appreciated for your help.
Thank you for the reply. This seems to work as well, but I was looking for something that will update the date & time every 7 days. Like windknife said, VBA is probably the way to go.
Shall I move this to the VBA section for you?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Yes please. Thank you.
This code will automatically run every time the sheet is activated. It will compare column A to cell A2 and update column A accordingly.
Please note that in order to update column A, sheet 1 needs to be activated.
Right click on sheet 1 tab, View Code, then paste the code into:
Save fie as .xlsm
PHP Code:Option Explicit
Private Sub Worksheet_Activate()
Dim lr&, cell As Range
lr = Range("A4").End(xlDown).Row
For Each cell In Range("A4:A" & lr)
If Range("A2") > cell Then
cell = cell + 7
End If
Next
End Sub
Quang PT
Thank you for the reply. I tried using that code and saving as an xlsm, the dates are not updating 7 days ahead. See the attached. Any ideas?? Thanks.
Works fine for me with your uploaded file.
Remember this code is triggered by the worksheet activate event.
If you are already on this worksheet just click on another tab and then click back and it should run.
Click the * Add Reputation button in the lower left hand corner of this post to say thanks.
Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.
Ok, that makes sense. It is working on my sample sheet. When I apply the VBA code to my personal sheet, it does some really bizarre behavior. It's not updating the dates & times. My dates and times are between A35 and A47 with empty rows in between. Then, it puts the number 7 in cells from cell A49 all the way down to A4738. Not sure why it's doing that. I tried changing the one line in the VBA code lr = Range("A4").End(xlDown).Row to lr = Range("A35").End(xlDown).Row, and I changed this line - For Each cell In Range("A4:A" & lr) to For Each cell In Range("A35:A47" & lr). It's not working. Thanks for your help, much appreciated.
Last edited by AliGW; 07-20-2023 at 09:03 AM. Reason: Please do NOT quote unnecessarily!
It could be that column A has many intermittent empty cells, so the last row starting from cell A4 downwards is only the last row of the contiguous range of cells, not necessarily the last row of column A.
Due to the difference in your actual data compared to the original file, it is advisable to attach a new file with your actual data.
Here it is, thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks