[Solved] Need formula to enter data every 28 days

Discuss the spreadsheet application

[Solved] Need formula to enter data every 28 days

Postby Clarky64 » Tue Feb 25, 2020 2:50 pm

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 19 times
Last edited by Clarky64 on Tue Feb 25, 2020 9:54 pm, edited 1 time in total.
Libreoffice 6.3, Manjaro Linux
Clarky64
 
Posts: 5
Joined: Tue Feb 25, 2020 12:49 pm

Re: Need formula to enter data every 28 days

Postby FJCC » Tue Feb 25, 2020 4:43 pm

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)
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7744
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Need formula to enter data every 28 days

Postby Clarky64 » Tue Feb 25, 2020 8:14 pm

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

Re: Need formula to enter data every 28 days

Postby FJCC » Tue Feb 25, 2020 8:30 pm

Like this?
Code: Select all   Expand viewCollapse view
=IF(AND(A6<=$A$5;MOD(A6 - A$6;28) = 0);$C$5;0)
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7744
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Need formula to enter data every 28 days

Postby Clarky64 » Tue Feb 25, 2020 9:53 pm

Perfect Thank you.
Libreoffice 6.3, Manjaro Linux
Clarky64
 
Posts: 5
Joined: Tue Feb 25, 2020 12:49 pm

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

Postby RusselB » Wed Feb 26, 2020 3:37 am

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.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 6103
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: No registered users and 5 guests