Projected cashfllow for balance of working days left in month

[pannells]

I have tried with all my knowledge to try and figure this out but with no success.
Scenario.
March has 31 days in the month (this would be entered into the alllocated cell)
We do not work Sundays so less 5 Sundays (this would be entered into the alllocated cell)= 26 Working days.
Last Cash-up complited was on the 20th of March.(this would be entered into the alllocated cell)
Avererage turnover per day is $1350.00 (This amount would be pulled from an allocated cell)
Result should be the following.
31-20(last cash-up day enteted)-2(two non working days left in month)=9
9 x $1350 = $12,150.00.
I have been using =((EOMONTH($D$45,0)-$D$45)-B47)*(1350)
D45 = last cash-up day (20)
B47 = Number of non working days in month(5)
Result is obviously $8,100 and not % 12,500 as it should be.
I have no idea how to work backwards for sundays or count how many Sundays in month and subtract how many are left or have past.
Please assist.

 

[miss_ell]

Hello,

There is a roundabout solution, because NETWORKDAYS removes Saturdays as well as Sundays, so the remaining Saturdays have to be added back in.

In order to do this, a two-column table will need to be set up separately, or in a hidden sheet. Mine starts from M3.

A2 = cash-up date
B2 = remaining days
C2 = turnover per day
D2 = result.

M3 onwards = first to last days of the month
N3 onwards = the day of the week.

The formulas are:

A2 = (you add the date)
B2 =NETWORKDAYS(A2+1,EOMONTH(A2,0))+COUNTIF(N3:N33,6)
C2 = (you add the turnover per day)
D2 =B2*C2

M3 (first day of the month) =DATE(YEAR($A$2),MONTH($A$2),1)
M4 (second onwards) =IF(M3=EOMONTH($A$2,0),"",M3+1)
N3 (format as dddd) =IF(M3="","",IF($A$2 > M3,"",WEEKDAY(M3,2)))

I hope this helps.