+ Reply to Thread
Results 1 to 13 of 13

Calendar Help with adding days off for single employee

  1. #1
    Registered User
    Join Date
    01-19-2019
    Location
    richmond va
    MS-Off Ver
    2013
    Posts
    5

    Calendar Help with adding days off for single employee

    Hello All,
    I am trying to merge four different excel spreadsheets so when an employee updates that sheet it updates a master list of employees off for the day. I have use paste link to add the four sheets on to one in my master list and now want to show all those names on the calendar when they are off. I have been using Pete_UK updated calendar and have been trying to make an adjustment to add days off for an employees. Basically have the employees name in column C and then 18 columns after it with their picks but I can't figure out how to adjust the formula to do that. Any help would be appreciated.

    The sheets look like this
    23-May 25-May 27-May 12-Jul 14-Jul 17-Jul 2-May 4-May 6-May 28-Sep 19-Apr 21-Apr 15-Jun 09-Apr 11-Apr 13-Apr 15-Apr 19-Mar 30-Sep
    Last edited by mantle0213; 01-19-2019 at 03:43 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Calendar Help with adding days off for single employee

    I've submitted many calendar files to this forum, so you'll have to remind me which one it is (just quote the first part of it).

    It would also help if you attached the modified Excel workbook, so I can see what you have been trying to do.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon for attachments, as it doesn't work on this forum.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    01-19-2019
    Location
    richmond va
    MS-Off Ver
    2013
    Posts
    5

    Re: Calendar Help with adding days off for single employee

    Thanks for the response,
    Basically I have 4 workbooks that have picks listed like in the pick sheet one. I wanted to import their picks into the calendar
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Calendar Help with adding days off for single employee

    Presumably the colours in your Pick file mean something - are they set up manually? Do any of them represent a start and end date (i.e. a range), or do they all relate to a single day?

    I also assume the names down column A are for different people (i.e. Name1, Name2 etc.)

    Are the different pick sheets for different departments or something like that?

    Pete

  5. #5
    Registered User
    Join Date
    01-19-2019
    Location
    richmond va
    MS-Off Ver
    2013
    Posts
    5

    Re: Calendar Help with adding days off for single employee

    The colors are actually meaningless just meant to make the rounds of picks easier to see but have no bearing on date ranges. The names down A would all be different names. The sheets are for four different battalions of people that just dont compete for the same days so they can overlap with a max of 16 off per day.

    Our vacation selection is a little weird but the rules of those wouldnt apply to the calendar. Im just trying to figure out how to have the names down column A and pull the dates from other columns to display on the calendar showing the people off.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Calendar Help with adding days off for single employee

    What's the significance of the empty column N in the pick sheet?

    Do all 4 pick sheets follow the same formats?

    Pete

  7. #7
    Registered User
    Join Date
    01-19-2019
    Location
    richmond va
    MS-Off Ver
    2013
    Posts
    5

    Re: Calendar Help with adding days off for single employee

    Just separates a vacation pick from holiday pick but doesnt matter for the calendar. Just trying to show the employee names off on a certain day since the limit is 16

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Calendar Help with adding days off for single employee

    I can transform your pick sheet to a two-column table of Name and Date, but it would be easier to remove the blank column N and the blank row 13, as these will give rise to meaningless dates like 0 Jan 1900.

    Also, the black area covering part of columns H to M would also cause these same problems, and would be better removed.

    Is that okay, given that this is an intermediate process aimed at getting the data into a form suitable for the calendar?

    Pete

  9. #9
    Registered User
    Join Date
    01-19-2019
    Location
    richmond va
    MS-Off Ver
    2013
    Posts
    5

    Re: Calendar Help with adding days off for single employee

    sure thing

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Calendar Help with adding days off for single employee

    Actually, that calendar is probably not the best one to use if you want to display up to 16 events on a single day. I have others that are more suitable, and I'll dig one out.

    Pete

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Calendar Help with adding days off for single employee

    I've attached the modified Pick sheet, with formulae in there to transform the data into two columns.

    I've deleted the empty row 13 and the empty column N, and also removed the black area and moved the dates so they are contiguous. I have inserted a new row at the top of the sheet, and changed the names to Name1, Name2 etc.

    The formula in column V just counts the number of entries for each person, and a cumulative running total is given in column W. From this, the name can be repeated down column Y, sufficient number of times are there are entries for that person, and then each individual date is shown in column Z. Some of your entries are "C/O", and there is one spurious date in cell J31 which should be corrected. These 2 formulae are copied sufficently to cover all the data (shown in cell W31.

    If you want to use this in the calendar file, you will need to fix the values for these two columns, then you can copy/paste them into the appropriate columns of the Activities sheet. Obviously, the entries with "C/O" will not be displayed on the Calendar sheet, so you might want to remove these.

    Hope this helps.

    Pete
    Attached Files Attached Files

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Calendar Help with adding days off for single employee

    I'm just taking a break now, but I'll get you a better calendar file later on.

    Pete

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Calendar Help with adding days off for single employee

    I've uploaded a calendar which is more suited to this task.

    I've put the dates and names from the other file into the Activities sheet (columns E and F), and I've also included a Leave_type column (G) where you could record a code for different types of leave, e.g. A for Annual Leave, S for Sickness etc. If you don't want to use it, just leave the column blank - column C determines what is displayed on the calendar, so you can see what the first 4 entries look like with some made-up data.

    On the Calendar sheet you can select the Month and Year using the drop-downs in cells K5 and K6. The calendar is set up to show up to 10 events each day. However, another drop-down in cell K10 enables you to select which range of 10 you want to look at (1 to 10, 11 to 20, 21 to 30 etc., so you can effectively have up to 100 items per day.

    Hope this helps.

    Pete
    Attached Files Attached Files

+ 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] Formula to calculate working days and calendar days per month-year ?
    By donny007 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-05-2018, 06:23 PM
  2. [SOLVED] Array formula to treat range of Holiday Days as Weekends when adding data to Calendar
    By leovfx in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-16-2017, 09:40 AM
  3. Matching two employee directories, and adding the Employee #
    By sevanseriesta in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-26-2015, 01:58 PM
  4. Employee Time Tracking - Sick days & Consecutive Days
    By notaguru6 in forum Excel General
    Replies: 6
    Last Post: 08-09-2013, 12:50 PM
  5. Replies: 0
    Last Post: 04-25-2011, 04:28 PM
  6. Excel 2007 : Adding 2 sets of data to a single calendar
    By cactusrmt in forum Excel General
    Replies: 0
    Last Post: 01-13-2011, 11:47 AM
  7. HOW CAN I FILL AN EMPLOYEE CARD WITH EXACT DAYS CALENDAR
    By LARGO16 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-19-2005, 09:06 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