Use Formula to Insert Fortnightly Payments in Table

Joined
Dec 5, 2019
Messages
1
Office Version
2011
Platform
MacOS
Hello,

This is my first post and I'm hoping to get a bit of help with creating a formula that I can use on a finance table that I've created.

I am attempting to forecast my personal finances and want to insert fortnightly payments into a column automatically.

I've managed to do this for my monthly payments through using the following formula:=IF($J21=BJ$14,BJ$17,""). For context, $J21 is the first cell of the column that captures the day value of the date which is in $I21, BJ$14 is the day of the month that the particular payment comes out (in this case the 4th) and BJ$17 is the value of the payment ($20). After entering this formula and dragging it down the entire column, $20 appears whenever the date is the 4th of the month.

Does anyone know of a way that I could obtain the same result with fortnightly payments? Also, if you're feeling especially generous feel free to come up with a formula for quarterly payments too.

I've attached some screenshots, Screenshot A shows the columns that I want to insert the fortnightly payments into. Screenshot B shows what I have successfully done with the monthly payments.

My apologies if this is a silly question, I learn Excel by reverse engineering a lot of formulas that I come across in random spreadsheets (hence my display name). My knowledge definitely isn't up there at the moment and I might be missing something obvious.

Thanks in advance,

Mr XL. R Engineering
 

Attachments

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

hernantorres23

Active Member
Joined
Nov 21, 2019
Messages
256
Office Version
365, 2016
Platform
Windows, Web
Hi, If you can share this workbook, without data, I'm sure I can help you, or put every sheet using add-in specifically for this and it can be found here XL2BB
 

Watch MrExcel Video

Forum statistics

Threads
1,095,232
Messages
5,443,263
Members
405,221
Latest member
danthesuperman

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top