+ Reply to Thread
Results 1 to 6 of 6

Scheduling any year calendar

  1. #1
    Registered User
    Join Date
    03-08-2015
    Location
    Richmond, Indiana
    MS-Off Ver
    2010
    Posts
    10

    Scheduling any year calendar

    Hi,
    I've been working on a calendar that displays all the shift schedules at a factory. I'm using a calendar that Microsoft created that has a function to update to any year. I created drop down lists for the shift scheduling that displays on the calendar days. The problem I'm having is that the calendar displays the end of the previous month and the beginning of the next month on each sheet. The schedule data overlaps from one month to the next. When I change the year and update the schedule, the schedule overlaps and becomes inaccurate. In other words, on the month of January, the calendar shows the 1st through the 7th of February. When I change the year it indexes to show the 1st through the 6th. The data is repeated at the end of January and the beginning of February. I don't know how to make the data follow the index of the calendar year. I've uploaded the calendar for anyone that may have an idea how to make it work. Thanks in advance for helping.
    JStoffle
    Attached Files Attached Files

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

    Re: Scheduling any year calendar

    What was wrong with the one I did for you a few weeks back?

    Pete

  3. #3
    Registered User
    Join Date
    03-08-2015
    Location
    Richmond, Indiana
    MS-Off Ver
    2010
    Posts
    10

    Re: Scheduling any year calendar

    Hi Pete,
    I know you did a lot of work on the schedule and I do appreciate it. I need to have the shift letter with the shift time. It can't be off to the side. Plus I'm trying to learn Excel functions so I had to try for myself. The problem is, I get so far and I hit a brick wall and I can't seem to find the information I'm looking for on the internet or youtube. Please don't be offended that I can't use your calendar and again, thank you for your help.
    JStoffle

  4. #4
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Scheduling any year calendar

    Copy this formula in E6 in worksheet 1...

    =INDEX(Data!$D$1:$D$20,IF(MOD(C6-41985,20)=0,20,MOD(C6-41985,20)))

    Then copy from that cell to J6, O6, etc.

    The MOD function creates an index number to look up the array D1:D20 in the Data tab.

    I wish the MOD function was cleaner, but for 20, the MOD returns a 0, so I had to add an IF statement to avoid that. (it would be cleaner if someone knows how to fix that).

    The reason I used 41985 in the MOD is so that I can get 16 for 12/28/2014 (your first date).

    Let me know what you think.
    Last edited by djapigo; 04-07-2015 at 07:54 AM.

  5. #5
    Registered User
    Join Date
    03-08-2015
    Location
    Richmond, Indiana
    MS-Off Ver
    2010
    Posts
    10

    Re: Scheduling any year calendar

    Hey that's pretty neat. The formula follows the days of the month. That works perfect. Thanks for your help.
    JStoffle

  6. #6
    Registered User
    Join Date
    09-23-2015
    Location
    Newburg, PA
    MS-Off Ver
    MS 2013
    Posts
    1

    Re: Scheduling any year calendar

    Pete_UK

    I'd be interested to know what kind of calendar you set up. I'm having a real hard time creating a calendar right now for work. I've searched the thread and basically haven't seen anything that closely matches our needs.

    I'm looking to create a monthly calendar (starting on Friday) that also includes shift hours worked with total hours calculated. The calendar would have drop down boxes of locations and then you add the hours of the shift with the total hours noted somewhere. I have the calendar and drop down boxes of locations but can't, for the life of me, figure out how to add the hours and totals without completely destroying it.

    Thanks,

    Andy

+ 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. Scheduling: Calendar style scheduling based on scheduling list
    By denkatprieto in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-19-2012, 11:10 AM
  2. Scheduling Calendar
    By redkilner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-13-2012, 02:51 PM
  3. Custom scheduling calendar
    By silverstreak_z28 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-30-2009, 03:05 AM
  4. HOw to create EMP scheduling calendar
    By LOgle5318 in forum Excel General
    Replies: 4
    Last Post: 07-27-2007, 11:21 AM
  5. [SOLVED] scheduling calendar
    By Disorganized receiving in forum Excel General
    Replies: 1
    Last Post: 05-03-2005, 11:06 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