Determine if date is within range

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
152
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:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.
 
Upvote 0
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.
 
Upvote 0
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!!!
 
Upvote 0
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.
 
Upvote 0
Tetra's comments - essentially WEEKNUM as proposed is not good - support the idea that one pay day for Casey should be given.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top