Identifying date intervals depending on a variable

Whiox

New Member
Joined
Feb 19, 2019
Messages
33
Dear Excel community

To start with, I am an Excel novice - please forgive me any naive questions.

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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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?
 
Upvote 0
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
 
Upvote 0
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","-")
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

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