# Formula for Shift Roster being thrown off by leap years?

1. ## Formula for Shift Roster being thrown off by leap years?

I've been trying to create a shift leave roster for work, which works around a 4 year revolving roster. I want to be able to input a year at the top and have it adjust accordingly for all future years but every time i put in a leap year it throws the answer off by 1.

This is what I currently have in C14:

``Please Login or Register  to view this content.``
Capture.PNG

2. ## Re: Formula for Shift Roster being thrown off by leap years?

There are instructions at the top of the page explaining how to attach your sample workbook.

3. ## Re: Formula for Shift Roster being thrown off by leap years?

Here is the Spreadsheet

4. ## Re: Formula for Shift Roster being thrown off by leap years?

Explain the date in B13 - why 2010?

What result are you expecting and why?

Explain in WORDS what you ware expecting your formula to do.

5. ## Re: Formula for Shift Roster being thrown off by leap years?

Give this a try:

=MID(\$B14,MOD(DATEDIF(\$B\$13,C\$13,"m")/12,4)+1,1)

6. ## Re: Formula for Shift Roster being thrown off by leap years?

Administrative Note:

Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

Please see Forum Rule #5 about thread duplication.

I am closing this thread, but you may continue here in the original thread: https://www.excelforum.com/excel-for...-function.html

#### Thread Information

##### Users Browsing this Thread

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