Tough set of formulae - IF, AND and probably other functions needed!

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

Your workbook would be more useful Jonathan, so we can see all the data (such as E1, E4, F4, etc.).

Here you go, Bob - I couldn't find the attachments button before!

Order Book.xlsx

I think tat I have got it.

Try these formulae

in G4, enter =IF(H4<>0,DATE(RIGHT(H3,4)+1,1,1)-DATE(RIGHT(H3,4),1,1)-H4,0)

in H4, enter =MAX(0,MIN(DATE(RIGHT(H3,4)+1,1,1),$F$4+1)-MAX(MAX($E$1,$E$4),DATE(RIGHT(H3,4),1,1)))

copy H4 across to K4

Excellent, Bob - G4 needed tweaked a bit to do something else I wanted, but H4, which was the one I was really stuck on, is perfect! Many thanks!