up vote 4 down vote favorite
share [g+] share [fb]

I am trying to show the Days between NOW() and the dates (dd/mm/yy) in, either Column B or Column C - depending which one is NOT blank

A         B         C
29/03/10            01/04/10
29/03/10  02/04/10
29/03/10            30/04/10
29/03/10  31/03/10
29/03/10  03/04/10

I currently have the formaul below and then drag it down, but it obviously means I need to go back and amend the 'errors'.

=DAYS360(A1,B1)

I always forget how to nest this type of NULL/BLANK thing so any help, or pointers to remember would be appreciated.

UPDATE:

I'm using =ROUND(MOD(C2-A2,24),2) instead of =DAYS360(). It gives me more accuracy (I think).

Thanks

Mike

link|improve this question
89% accept rate
feedback

1 Answer

up vote 5 down vote accepted

You can use an IF statement to achieve that as follows:

=IF(ISBLANK(B2);DAYS360(A2;C2);DAYS360(A2;B2))

alt text

link|improve this answer
I've shifted away from my DAYS360 to MOD and ROUND. So using your formula structure it looks like: IF(ISBLANK(B2);ROUND(MOD(C2-A2,24),2);ROUND(MOD(B2-A2,24),2)). Unfortunately this gives me an error message. Any ideas? – Mike Mar 29 '10 at 13:03
I think you should use comma instead of semicolon inside the formula. I have to use semicolon due to my regional settings. – Mehper C. Palavuzlar Mar 29 '10 at 13:13
2  
Save some typing =DAYS360(A2;IF(ISBLANK(B2);C2;B2)) – Christopher Oezbek Mar 29 '10 at 14:14
Changing to comma did the trick - I should have known. – Mike Mar 29 '10 at 14:22
Chris: Thanks I'd try your example as well, but I'm using ROUND and MOD now instead of DAYS360 - as I think it's more accurate for my needs. – Mike Mar 29 '10 at 14:23
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.