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
[Solved] Need formula to enter data every 28 days
[Solved] Need formula to enter data every 28 days
- Attachments
-
- Rent Account for upload.ods
- (34.38 KiB) Downloaded 133 times
Last edited by Clarky64 on Tue Feb 25, 2020 9:54 pm, edited 1 time in total.
Libreoffice 6.3, Manjaro Linux
Re: Need formula to enter data every 28 days
I think you want this formula in C6 and then you can drag it down.
Code: Select all
=IF(MOD(A6 - A$6;28) = 0;$C$5;0)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Need formula to enter data every 28 days
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?
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?
Libreoffice 6.3, Manjaro Linux
Re: Need formula to enter data every 28 days
Like this?
Code: Select all
=IF(AND(A6<=$A$5;MOD(A6 - A$6;28) = 0);$C$5;0)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: [Solved] Need formula to enter data every 28 days
I know this has been answered, but wondered about simply using where A1 is the first cell with a date in it.
Code: Select all
=if(A1+28=today();A1+28;0)
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.