Dates

[Robo]

Hi
I have a spreadsheet with a monthly cashflow and a weekly cashflow.

On the monthly cashflow the payments go into the relevant month, however, I have the dates the payment was received, but I need that to go automatically onto my weekly cashflow?

Any offers please

Thanks

Robo

[martindwilson]

not sure where you want or what you want but if you enable the analysis pakadd in (tools/add ins) then
=WEEKNUM is added to your functions
=(WEEKNUM(A1,2)-30) the value 2 means week counted rom monday if omited or 1 sunday,
will return the week corResponding to the date in a1 [but must be whole date and cell formated as date] (i used
-30 because your year starts in august)
using that info you should be able to pull it into wherever you want.

[Robo]

Thanks, will try it.

[ratcat]

Hi
I have a spreadsheet with a monthly cashflow and a weekly cashflow.

On the monthly cashflow the payments go into the relevant month, however, I have the dates the payment was received, but I need that to go automatically onto my weekly cashflow?

Any offers please

Thanks

Robo

G'day

not sure where you want or what you want

I'm in the same boat as martin, but there my 2 cents worth.

The formula was used is a INDEX MATCH
EG

Please Login or Register  to view this content.

Cheers

[Robo]

Hi

I could not get it to work.

Can you give me an example. The values on Cred Pay, I would like on the Cash Flow, in the correct weeks.

Thanks for your help

Robo

[ratcat]

Hi Robo,

Are you refering to martin or I (ratcat)

Thanks

[ratcat]

Hi

I could not get it to work.

Can you give me an example. The values on Cred Pay, I would like on the Cash Flow, in the correct weeks.

Thanks for your help

Robo

I think I work it out.!?!?

You want the payment and the dates of those payment are located in col C on the Cred.pay sheet then transfer to cash flow sheet in the right places

[Robo]

Hi

The days paid have got to be split by weeks on the cash flow. The date paid each month, is in the day paid in month

Thanks

Robo

[Robo]

yes thats right

[ratcat]

G'day Robo,

Here the final product *I think*

You'll see that there is a few hidden rows and columns that to automate the formulas.

The Paid Day on the Cred.pay sheet can be changed the rest of the information will follow.

The months and the days (Rows 4 & 5) in the Cash Flow sheet can be also for can be changed for other future references. Also in the Cre.pay sheet rows 1 to 14.

That's for the INDEX and MATCH formula used.

More than welcome to PM me for further information.

Hope that helps


Cheers

[Robo]

Sorry for the delay in saying thanks, Ive been away from the office. It worked a treat.

I built the ISNA into your formula.

Thanks

Robo