Page 1 of 1

[Solved] Need formula to enter data every 28 days

PostPosted: Tue Feb 25, 2020 2:50 pm
by Clarky64
Need formula to enter data every 28 days
Sounds simple but I cannot get it to work. Sheet layout is as follows

Column A date
Column B Rent
Column C Rent Benefit
Column D My Payment

Column B formula =IF(AND(WEEKDAY($A7,2)=1,A7<=$A$5),$B$5,0) enters amount every Monday

Column C formula =IF($A6=43839,$C$5,0) Formula for the first cell only
Column C formula =IF(AND(A7<=$A$5,A7=A$6+28),$C$5,0) enters amount in cell A6 then 28 days after
I cannot drag the bottom right hand corner of the cell and get the formula correct. So it should be A6+56 then A6+84 and so on.

Although I can manually do it I would like to know how to get the formula to work.

Easier if you look at the sheet

Re: Need formula to enter data every 28 days

PostPosted: Tue Feb 25, 2020 4:43 pm
by FJCC
I think you want this formula in C6 and then you can drag it down.
Code: Select all   Expand viewCollapse view
=IF(MOD(A6 - A$6;28) = 0;$C$5;0)

Re: Need formula to enter data every 28 days

PostPosted: Tue Feb 25, 2020 8:14 pm
by Clarky64
Thank you for the quick reply.

It works apart from one thing.

Can you adjust it so it doesn't enter future cells.

I guess an AND condition would work, is that right?

Re: Need formula to enter data every 28 days

PostPosted: Tue Feb 25, 2020 8:30 pm
by FJCC
Like this?
Code: Select all   Expand viewCollapse view
=IF(AND(A6<=$A$5;MOD(A6 - A$6;28) = 0);$C$5;0)

Re: Need formula to enter data every 28 days

PostPosted: Tue Feb 25, 2020 9:53 pm
by Clarky64
Perfect Thank you.

Re: [Solved] Need formula to enter data every 28 days

PostPosted: Wed Feb 26, 2020 3:37 am
by RusselB
I know this has been answered, but wondered about simply using
Code: Select all   Expand viewCollapse view
=if(A1+28=today();A1+28;0)
where A1 is the first cell with a date in it.