+ Reply to Thread
Results 1 to 13 of 13

MOD function incorrect outcome

  1. #1
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    MOD function incorrect outcome

    Hello!

    I can't wrap my head around the issue I face: I am trying to compute the correct end date based on the formula

    =IF(OR(MOD(WEEKDAY(WORKDAY.INTL($C$2,$D$2,1,$B$2:$B$32)-1),7)=1,MOD(WEEKDAY(WORKDAY.INTL($C$2,$D$2,1,$B$2:$B$32)+1),7)<>0),WORKDAY.INTL($C$2,$D$2,1,$B$2:$B$32)+1,WORKDAY.INTL($C$2,$D$2,1,$B$2:$B$32)-1)

    My start date is Wednesday July 26, 2017 , which is 42 942.

    There are 7 working days, which means I have to add 9 (including two weekends) to 42 942, hence get 42 952.

    I use the mod function to check if + 1 day gives Saturday, and as I show in the file, MOD(7,7)<>0 returns FALSE when the date + 1 is actually a Saturday.
    How come? MOD(7,7) does equal 0; there is no remainder when we divide 7 by 7.

    Please, help me to resolve this conundrum.

    Thank you!
    Attached Files Attached Files
    Last edited by Vitalite; 07-30-2017 at 01:28 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,343

    Re: MOD function incorrect outcome

    This:

    MOD(7,7)<>0

    means MOD(7,7) does NOT equal 0.

    This:

    MOD(7,7)=0

    means MOD(7,7) DOES equal 0.
    Last edited by AliGW; 07-30-2017 at 05:36 PM. Reason: Corrected typo . To ,
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: MOD function incorrect outcome

    Hi Vitalite,

    In newer versions of Excel they have created a newer function of WeekDay that includes the optional "Return Type" that might be exactly what you are trying to reproduce with your Mod function. Read about it at:
    https://support.office.com/en-us/art...rs=en-US&ad=US
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,343

    Re: MOD function incorrect outcome

    In other words, you need this, I believe:

    =IF(OR(MOD(WEEKDAY(WORKDAY.INTL($C$2,$D$2,1,$B$2:$B$32)-1),7)=1,MOD(WEEKDAY(WORKDAY.INTL($C$2,$D$2,1,$B$2:$B$32)+1),7)=0),WORKDAY.INTL($C$2,$D$2,1,$B$2:$B$32)+1,WORKDAY.INTL($C$2,$D$2,1,$B$2:$B$32)-1)

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,343

    Re: MOD function incorrect outcome

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  6. #6
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: MOD function incorrect outcome

    [QUOTE=AliGW;4710208]This:

    MOD(7,7)<>0

    means MOD(7,7) does NOT equal 0.[\QUOTE]

    Yes, I know exactly what it means, and used this sign specifically as it answers my purpose.

  7. #7
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: MOD function incorrect outcome

    Quote Originally Posted by AliGW View Post
    In other words, you need this, I believe:

    =IF(OR(MOD(WEEKDAY(WORKDAY.INTL($C$2,$D$2,1,$B$2:$B$32)-1),7)=1,MOD(WEEKDAY(WORKDAY.INTL($C$2,$D$2,1,$B$2:$B$32)+1),7)=0),WORKDAY.INTL($C$2,$D$2,1,$B$2:$B$32)+1,WORKDAY.INTL($C$2,$D$2,1,$B$2:$B$32)-1)
    Thank you, but no, this is not what I need. My question was related to the MOD(7,7). The overall formula is written the way I need it to be for my purpose.

  8. #8
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: MOD function incorrect outcome

    Quote Originally Posted by MarvinP View Post
    Hi Vitalite,

    In newer versions of Excel they have created a newer function of WeekDay that includes the optional "Return Type" that might be exactly what you are trying to reproduce with your Mod function. Read about it at:
    https://support.office.com/en-us/art...rs=en-US&ad=US
    Thank you. I use this function in my big formula. But to check if days are right I still need MOD, if I am not mistaken.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: MOD function incorrect outcome

    Am I missing something here ..?????

    as MOD(7,7) is always zero, the test for MOD(7,7)<>0 is pointless

    MOD(7,7)<> 0 correctly returns FALSE.

  10. #10
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: MOD function incorrect outcome

    Quote Originally Posted by JohnTopley View Post
    Am I missing something here ..?????

    as MOD(7,7) is always zero, the test for MOD(7,7)<>0 is pointless

    MOD(7,7)<> 0 correctly returns FALSE.
    Looks like I have not stated my question correctly, and myself missed something. The only thing for sure is that MOD() <> 0 is not pointless, because it is not always MOD(7,7).

    But, please, if you have a minute (it seems I am having a "writer's block" today; not my best day), could you explain why if MOD(7,7) <> 0, which means 7 / 7 is not equal 0, returns FALSE and not TRUE - it is correct that MOD(7,7) <> 0, i.e. is not equal to 0?

  11. #11
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: MOD function incorrect outcome

    Hi Vitalite,

    MOD(a,b) means the remainder (normally an integer) after you divide a by b. Example: MOD(20,2) = 0 , MOD(20,3) = 2 , MOD(20,7) = 6 . You divide the first number by the second and see how many are left over, or the remainder. So John is correct that MOD(7,7) = 0 is true.

    I had a college class where we did remainders and found there is a "theorem" for determining how to graph a polynomial. Remainder theory also comes into play with Fibonacci numbers, creating pseudo random number generators.

    Read more at:
    http://www.purplemath.com/modules/remaindr.htm
    Last edited by MarvinP; 07-30-2017 at 02:35 PM.

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: MOD function incorrect outcome

    MOD(7,7)=0 (Fact)

    so MOD(7,7)=0 is TRUE

    therefore MOD(7,7)<>0 is FALSE (as it does equal 0)

    <> means NOT EQUAL so MOD(7,7) <> 0 is stating that (IF) MOD(7,7) does NOT EQUAL 0 which is FALSE as it DOES equal 0.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,343

    Re: MOD function incorrect outcome

    Quote Originally Posted by Vitalite View Post
    Looks like I have not stated my question correctly, and myself missed something. The only thing for sure is that MOD() <> 0 is not pointless, because it is not always MOD(7,7).

    But, please, if you have a minute (it seems I am having a "writer's block" today; not my best day), could you explain why if MOD(7,7) <> 0, which means 7 / 7 is not equal 0, returns FALSE and not TRUE - it is correct that MOD(7,7) <> 0, i.e. is not equal to 0?
    I explained this in post #2 and you summarily dismissed what I told you. Others have tried, too.

    You need to start actually 'listening' to what people are trying to teach you. None of it has anything to do with 'writer's block' or having a bad day. The formula I suggested, which you also dismissed, gives the answer you are looking for, I believe.
    Attached Files Attached Files
    Last edited by AliGW; 07-31-2017 at 02:39 AM.

+ 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. incorrect outcome
    By Kerkoerle in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-30-2016, 06:38 AM
  2. How do I get a specific outcome from a function?
    By Platerising in forum Excel General
    Replies: 7
    Last Post: 01-12-2016, 05:29 PM
  3. [SOLVED] If function, many ifs but one or two outcome?
    By axangec in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-11-2016, 06:14 AM
  4. [SOLVED] outcome of CONCATENATE function
    By clareleeder in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-20-2013, 01:17 PM
  5. Macro runs Text to Columns with incorrect outcome.
    By JonnieB in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-08-2013, 11:15 AM
  6. Incorrect Outcome with Dates and >= statement
    By stuart010 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-29-2011, 12:55 PM
  7. Incorrect formula outcome
    By leonidas in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-27-2006, 09:25 AM

Tags for this Thread

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