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

1. ## 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

2. ## Re: Determine if date is within range

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.

3. ## Re: Determine if date is within range

Maybe

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

4. ## 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. ## 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. ## 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. ## 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.