Any Excel Formula jockeys?

[HeroHog]

Anyone else do such craziness in your spreadsheets?

Used to populate budget calendar date values based on 2 week pay periods and to account for the 2 and possible 3 pay periods/month. There is also a payday on the 3rd of every month so if the secondary payday is close enough to the 3rd, it is included there and not shown in the next date column and the pay period AFTER that goes there instead.

Code: [Select]

=IF(INDIRECT("'"&TEXT($AE$1-1,"##")&"-17"&"'!"&"L1")="",
  IF(DAY(INDIRECT("'"&TEXT($AE$1-1,"##")&"-17"&"'!"&"G1")+14)<=4,
    INDIRECT("'"&TEXT($AE$1-1,"##")&"-17"&"'!"&"G1")+28,
    INDIRECT("'"&TEXT($AE$1-1,"##")&"-17"&"'!"&"G1")+14),
  IF(DAY(INDIRECT("'"&TEXT($AE$1-1,"##")&"-17"&"'!"&"L1")+14)<=4,
    INDIRECT("'"&TEXT($AE$1-1,"##")&"-17"&"'!"&"L1")+28,
    INDIRECT("'"&TEXT($AE$1-1,"##")&"-17"&"'!"&"L1")+14))
Budget available based on pay value for 1st column. Adds two checks from the 3rd of the month with income from bi-weekly pay if bi-weekly pay falls within the 1st and 5th.

Code: [Select]

=Q37+Q38+
  IF(INDIRECT("'"&TEXT($AE$1-1,"##")&"-17"&"'!"&"L1")<>"",
    IF(DAY(INDIRECT("'"&TEXT($AE$1-1,"##")&"-17"&"'!"&"L1")+14)<=4,
      $Q$39*2/4.3333,
      0),
    IF(DAY(INDIRECT("'"&TEXT($AE$1-1,"##")&"-17"&"'!"&"G1")+14)<=4,
      $Q$39*2/4.3333,
      0))

[HeroHog]

Indicating that certain cells are marked as "paid."

Code: [Select]

=IF(AND(ISBLANK(D27),
  ISBLANK(I27),
  ISBLANK(N27)),
    "",
    "pd")&","&
  IF(AND(ISBLANK(D26),
    ISBLANK(I26),
    ISBLANK(N26)),
      "",
      "pd")&","&
  IF(AND(ISBLANK(D24),
    ISBLANK(I24),
    ISBLANK(N24)),
      "",
      "pd")&","&
  IF(AND(ISBLANK(D25),
    ISBLANK(I25),
    ISBLANK(N25)),
      "",
      "pd")

[Hawkmoon]

Some people have too much time on their hands ...

Is that a formula, or a macro?

[HeroHog]

Formula

I have a macro that makes whatever I enter in some cells into all caps on the sheet though.

 

[Hawkmoon]

Formula

I'm impressed. It's way beyond my rudimentary capabilities.

[HeroHog]

Google and programming background are my friends. It takes a hell of a lot longer to suss out these days what with my head the way it is but it gives me something to work the rusty gears a little these days.

[K Frame]

I do my budget with Excel, but it's nothing anywhere near that complicated. Just a few simple formulas.

[HeroHog]

I'm getting old and my brain is damaged from age and meds I guess so I forget stuff. Doing it this way it makes it more likely that I will get everything paid and on time and it makes planning much easier as I can see it all at a glance.

It tracks:
ALL our bills and debt,
min payment needed,
recommended min payment (min x1.5),
balance remaining/account,
total revolving debt (running balance & paid monthly),
total other debt & per/month
and if each bill due is paid.

There is a summary page that shows the monthly averages and yearly totals of:
Insurance Premiums
Medical
Other
Utilities, Cable, Phones
Credit Cards
Food
and Net yearly savings deposited (usually 0   )

There are a few other tricks made to make yearly changes easier and to deal with copying/replacing values as the months progress without having to do anything more than type in a new value if it has changed. That goes for pay changes, etc.. Dates are also auto calculated/propagated. You only need enter the initial value in the 1st sheet and the rest pick it up and use it wherever needed from there or calculate what they need based off the day/month/year in the sheet 1 field.