+ Reply to Thread
Results 1 to 5 of 5

Leave Schedule - Excel VBA

  1. #1
    Registered User
    Join Date
    02-04-2018
    Location
    Sumatera, Indonesia
    MS-Off Ver
    MS Office 2010
    Posts
    29

    Leave Schedule - Excel VBA

    Hello,

    I need some help to make a leave schedule plan based on the date an employee is joining.
    Employee name = column C
    Joining date = column D
    Data start in row # 3
    Column D to column BDJ is date, starting from Jan 1, 2018 to Dec 31, 2021

    Below the conditions:
    Employee entitle for rotation off if employee has been working for 70 days
    Employee get rotation off for 14 days

    What I need:
    Based on cell value on column D, joining date
    1. 70 days after joining date, the column will be colored with red background
    2. The red background color will be 14 days or equal to 14 columns referring from the first day of employee's leave,
    3. It should be repeated to another columns based on conditions 1 & 2 above

    Example:
    If an employee is joining on Feb 2, 2018 (column D) then
    after 70 days working, until Apr 12, 2018
    Employee will get 14 days leave, starting from Apr 13, 2018 until April 26, 2018
    The color on that row, along 14 days or 14 columns will colored in red background
    And will be continue until Dec 31, 2021 at column BDJ

    To me, it is really impossible to write the code, but I do believe this forum having a magic code that could help me.

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Leave Schedule - Excel VBA

    Give this a go...using my template & incorporating some code learnt from bakerman2.
    Please Login or Register  to view this content.
    Edit
    change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 06-04-2018 at 06:44 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Registered User
    Join Date
    02-04-2018
    Location
    Sumatera, Indonesia
    MS-Off Ver
    MS Office 2010
    Posts
    29

    Re: Leave Schedule - Excel VBA

    Dear sintek,

    Many thanks for your helping hand. The code works like a charm. It's really great. Problem solved !

    Additional question :
    How to add "L" as leave for those coloring red background automatically?

    Thanks so much.

  4. #4
    Registered User
    Join Date
    02-04-2018
    Location
    Sumatera, Indonesia
    MS-Off Ver
    MS Office 2010
    Posts
    29

    Re: Leave Schedule - Excel VBA

    Dear sintek,

    Problem solved !!! I just add to your code ..
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Again, thanks so much. God bless you !!!

  5. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Leave Schedule - Excel VBA

    Well done...Glad you figured it out...Thanks for rep +
    May i ask why you are subtracting 91 and not 70+14(84)

+ 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. [SOLVED] Leave tracker - how to use formula to automate the total leave days
    By yiyi2 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-16-2018, 10:59 PM
  2. Small Program to calculate and input Personal leave in an employee schedule
    By curiousdesi in forum Excel Programming / VBA / Macros
    Replies: 28
    Last Post: 09-04-2015, 02:15 AM
  3. Replies: 5
    Last Post: 03-03-2015, 01:38 PM
  4. Need only leave days one by one from leave attendace full data
    By sathiyamoorthy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-18-2014, 03:23 AM
  5. Sick leave annual leave minutes spreadsheet
    By News12kim in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-22-2013, 03:28 AM
  6. How to create a Leave Schedule in excel 2007
    By datuelijah in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-19-2012, 09:21 AM
  7. Replies: 1
    Last Post: 05-18-2011, 10:46 PM

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