Hi all,

I'm trying to simplify a workbook required by my Finance function which reports how much of a confirmed work pipeline the company has. I merrily volunteered to automate the Excel spreadsheet, before realising how tricky it was going to be!

I've attached two pictures of the spreadsheet below and will then try and explain what I have done so far.

1.JPG2.JPG

- Cells E4 and F4 are simple date functions
- Cell G4 is: =IF(E4="","ERROR",IF(F4<E4,"ERROR",IF(F4="","ERROR",IF(E4>E1,0,IF(F4>E1,E1-E4,F4-E4)))))
- Cell H4 is: =IF(E4>DATE(2012,12,31),0,IF(F4<E1,0,IF(F4>DATE(2012,12,31),DATE(2012,12,31)-E1,F4-E1)))
- Cell L4 is: =SUM(H4:K4)
- Cell M4 is manual input
- Cell O4 is: =G4*M4
- Cell Q4 is =L4*M4
- Cell S4 is: =O4+Q4

The formulae I need to develop are for Cells I4, J4, K4, U4, W4, Y4 and AA4. These have all appeared easy to start with until I start testing them out and realise that scenarios all occur which cause them to fail.

For example, with Cell I4, I need the following:
- If E4 and F4 <= 31/12/12, then I4 = 0
- If E4 >= 01/01/14, then I4 = 0
- If E4 and F4 are in 2013, then F4-E4
- If E4 is in 2013 and F4 >= 01/01/14, then I4 = 31/12/13 - E4

I've tried various nested IF and AND functions, but can't make it all work.

I might just have created a monster, but I'm sure someone out there will be able to help or point out errors or simplifications in the formulae I have created so far!

Many thanks in advance ... and in hope!

Jonathan