Constructing a cel calculation from a string

CONTINUED:

PROCEDURES FOR SETTING UP A NEW PAY CYCLE:

Notes:

I am the only user of this sheet, and the steps could be improved for understanding, if others were to use them.

I've included some pseudo variable names that maybe I can use with LET.

--

In columnA (paydate), enter "paydate" for the PayDateNext (generally the 2nd Wednesday or the 25th of the month). (This is so you know how many rows the new pay cycle you are creating should have.)

Select (click-drag) the celColumnERow1stPayPeriodCurrent (budgetUpdatedNotTodaySpending) through celColumnSRowLastPayPeriodCurrent (i.e., a range) and copy (cmd-c).

Select ColumnERow1stPayPeriodNext, then paste.

• IF you need to fill out more rows in the new pay period, select the rowLastColumnE with data, and shift-click to the rowLastColumnM that you need to fill out, then do: Edit > Fill > Down cmd-shift-d). Even if new cels don't look right, continue.

• IF you need to remove one or more rows of data (that have been pasted into the next pay period), select from column E, row corresponding to PayDateNext, through the last row of data, to column M, then: delete (to remove data, but not formatting); AND select leftover formatted cels in columns L & M, and: Edit > Clear All.

Select (click-drag) from columnP, 1st row row of paydate, to columnS, last row of data in columnS, and: Edit > Clear All [right click also brings up that choice].

Update (if necessary) your current budgetBiMonthly budget (typically this is the sum of a bimonthly recurring checking spending transaction, and available cash).

Update (if necessary) your checking and cash spending remaining amounts (the total needs to equal the current budgetBiMonthly, since it is the 1st day of the cycle and no expenditures have yet been made (if you've keep those numbers current, they should (already) be good.)

In column L, row first: Double click the formula and manually enter [or select, then choose preserve row] the rowLast (after $B) TO (i.e., INCLUDING) the payDateNext (column B, e.g.: [ =DATEDIF(B77,$B$77,"d")] to [ =DATEDIF(B77,$B$92,"d") ] . The cell1st (e.g., B77) should already be accurate already, after the copy and paste (due to relativity).

Select from columnL, paydate1st cel through the last date of the payPeriodCurrent.

command-shift-D to Table > Autofill cells > Autofill down.

UNLESS already set correctly, row1stColumnO (budgetCalculatedPerDay which is the row1stCurrentPayPeriod), change the celReference2nd in the parenthesis TO (INCLUDING) the PayDateNext. Example: budgetBimonthly 'Wednesday 9/11/24'÷DATEDIF(A2059,A2073,"D")

In row1stColumnIPaydateCurrent (budgetDaily), delete the formula and select day1stColumnOPayPeriodCurrent, (budgetCalculatedPerDay), preserve row [We could manually change the date (and use the existing preserve row), but it requires changing both the day and date.] xxxx the row number (following $) to xxxx; output example: $budgetCalculatedPerDay $'Wednesday 11/13/24' . You must use: Daily  budget must equal budgetCalculatedPerDay.

Shift-click from columnIRow1st, to the rowLastCurrentPayPeriod.

command-shift-d to Table > Autofill cells > Autofill down.

Change columnJRow2ndPayPeriodCurrent, last row number, to the rowNumberColumnO1stPayPeriodCurrent (columnO) , e.g.: [ =J77-$O$61 to =J77-$O$77 ] . Need to preserve row, e.g.: [budgetPlannedCountdown 'Wednesday 8/14/24'−$budgetCalculatedPerDay $'Wednesday 8/14/24']  TO [budgetPlannedCountdown 'Wednesday 8/28/24'−$budgetCalculatedPerDay $'Wednesday 8/28/24']   ==> I can select the cel and click the new one.

Shift-click from columnJ2ndRowPayPeriodCurrent cel to the last date of the payPeriodCurrent.

command-shift-d to Table > Autofill cells > Autofill down. columnJRowLastPayPeriodCurrent must equal row1 Column budgetCalculatedPerDay.

In columnKRow2ndPayPeriodCurrent change the row2ndPayPerodCurrent, the last row number, to the row1stColumnOPayPeriodCurrent, e.g: [ =K77+$O$61 to =K77+$O$77 ] . Required: preserve row.

Select from columnKRow2ndPayPeriodCurrent cel through the last date of the payPeriodCurrent.

command-shift-d to Table > Autofill cells > Autofill down. rowLastCurrentPayPeriodColumn spendingTotalPlanned must equal row1stCurrentPayPeriod column budgetBimonthly.

If necessary, Update cellRightOf "above - amount remaining this period (last day of cycle) _set!". to the rowLastPayPeriodCurrent. You must use: preserve row.

With formulas converted to text:

Thank you all for help!

After reading your reply and looking my sheet, it became clear I need to spend quite a bit more time reviewing the formulas that I have been hopeful I optimize, as follows.

I just modified what I was going to post, since two replies came in with suggestions for using LET for variable use, and INDIRECT to create a reference from a string. I am going to review. Those l look extremely promising. I will try them out, and report back anything useful.

Being able to use variables like paydateCurrent and payDateNext. 

This is not a perfect example (but shows how I could use a 'variable' name (using LET).

(On a separate note, I might also be able to change "Monday 5/26/2025" into cycleDay1, and then be able to increment (pseudo code) cycleDay1 + 1, to get the next day, etc.)

Change from:

budgetBimonthly 'Monday 5/26/25'÷DATEDIF($A2316,$A2332,"D")

To:

budgetBimonthly 'Monday 5/26/25'÷DATEDIF($paydateCurrent,$payDateNext,"D")

Looking into LET and INDIRECT will be my next two steps. At this point, I think more details about my sheet and how I am currently updating it, would be difficult/time-consuming to pull apart, and may bring up even more questions, but in case useful, here it is:

(I've duplicated the spreadsheet and put in some placeholder numbers.)

The main purpose of the sheet is to track spending against a bimonthly (the exact dates vary, but for me they are approximately the 10th and 25th of each month) budget, where the running budget total is decrementing by daily spending. "paydate" simply means a source of income; it could be a pay check, business owner draw, and/or a pension / nest egg draw / Social Security benefit. There a fields for remaining checking and cash amounts. (As I spend from either, I decrement the amount to match the actual figures, which are Quicken balances. I don't currently have an automated way to have the amounts in Numbers be automatically updated to those amounts.)

This main sheet (actually, the only sheet with data in it so far, but I may later use a separate sheet for 'constants', if that ends up being a worthy plan) has two header rows (shown with grey cel backgrounds). The first row is simply extra information to supplement the second header row, which contain the text used for header names. The reason I made the first row as a header column was simply so I could freeze it, and be able to always see it.

CEL FILL AND TEXT FORMATTING (these of course have no effect on formulas, etc.): Green and white background denotes manually-entered numbers; orange background is the main block of numbers (and one cell to the right). The first (header) column, and the first two (header) rows have a grey background.

The header column "date" has a date format.

"paydate" column is where I enter: "paydate" in the first row of pay cycles. It is simply a visual reference for me when I create a new pay cycle; the "date" column is the one used for cel references. The rest of the cells in columns and standalone have text describing their purpose.

CONTINUED IN NEXT REPLY

A day later, I noticed my last two replies ended up in the reverse order they should have been.

My last reply (ending with: "CONTINUED IN NEXT REPLY") should have been before my previous-to-that reply beginning with: "CONTINUED:"

(It's now too late to change things, so just FYI for anyone reading this thread.)

Thanks!