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.

 

[James006]

Hi,

With an array formula, you can count the number of Sundays between two dates :

=SUM(N(WEEKDAY(ROW(INDIRECT(INT(A2)&":"&INT(B2))))=1))

HTH

 

[pannells]

Thanks James006
the "A2" & "B2" cells refer to what infomation?
Once I know that thenI can drop your formula in to see if you works - no doubt it probably does.

 

[James006]

Sorry for the lack of precision ...

A2 is your start date and B2 is your end date

For an array formula, use control+shift+enter ... not just enter

HTH

 

[pannells]

Thanks - no problem.
As I have no idea as to what your formula does - plesae could you add the balance to make it complete based on the info provided.
Average t/o for month - Cell A1
not sure what else you need due to not knowingwhat exactly the your complex formula does.

 

[James006]

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.

The formula counts the number of Sundays ... between two dates ...

 

[pannells]

Ok - dumped your formula in but it seems to be amounting to only calculating 5 working days left and not nine based on the info below.
31 daysin March
last cash up day was 20th
Woking days in March - 26
3 Sundays have passed
2 Sundays still to go
That leaves 31-20-2=9 working days.
Not 5 as your formula gets to.

 

[James006]

Let me clarify once more :

The formula is not a substitute for your own calculation ...

The formula allows to calculate what you are calling the "Sundays still to go" ...

In your example, in order to get 2 Sundays still to go :

A2 = date(2013,3,20)
B2 = date(2013,3,31)

By applying the formula, you will get 2 ... which is the number you need to deduct from 31-20 in order to reach 9

Hope this clarifies ...

 

[pannells]

Thanks James006
Got it - sorry - took a while to follow the thought process of the formula in my head.
It works.
Many thanks for yout time and dedication to an incredible program. Whan one knows how to use it completely.

 

[James006]

Glad you could fix your problem ...!!!