Problem with basic Excel formula 1

[DeepBlueIT]

I work away from home and am trying to create a spreadsheet which will automatically display the number of days spent away from home and the number of days spent at work.

Formula in C1 is =IF(A1>0,B1-A1+1,0)
Formula in D1 is =IF(A1>0,A2-B1-1,0)
Formula in C2 is =IF(A2>0,B2-A2+1,0) and so on
Columns C and D are Summed.

My problem is that I get strange negative figures in Columns C and D until dates are entered into Columns A and B
For example if I leave home on 01 January 2005 (A1) and arrive home on 10th January 2005 (B1) and then leave home again on 20th January 2005 (A2) then the tallies in C1 and D1 are correct but C2 displays -38371 and D2 displays -1 and of course the summed totals are also incorrect.

How do I get the spreadsheet not to display the negative (unwanted) figures and also to ignore these figures when summing the columns ?
I realise that the "errors" are generated because cells have no data entered in them but wondered if I could get round the problem ?

Thanks for the help

John

 

[dhulbert]

chance the A1>0 to A1<>'' and the A2>0 to A2<>'' and that should sort it out, then as long as the cells are empty it displays 0 once you populate them the formula works properly.

 

[DeepBlueIT]

Sorry, that didn't seem to work (perhaps I am being thick...??!!)
I opened a new spreadsheet and entered the formulae as you suggested, as =IF(A1<>",B1-A1+1,0) but a message came up to say there was a formula error and asked if I wanted to accept =IF(A1<>"",B1-A1+1,0) which I did. Same result as before.....

 

[AnotherHiggins]

Welcome to Tek-Tips, DeepBlueIT

Try this:

In column C:
[COLOR=blue white]=IF(B1-A1<=0,"",B1-A1+1)[/color]
In column D:
[COLOR=blue white]=IF(A2-B1-1<0,"",A2-B1-1)[/color]

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red] R.I.P Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[DeepBlueIT]

That works perfectly, thanks very much for your time and effort, much appreciated. Looking at the formula it makes logical sense !!!

John

 

[AnotherHiggins]

Glad to help! It always comes down to logic.

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red] R.I.P Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.