+ Reply to Thread
Results 1 to 3 of 3

Help with dates and MOD

  1. #1
    Registered User
    Join Date
    08-05-2012
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    8

    Help with dates and MOD

    Hi, I've been scratching my brain and google trying to find a solution to what I want to do, but have not had any luck yet.

    I have made a calendar, with a bunch of conditional formatting to highlight certain days based off user selections. One of them being Pay Dates. Now, the user will enter in their first pay date in another cell, and then enter the pay period. In this case I am using biweekly, every 14 days. What I want to do is highlight all dates in the calendar that the pay date is on. I have made one way to do it, checking if a date is within a range I made, the range containing 6 pay dates before and after the current pay period.

    The problem with that is it is limited on the calendar to the those 6 dates, so if pay period is every 2 weeks, that would be 3 months of the calendar would highlight, after the user changes the month past those 3 months, the dates will no longer highlight.

    So, I am trying to find a formula that will check if a date is divisible evenly by a number, whether it is 7 or 14, based off the first pay date.

    If anybody can help, that would be great. Thanks

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Help with dates and MOD

    Conditional format, formula based:

    mod(abs(pay date start-reference),14)=0

    The difference between dates is the count of days. Negative time creates an error, so we use the absolute value of their difference, so that it functions both forwards and backwards.

    If the remainder of the difference is 0, it is evently divisible by 14.
    Last edited by daffodil11; 09-26-2013 at 12:05 PM.

  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Help with dates and MOD

    And here's an example, in case you get stuck.

    mod - abs.xlsx

+ 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. Check dates in range either same dates or different dates by formula
    By breadwinner in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-12-2013, 07:42 AM
  2. Replies: 6
    Last Post: 03-11-2013, 06:11 PM
  3. [SOLVED] Array of dates when tested shows no dates ......but there are dates
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-29-2012, 12:45 PM
  4. [SOLVED] Highlight Group of Dates if Dates Match List of other Dates
    By martinpgibson in forum Excel General
    Replies: 5
    Last Post: 10-24-2012, 08:14 PM
  5. [SOLVED] Auto filling dates based on previously entered dates and averaging numbers if dates equal
    By grambograham in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2012, 03:21 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