Use Formula to Insert Fortnightly Payments in Table

Joined
Dec 5, 2019
Messages
1
Office Version
  1. 2011
Platform
  1. 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

  • Screenshot A.png
    Screenshot A.png
    102.8 KB · Views: 17
  • Screenshot B.png
    Screenshot B.png
    125.6 KB · Views: 18

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

hernantorres23

Active Member
Joined
Nov 21, 2019
Messages
269
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. 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,122,939
Messages
5,598,967
Members
414,269
Latest member
FJXMTT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top