+ Reply to Thread
Results 1 to 3 of 3

Preventive Schedule Automatic Plan Visit

Hybrid View

dontbugme1 Preventive Schedule Automatic... 03-11-2016, 05:47 AM
p24leclerc Re: Preventive Schedule... 03-11-2016, 06:22 PM
p24leclerc Re: Preventive Schedule... 03-11-2016, 08:01 PM
  1. #1
    Registered User
    Join Date
    12-17-2014
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    19

    Lightbulb Preventive Schedule Automatic Plan Visit

    Dear Team

    I am working on an Preventive maintenance schedule excel sheet,In which i would like to highlight Planned visit month based on Following par-maters 1)Start & End date of Contract
    2) No of visits to be done(Like Quarterly,Bimonthly,Monthly etc)
    3)Rest off cells should be white and planned visit should be highlighted

    Take an example for one of job we have contract start date:April 15 2015 & contract end date April 14 2016 with frequency Quarterly so the first visit should be in April month then accordingly in 12 months based on frequency i.e Quarterly next visit should be august,then december and then april of next year.

    Attached sheet for reference
    Attached Files Attached Files

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Preventive Schedule Automatic Plan Visit

    Are you looking for a VBA macro to do the highlight or a formula if, at all, possible?
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Preventive Schedule Automatic Plan Visit

    How about conditional formatting to highlight your cells?
    As your table is a monthly one, I added a Month column where you enter the number of month between maintenance.
    If a maintenance is done multiple time per month, you enter 1. Bimonthly =2. Quaterly=4 etc.
    This is because any maintenance done more then once in a month will be shown highlited every month.
    See attached workbook for a working conditional formatting for rows 2:8
    I hope this will help you.
    here is ine of the conditional formula. It will return 0 when it is time for the maintenance:
    =MOD(MONTH(Q$1)-MONTH($N$7),$M2)=0
    And here is the other conditional formula. It looks at the end date and revert the previous conditional formating.
    =Q$1>$O2
    Thinking about this, you might need one to look at the beginning date and make sure the maintenance is not
    highlighted if the column month is before that date.
    Attached Files Attached Files
    Last edited by p24leclerc; 03-12-2016 at 08:26 PM. Reason: added formula for the benefit of all

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Automatic Break Schedule
    By mdobrien3117 in forum Excel General
    Replies: 6
    Last Post: 11-06-2023, 04:10 AM
  2. Replies: 0
    Last Post: 10-27-2014, 11:54 PM
  3. [SOLVED] Automatic Seating Plan
    By sallynz in forum Excel General
    Replies: 3
    Last Post: 09-18-2014, 09:20 AM
  4. Rotation Visit Schedule
    By kombra1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2014, 10:36 AM
  5. Replies: 0
    Last Post: 03-26-2014, 05:23 AM
  6. automatic fill of expired bills in the financial plan
    By kghisla in forum Excel General
    Replies: 1
    Last Post: 09-08-2010, 11:44 PM
  7. [SOLVED] schedule plan for every 3 month in one sheet excel
    By jameela in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-14-2006, 07:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1