• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

A Macro to Update a date for all the dates in a row

GN0001

Member
I need to design an Excel Spread sheet. I have 300 equipments that some of them are inspected every 3 days and some of them every 4 days and some of them every 7 days. This excel sheet shows the inspection date for a year. For example if an equipment is supposed to be inspected on 10/20/2010 and the frequency of inspection is set to every two days, if we shift the inspection date to 10/19/2010 and keep the same frequency, I want a macro to update the date for the whole year without me doing the copy, past and move the cell. Only with a running a macro


Please help me. I do appreciate it.

Regards,

Guity
 

Hui

Excel Ninja
Staff member
Guity

Why not just use formulas

If you have a matrix with columns for days and each row a piece of equipment

you could setup an equation to check if the equipment is due for a service based on its previous service and date of the column.


have a look at:

http://rapidshare.com/files/422830078/Guity_Sched.xlsx

Where I've put an example with and without equipment working weekends
 

GN0001

Member
Hello Hui,

When I click on this link, it takes me to some page that I think I need to set up account, and I did set up an account. I clicked the download button to download the file. but there was nothing to be downloaded, I mean there was not a file name or something to open or save. Please either double check the link or send me more instruction in how to open the file or send me a link that I can open it. I am looking forward to hearing from you.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 

GN0001

Member
Never mind, I was able to see the tables. Thank you so very much. But if you inspect the equipment one day earlier versus what is scheduled, how would you extend your update all across the row? Keep in mind, please that the schedule is for whole year. For example: If you are supposed to do the inspection on 10/05/2010, but something occurs and you need to change the inspection date and do it on 10/04/2010. Is there any macro to update the whole row? My second question is: why did you put + sign prior to if. What does it do?

My thanks can't be expressed in words,

Guity
 

Hui

Excel Ninja
Staff member
If you want to schedule it 1 day earlier type the date a day earlier and all the dates after that will adjust automatically

EG: If it was scheduled for the 15/10 and you want to schedule it on 14/10, type 14/10 on 14/10 and all the dates after will reschedule automatically.


Same with a day later, just type the date on the date and then delete the old scheduled date


You can copy formulas from before or after, above or below to repair mistakes


ps: Don't worry about the + sign, its a 20 year old habbit
 

GN0001

Member
You won't believe how much I appreciate your response. I am going to extend this schedule for the whole year and probably next year. However I may need some assistance in this spreadsheet. I will let you know. Thank you, Thank you... Guity
 
Top