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
Bookmarks