# Scheduling any year calendar

1. ## 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

2. ## Re: Scheduling any year calendar

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

Pete

3. ## 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. ## 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.

5. ## 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. ## 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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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