+ Reply to Thread
Results 1 to 18 of 18

Excel calendar

  1. #1
    Registered User
    Join Date
    03-01-2019
    Location
    Lisbon
    MS-Off Ver
    2016
    Posts
    20

    Excel calendar

    hello, need your help please

    I have a monthly excel calendar and a list of people with their respective holiday dates for the year in a different sheet.

    Can it automatically populate the calendar based on the list of peoples holidays?

    thank you in advance.

  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: Excel calendar

    Yes, this is fairly straightforward using formulae, and I should be able to amend one of the calendar files that I have submitted to the forum.

    It would help if you attached a sample Excel workbook, to show the format of what you have at the moment (anonymise it first, so there is no confidential data).

    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. Do not try to use the Paperclip icon, as it doesn't work on this forum.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    03-01-2019
    Location
    London, England
    MS-Off Ver
    365
    Posts
    10

    Re: Excel calendar

    Pete,

    Can this be accomplished with multiple sheets/employees?

    Thanks

  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: Excel calendar

    @richdms,

    are you the same person as the OP (using a different user name)? If not, then you should start a new thread of your own, rather than trying to hijack someone else's thread (see the Forum Rules at the top of the screen). With your own thread, you can give your own details, and solutions will be tailored to suit your circumstances.

    Pete

  5. #5
    Registered User
    Join Date
    03-01-2019
    Location
    London, England
    MS-Off Ver
    365
    Posts
    10

    Re: Excel calendar

    Not a problem Pete. I have already started a thread, just thought as this was similar it was a good idea to merge the two. Apologies

  6. #6
    Registered User
    Join Date
    03-01-2019
    Location
    Lisbon
    MS-Off Ver
    2016
    Posts
    20

    Re: Excel calendar

    here it is the attachment
    Attached Files Attached Files

  7. #7
    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: Excel calendar

    Mike,

    thanks for attaching the file, but I know what a calendar looks like - I was hoping that you would include the "booking" sheet where staff list their holiday requirements, so that I can see how your data is laid out. I am interested in particular with how you record the dates for the holidays - do you have a start date column and an End date column to specify the range of dates, or do you have a Start date column with another column for number of days leave (and is this working days, or all days in the range?), or is every leave day listed separately so that you just have one date column?

    Obviously, there might be other data that you want to record, like name (separate columns for forename and surname, or just one column?), and department (shown on the calendar as well?), and you might have an "Approval" column where a manager or supervisor can approve the leave request.

    So, can you upload another file showing this sheet (with some made-up examples of leave requests), so I can generate the calendar from it?

    Pete

  8. #8
    Registered User
    Join Date
    03-01-2019
    Location
    Lisbon
    MS-Off Ver
    2016
    Posts
    20

    Re: Excel calendar

    you are absolutely right...I'm a noob!! SORRY!

    here it is now
    Attached Files Attached Files

  9. #9
    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: Excel calendar

    Thanks for that. I'm taking a break now, but I'll get back to you later. In the meantime, a few questions occur straightaway:

    If the status is "denied", do you want this displayed on the calendar (maybe with a different colour), or not?

    Do you have a pre-set list of Absence types, or is that column typed manually?

    Do you want the name shown as last name followed by first name, or the other way round?

    Pete

  10. #10
    Registered User
    Join Date
    03-01-2019
    Location
    Lisbon
    MS-Off Ver
    2016
    Posts
    20

    Re: Excel calendar

    Quote Originally Posted by Pete_UK View Post
    Thanks for that. I'm taking a break now, but I'll get back to you later. In the meantime, a few questions occur straightaway:

    If the status is "denied", do you want this displayed on the calendar (maybe with a different colour), or not? - it would be perfect

    Do you have a pre-set list of Absence types, or is that column typed manually? - yes I have a pre-set list

    Do you want the name shown as last name followed by first name, or the other way round? first and then last

    Pete
    see above answers

  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: Excel calendar

    Hi Mike,

    can you confirm how many employees you are likely to have off on any one day (i.e. maximum number). I've set the calendar up so that it shows up to 10 on any particular day, but if you need more I can adjust this.

    Everything else seems to be working okay at the moment.

    Pete

  12. #12
    Registered User
    Join Date
    03-01-2019
    Location
    Lisbon
    MS-Off Ver
    2016
    Posts
    20

    Re: Excel calendar

    up to 6 people.

    if you could share that calendar it would be great. Thank you for your time

  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: Excel calendar

    File is attached.

    On the Leave_bookings sheet I have inserted a few columns shown in green and blue (more later), so that your table of bookings occupies columns J to Q. I've added a new column here, so that you can choose the leave type by code using a drop-down (saves typing). The codes and their meanings are given in the key in columns S and T. You can leave it blank (as in O14), and this affects what is displayed on the calendar.

    The coloured columns on the left basically expand any date range into individual days, and also control what gets displayed. If there is no End_date then a single day is assumed. I've set it up (in E2) to show the First Name and Second Name and then the Leave code in brackets followed by an underscore if the leave request has been denied. I've copied the formulae in the green columns down to row 100, and those in the blue columns to row 300, but you need to ensure that these are copied down further to accommodate all your data. You can see that the 14 test entries in the bookings table have been expanded to row 41 because of a few date ranges, so it's important to ensure that the formulae cover all the data that you expect - you can see hyphens in columns D and H which show where the formulae are active.

    If the formulae are copied down sufficiently far, then you can hide columns A to I, so that your bookings table looks the same as before.

    On the Calendar sheet you can select the month and year of interest using the drop-downs in cells K5 and K6, and the display will automatically be adjusted to show this. Up to 10 names can be displayed each day, arranged in weekly blocks from Sunday to Saturday. A 30-day month which starts on a Saturday, or a 31-day month which starts on Friday or Saturday, will normally give rise to "orphan" days which spill into a 6th week for this Sunday to Saturday arrangement. To avoid this, I show these "orphan" days at the top of the calendar - you can see this if you select September 2018 as an example.

    There is another drop-down in K1 which uses conditional formatting to control how a "denied" leave request is displayed - if Yes it will be shown with a red background, if No it will be blanked out. Note that the screen has to be re-drawn for changes in this setting to take effect (i.e. re-select the same month if you change the setting).

    If you want to set this up for Portuguese instead of English, then you will need to change the names of the days in B3:H3. Also, you can see this formula in K4:

    =CHOOSE(K5,"January","February","March","April","May","June","July","August","September","October","November","December")

    so you will need to change the month names here. There is also an English phrase in cell E1 - "for the month of " which will need translating. The key to leave types (which is reproduced on the Calendar sheet for information), should be translated in column S of the Bookings sheet, and will propagate through.

    Anyway, I think this does what you want, and it would be possible to set up another sheet to do some analysis of the different leave types for each employee.

    Hope this helps.

    Pete
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    03-01-2019
    Location
    Lisbon
    MS-Off Ver
    2016
    Posts
    20

    Re: Excel calendar

    everything looks amazing!!!

    Can you tell me how I can add items under columm S ?

  15. #15
    Registered User
    Join Date
    03-01-2019
    Location
    Lisbon
    MS-Off Ver
    2016
    Posts
    20

    Re: Excel calendar

    and if I may ask...

    can I have the Leave_code populate automatically if I fill in the Leave_type?

  16. #16
    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: Excel calendar

    Both those questions relate to the same thing. I arranged it so that you would only have to enter a single letter (and from a drop-down), to reduce typing and to ensure accuracy. If you want to do it the other way round, then you can have a drop-down in the cells in column P and have the codes in column O filled automatically (although it might be more logical to have Leave_type in column O, Status in column P and then the Leave_code in column Q, so that's how I'll set it up for you.

    The Leave_Code can be derived by this formula in Q3:

    =IF(O3="","",VLOOKUP(O3,$S$4:$T$36,2,0))

    where I have set up column O so you can choose the Leave_type from a drop-down. The drop-down allows you to choose from a named range Leave_types, which I have defined as covering S4:S34 in the bookings sheet - you can use Name Manager on the Formulas tab if you need to change this. Just add extra types and codes to the bottom of those there already (which you can also change to suit your requirements).

    Hope this helps.

    Pete
    Attached Files Attached Files

  17. #17
    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: Excel calendar

    By the way, if that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of any post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  18. #18
    Registered User
    Join Date
    03-01-2019
    Location
    Lisbon
    MS-Off Ver
    2016
    Posts
    20

    Re: Excel calendar

    Exactly what I wanted. Thank you Pete_UK

+ 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. Replies: 0
    Last Post: 08-27-2015, 11:37 PM
  2. Calendar functions - linking work activities with formatted dates to calendar
    By SKSS in forum Access Programming / VBA / Macros
    Replies: 1
    Last Post: 05-14-2012, 06:38 PM
  3. Calendar VBA auto filling week and month based on calendar entry.
    By perrymagic in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-18-2011, 02:00 PM
  4. Using a userform Calendar to fill in an excel calendar, and also a log
    By 00Able in forum Excel Programming / VBA / Macros
    Replies: 64
    Last Post: 09-08-2011, 08:50 PM
  5. Replies: 0
    Last Post: 03-27-2008, 04:36 PM
  6. Modify Yearly Calendar to Monthly Calendar Excel 2000?
    By James Cooper in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-13-2006, 06:50 PM
  7. [SOLVED] import calendar items from excel into outlook calendar
    By jsewaiseh in forum Excel General
    Replies: 0
    Last Post: 09-02-2005, 11:05 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