# Thread: Identifying date intervals depending on a variable Thanks: 0 Likes: 0

1. ## Identifying date intervals depending on a variable

Dear Excel community

Context
Column A contains dates in ascending order - each row for a new day.
Cell C1 contains a variable as text string, which can either be "weekly", "fortnightly", or "monthly".

Problem
Depending on the variable chosen in C1, I wish to have a formula in column B that identifies weekly, fortnightly, or monthly intervals from the starting date in column A.
Basically, column A shows a date, and column B indicates next to it whether this date is a "payday" or "--".

Thanks for taking time to help me.

2. ## Re: Identifying date intervals depending on a variable

You forgot some of the rules. What day is a payday on for weekly? Same for fortnightly but you will also need to supply a date that is a payday so the date in column A can be tested if it is the correct week for paypay. What day of the month is payday on monthly pay?

3. ## Re: Identifying date intervals depending on a variable

Hello Steve - thanks for your response.

The payday will change in relation to the starting date. Let's say the starting date is 6/6/19, then I'd like to identify every 7th day, 14th day, or monthly interval from there on. If the starting date changes so will the paydays.

Monthly should take into account the varying number of days for each month. In our example, I'd like to highlight 6/7/19, 6/8/18, 6/9/19 and so on.

Thank you

4. ## Re: Identifying date intervals depending on a variable

Ok this will base all results based on cell A1 that should have your first date:

=IF(OR(AND(\$C\$1="Monthly",DAY(\$A\$1)=DAY(A1)),AND(\$C\$1="Fortnightly",MOD(A1-\$A\$1,14)=0),AND(\$C\$1="Weekly",MOD(A1-\$A\$1,7)=0)),"Payday","-")

5. ## Re: Identifying date intervals depending on a variable

That's fantastic - thank you so much!!