[Solved] Need formula to enter data every 28 days

Discuss the spreadsheet application
Post Reply
Clarky64
Posts: 5
Joined: Tue Feb 25, 2020 12:49 pm

[Solved] Need formula to enter data every 28 days

Post 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
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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Need formula to enter data every 28 days

Post by FJCC »

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.
Clarky64
Posts: 5
Joined: Tue Feb 25, 2020 12:49 pm

Re: Need formula to enter data every 28 days

Post 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?
Libreoffice 6.3, Manjaro Linux
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Need formula to enter data every 28 days

Post by FJCC »

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.
Clarky64
Posts: 5
Joined: Tue Feb 25, 2020 12:49 pm

Re: Need formula to enter data every 28 days

Post by Clarky64 »

Perfect Thank you.
Libreoffice 6.3, Manjaro Linux
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Post by RusselB »

I know this has been answered, but wondered about simply using

Code: Select all

=if(A1+28=today();A1+28;0)
where A1 is the first cell with a date in it.
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.
Post Reply