Determine if date is within range
Results 1 to 7 of 7

Thread: Determine if date is within range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2018
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Determine if date is within range

    I have beat my head against the wall with this one. I really hope someone can help here. To Completely summarize what I am trying to do....

    {
    If it is Peter's pay-week, N4 equals Y1
    If it is Casey's pay-week, N4 equals Y1 + Y2
    }
    -----------------------------------------------------------------
    Peter is paid weekly, and Casey is paid Bi-Weekly.
    Peter and Casey are both paid on Thursdays.

    I know how to make it work THIS WEEK with the following (which would need to be edited to apply here; I copy/pasted from the forum I found it on):
    =IF(ABS(TODAY()-WEEKDAY(TODAY()-1)+3-D2)<=3,G2,150)
    How do I make the formula referenced above work also for future date ranges rather than just THIS WEEK? I have the ranges I am interested in considering in column J
    Last edited by Peteor; Jun 19th, 2019 at 07:11 PM.

  2. #2
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,127
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Determine if date is within range

    I think more information is needed.

    Can you show how the worksheet is set up?

    And what defines which week Casey is paid. I'm thinking there needs to be a date of one such payment defined - then the formula can use that.
    If you've posted a clearly explained question & sample data - both input & corresponding output - that can be copied to Excel, THANK YOU.
    Google can find answers to nearly every question.


  3. #3
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,355
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Determine if date is within range

    Maybe

    =IF(ISODD(INT((D2-1)/7)),G2,150)

  4. #4
    Board Regular
    Join Date
    Mar 2018
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Determine if date is within range

    Thank you both for responding.

    Tetra201 - I will test, and reply when I get a chance. Thank you!

    Fazza - I would link the sheet, but honestly am unsure how. I have both Peter and Casey's Paydays listed by date going out 6 months. For this example, Assume Casey's next payday is shown in J1 = 6/20/2019, J2 = 7/4/2019, ... going out 6 months.
    A simple solution would be IF(Date falls within range, N4 = Y1 + Y2, Y1)
    For the life of me though, I cannot figure out the "Date falls within range" part of the example. where "range" would be cell references, rather than being hard coded.

  5. #5
    Board Regular
    Join Date
    Mar 2018
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Determine if date is within range

    I GOT IT!!! Tetra201.... Your response got me thinking. Peter is paid every week, and Casey is paid every other week. It also works out this means Casey is paid on even weeks. because of all this, the following worked:

    =IF(ISEVEN(WEEKNUM(J2))=TRUE,$Y$2+$Y$4,$Y$2)

    J2-J26 are all Peter's paydays. This is a very counter-intuitive example, but thank you both!!!

  6. #6
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,355
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Determine if date is within range

    You are welcome.

    A word of caution about WEEKNUM: when the year changes, a full odd-numbered week (53) may be followed by another full odd-numbered week (1), for example 2016-->2017.

  7. #7
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,127
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Determine if date is within range

    Tetra's comments - essentially WEEKNUM as proposed is not good - support the idea that one pay day for Casey should be given.
    If you've posted a clearly explained question & sample data - both input & corresponding output - that can be copied to Excel, THANK YOU.
    Google can find answers to nearly every question.


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •