Automatic Withdrawls: Calculate future dates, convert to workday, which pay period

I found it hard to adequately convey my question in the title - hopefully there's enough meat to attract some attention.

What I'm doing: trying to regain control of my finances. I have so many automatic withdrawals - life insurance, house insurance, car insurance, mortgage, taxes and so on, that I find it difficult to predict when the payments are coming out, relative to my bi-weekly pay day. I have created a sheet that can determine the frequency (monthly or bi-weekly), calculate the next withdrawal date, convert that date to a work day if required, determine whether that date falls within the current pay period and sum up the payments.

What I'd like to do, is extend the look into the future for several pay periods (let's say 4 to avoid ambiguity). I can do it by tweaking the formulas but they either a) become so large within a single cell that deciphering them is nigh impossible or; b) require three columns for each date. I'm looking for a simpler / cleaner method to do this.

Fair warning - I had to google to find the solutions I do have and I know they're not elegant.

I made some minor modifications to the sheet - moved Total to one of first rows - may seem crazy but gives easiness at the stage of preparying.
once the sheet is almost ready - you can move it below data (as most people got used to.)
All formulas below refer to this new layout.

First, to have universal formula, I'd get rid of TODAY in calculation of next payment for specific period. Instead I would use the most recent payday.
Then use WORKDAY(day_before,1) which will return first working day at or after the day instead of calculations of day of week etc.
WORKDAY allows also to include bank holidays in calculations - optional third argument.

so now in E4 we have:

Please Login or Register  to view this content.

copied down and right
(note that I changed Campus payment mode to quarterly to better show how it works)


Next: everything for one payday can (and shall - to keep things neat looking) be done in single column.

Instead of columns returning true/false for date conditions and then SUMIF I used SUMIFS (You do not disclosed excel version in your profile, but the attachment is in xlsx format introduced together with SUMIFS function in excel 2007)
in E2:

Please Login or Register  to view this content.

and copied right
This works perfect for future periods, but wor this bi-week, as you were payed on 11 and mortgage already has been payed you are more interested in E3:

Please Login or Register  to view this content.

(this one not copied - only here)


I also used two simple conditional formatting rules based on AND formulas

And that's it.
3 formulas for whole sheet.
(plus your paydays calculations - 1 and a half ;-) formula
Why half? Because =F1+14 i'd count as no more than 0.25 of "whole formula" :-P

Elegant - isn't it? :-D

Enjoy!

Great you noticed it.

Have you checked it in original version?
I can see it there too.
So it is an inherited bug :-D

I think that to avoid problems in monthly/n-monthly withdrawal happening on payday
DATEDIF($A4,E$1-1,"m")
shall be used instead of
DATEDIF($A4,E$1,"m")
so try:
=WORKDAY(IF($B4="Bi-Weekly",MOD($A4-E$1,14)+E$1,EDATE($A4,CEILING(DATEDIF($A4,E$1-1,"m")+1,VLOOKUP($B4,{"Monthly",1;"quarterly",3;"semi-annual",6;"annual",12},2,0))))-1,1)

copy and paste special as formulas only to keep conditional formatting unchanged.
Then go for further tests.

Next test for you:

I assume that bank is doing withdrawal in the morning "interbank exchange session" so you have to had big enough balance in the morning.
(This is how it work in my country. You, as for profile data live "nowhere" so I can't even check with Google how it is organised there.)
Even if your employer also pays morning on the payday - the monies are visible on your account after the first (in our case sometimes after the second) interbank exchange session.
So usually (depending on bank rules and allowed debt), bank will block the withrawal, because balance is too small.
(Of course only if you are really going near the edge - if you keep "buffor amount" on account - no problem)

If bank tries to make next approach to do withdrawal later on that day - also no problem - your wages are already there.
But in our case bank makes second attempt only next day (and it means you were past due).

So - please comment on the above.

So my honest advice is to treat for these calulations that your payday is a day later than an actual one.