Tricky Excel spreadsheet thing

  Ray5776 17:33 23 May 08

Hi everyone,
I am trying to create a spreadsheet to monitor cashflow but it is not as easy as I thought.
Using XP pro and Excel.
1st column is the date of an invoice.
2nd column is the ammount.
3rd column is the date paid.
4th column is how long it took to be paid in days.
5th column is to show invoices outstanding and how many days.
This needs to be time related ie add a day every day unless it can be configured to the clock.
If that is not hard enough for you then I would also like all previous paid invoices that took longer than 30 days to show in red and all outstanding invoices over 30 days to show in red.

Hope someone can help, thanks.


  rossgolf 17:43 23 May 08

vog will be along soon to help you out.
the 30 days thing will be needed to be done by conditional formating.

do you have excel 2007?>

  VoG II 17:54 23 May 08

Formula in D2 and filled down


Formula in E2 and filled down


Select the whole table of data from B2 across and down. Format > Conditional Formatting. On the drop-down select Formula Is and enter the formula


then click the Format button and select Red for the font colour then click OK.

  Ray5776 17:59 23 May 08

Thanks rossgolf, yes it will need conditional formating, a bit complicated for me hence the posting
also tying up the dates to work out the outstanding days is not easy for me. vog has helped me a lot in the past with similar things he seems to be the Excel guy, hope he comes on the site and can help but I am not biased I will take help from anyone that knows more than me and I am sure that there are plenty.



  Ray5776 18:03 23 May 08

Thanks vog, that was quick,
I will spend a few hours on this as still not a fast learner as too many other things to do. Will post back when done or if need more help.



  Ray5776 18:08 23 May 08

This is a very complicated set of formulae.


  Ray5776 19:02 23 May 08

Ok knew I would struggle, why are we starting from D2 and not D1.

  rossgolf 19:09 23 May 08

becuase i would assume D1 would be the title of the column/row.

  Ray5776 19:35 23 May 08

I see your reasoning rossgolf but I was trying to set up from a virgin sheet just to establish the formulae,
I have now made row one the title bar and will enter vogs formulae into D2 & E2.


  Ray5776 20:37 23 May 08

All I get when I enter the formula is #VALUE!

  VoG II 21:44 23 May 08

The formulas all work - I tested them before posting. I suggest that you copy them from this page and paste them in.


A2 = 21/04/2008
C2 = 25/04/2008
Result in D2 is 4

With the same date in A1 and C2 blank result in E2 is 2.

Are your dates actually dates recognised by Excel?

If you like you can contact me by clicking my envelope and I will send you a working example. Please indicate which version of Excel you are using.

This thread is now locked and can not be replied to.

The Legend of Zelda Breath of the Wild review: Five hours with Zelda on the Nintendo Switch

1995-2015: How technology has changed the world in 20 years

How the painting-like animated sequences in A Monster Calls were created by Glassworks Barcelona

The 22 best Safari extensions | Best Safari plugins: Improve Apple's Safari web browser with these…