+ Reply to Thread
Results 1 to 3 of 3

Need help working the day number in shift cycle relative to the shift cycle start date and

  1. #1
    Registered User
    Join Date
    08-24-2017
    Location
    Reading, England
    MS-Off Ver
    2016
    Posts
    2

    Need help working the day number in shift cycle relative to the shift cycle start date and

    Hey guru's

    Scenario:
    Shift cycle length = 21 days rotating so after day 21 it goes back to Day 1. (could be a 7 or 14 day cycle)
    Shift cycle Start Date on = Jan 1 2017 (can be different dates)
    I’m looking for a formulae that can work out what day number in the cycle a future date will map to by using the shift cycle length and a future date.

    So using information above I want to be able to enter any future date e.g. 22/1/2017 and using the cycle value = 21 and Shift cycle Start Date = 1/1/17 it work out the 22/1/17 is day 1 in the Shift Cycle.

    This is drivng me crazy!

  2. #2
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    483

    Re: Need help working the day number in shift cycle relative to the shift cycle start date

    Assuming your Shift Cycle Start Date is in cell A1 and your date of interest is in cell A2, try this:

    =MOD(A2-A1,21)+1

  3. #3
    Registered User
    Join Date
    08-24-2017
    Location
    Reading, England
    MS-Off Ver
    2016
    Posts
    2

    Cool Re: Need help working the day number in shift cycle relative to the shift cycle start date

    This is awesome. Works like a dream

+ 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: 4
    Last Post: 04-27-2021, 11:49 AM
  2. Replies: 2
    Last Post: 05-15-2014, 09:04 AM
  3. Count working shift in start and end date
    By eojchin in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-25-2013, 12:48 AM
  4. Replies: 4
    Last Post: 09-21-2012, 12:58 AM
  5. Replies: 19
    Last Post: 09-16-2009, 07:22 PM
  6. Replies: 15
    Last Post: 09-16-2009, 12:02 PM
  7. Replies: 0
    Last Post: 07-27-2006, 03:35 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