Tricky Excel spreadsheet thing

  Ray5776 17:33 23 May 2008
Locked

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.

Ray

  rossgolf 17:43 23 May 2008

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 2008

Formula in D2 and filled down

=IF(C2="","",C2-A2)

Formula in E2 and filled down

=IF(C2="",TODAY()-A2-30,"")

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

=OR($D2>30,AND($C2="",$E2>0))

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

  Ray5776 17:59 23 May 2008

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.

Thanks

ray

  Ray5776 18:03 23 May 2008

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.

Thankyou

Ray

  Ray5776 18:08 23 May 2008

This is a very complicated set of formulae.

Ray

  Ray5776 19:02 23 May 2008

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

  rossgolf 19:09 23 May 2008

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

  Ray5776 19:35 23 May 2008

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.

Ray

  Ray5776 20:37 23 May 2008

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

  VoG II 21:44 23 May 2008

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

Examples

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.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone