+ Reply to Thread
Results 1 to 4 of 4

Highlighting specific days on a calendar based on work patterns

  1. #1
    Registered User
    Join Date
    03-08-2018
    Location
    Derbyshire, England
    MS-Off Ver
    Excel 2016, Office 365
    Posts
    31

    Highlighting specific days on a calendar based on work patterns

    Hi,

    I have an attendance tracking spreadsheet that I put together for my manager a couple of years ago so that he can see at a glance who’s in the office each day, and also record absences for various reasons. I couldn't find any templates that met my needs at the time. It’s essentially a very long spreadsheet like a calendar with the dates across the top and each employee’s name down the side – one row for each employee, and it’s split into “flexi-periods”, the 4 week blocks of time that are corporately used for recording time, with hyperlinks as short-cuts to go to the different time periods.

    It picks up each employee’s working pattern from a configuration page that has the days of the week across the top and the employees’ names down the side, with a ‘Y’ (for yes) for the days an employee works. This automatically fills in on the attendance tracker the days each employee’s in the office, but then the manager can overwrite it using a drop-down list to change it to a variety of reasons for absence.

    It works well for full time employees, and for some part-time employees, but there are some who work a specific day on alternate weeks. To get it to fill in the working pattern for each employee for the year I simply have to enter the start date for the year on the configuration page, and it fills everyone’s working patterns in, but with the ones who work a specific day on alternate weeks, I have to go through it and manually alter whichever day they work alternate weeks for the whole year. Usually it’s where it’s a job-share post, and one works the beginning of the week – Monday, Tuesday and alternate Wednesdays – and the other works the end of the week – alternate Wednesdays, when their job share partner’s not in, and Thursday and Friday. It’s further complicated by the fact that if there’s a public holiday in the week, neither works the alternate day and they split the remaining days in that week between them, and then the following week go back to the regular pattern of working alternate Wednesdays each.

    It's a pain having to go through and manually change those at the start of every year (especially as currently there are 4 employees with that working pattern), and I’ve been wracking my brains trying to figure out it there’s a way I can automate it. I did think that I could possibly somehow make each week a table and name them by their week number, and somehow set something up (no idea what or how) so that the Wednesday for the employee is only filled in for either odd or even weeks, but the fact that if there’s a public holiday in the week the rule doesn’t apply would throw it all out of whack. It's further complicated because any formulas wouldn't be the same for every employee, and though at the moment there are 4 employees on this kind of work pattern, in the future there could potentially be more or fewer. I'm guessing that to future-proof it I'd need to use the same formula for all employees and add it into the formula that currently highlights the days, additonally testing whether the week's odd or even (and I suppose to start off, which variation of the work pattern they start on at week 1), but it would need to leave the days for full-time employees, or those on part-time fiexed days, unaffected. I haven't a clue where to start on it!

    I’ve attached an anonymised copy of the spreadsheet so that you can see what it’s like. The working patterns it's showing are the result of the formulas using the working day information from the configuration page. Normally there is protection on the attendance tracker page to protect the formulas, but I've taken it off for this example (although actually if my manager enters an absence reason, it overwrites the formula anyway - the formulas just save either me or my manager having to input each employee's normal working pattern). For w/c 24/2/20 and w/c 2/3/20 I've edited it manually to show the employees who work alternate Wednesdays (Persons 2 and 3, and Person 8 and 11. The following week shows how you can use drop-down lists to enter absence reasons. If you click the 'Flexi-period 3' hyperlink you'll see that Excel's also automatically put in public holidays, which again it picks up from a list on the configuration page, and it's these days that really mess up me working out who's working which Wednesdays!

    Is this do-able? Any suggestions as to how I may be able to accomplish this would be gratefully received.
    Attached Files Attached Files

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Highlighting specific days on a calendar based on work patterns

    for p2
    how can we know there is an annual leave?
    for halfweek:
    =VLOOKUP($H11,Configuration!$D$3:$L$22,2 +WEEKDAY(Y$8,2),)

    btw row10 this way is better
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-08-2018
    Location
    Derbyshire, England
    MS-Off Ver
    Excel 2016, Office 365
    Posts
    31

    Re: Highlighting specific days on a calendar based on work patterns

    Hi, thanks for taking a look at this.

    Quote Originally Posted by tim201110 View Post
    for p2
    how can we know there is an annual leave?
    The manager selects the appropriate absence code from the drop-down list. The formula essentially fills in everyone's work pattern at the start of the year, saving either myself or the manager from having to enter it manually, but the manager can manually overwrite the work pattern to show absences from the office.

    for halfweek:
    =VLOOKUP($H11,Configuration!$D$3:$L$22,2 +WEEKDAY(Y$8,2),)

    btw row10 this way is better
    I'm sorry, I don't understand. The VLOOKUP seems to just put in the work pattern the same as the existing formula, but I don't understand what you mean about row10.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,164

    Re: Highlighting specific days on a calendar based on work patterns

    This proposal builds off Tim's.
    The formula used is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    There will need to be some manual entries using data validation:
    1. "AW" (alternate Wednesday) is selected for cells K12 and K18
    2. Since R12 is "A" R13 will need to be "I"
    3. Two conditional formatting rules have been added to hide "AW" and zeros
    4. "AW" is added to the data validation list
    Note that the formula and conditional formatting is applied to the range I11:AO30
    On the Configuration sheet
    1. Y's are changed to I's
    2. AW is placed in cells to signify those employees work alternating Wednesdays.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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] Change your Calendar work days and the start of your week Excel 2010
    By jason.hampton in forum Excel General
    Replies: 4
    Last Post: 08-08-2015, 09:58 AM
  2. Distributing work across the calendar days based on effort taken per task
    By sriramdh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-18-2014, 02:11 AM
  3. [SOLVED] How do I display work days only in a calendar format
    By john dalton in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-09-2014, 10:47 AM
  4. Replies: 2
    Last Post: 07-31-2013, 02:00 PM
  5. Highlighting work days declared by workers
    By Ranulor in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2013, 09:05 AM
  6. Replies: 0
    Last Post: 01-14-2011, 01:05 PM
  7. Calculating based on calendar days
    By Pennypacker in forum Excel General
    Replies: 0
    Last Post: 02-19-2005, 04:28 AM

Tags for this Thread

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