Hi - I'm trying to create a spreadsheet which contains a column that
calculates the number of days between dates and then a cell that adds
up the total of the dates.
For the date calculation I have tried using =datedif(a1,b1,"d") - this
is fine if you enter both dates however, if some are to follow i.e you
have the date to enter in A1 but not b1 then the formula returns #NUM!
and consequently the addition cell will not give any result in respect
of other rows where you have been able to enter both dates - you could
say well dont enter any data until you have both dates however, the
purpose of the spreadsheet is to record info as its received. Ideally
I would like the date formula to return 0 if the date for cell b1 is
not yet known.

Sounds confusing but I dont know how to say any other way - can anyone
help?


--
martins
------------------------------------------------------------------------
martins's Profile: http://www.excelforum.com/member.php...o&userid=31616
View this thread: http://www.excelforum.com/showthread...hreadid=522849

Try:

=IF(ISERROR(DATEDIF(A1,B1,"d")),0,DATEDIF(A1,B1,"d "))

--
HTH

Sandy

with @tiscali.co.uk

"martins" wrote in
message ...

Hi - I'm trying to create a spreadsheet which contains a column that
calculates the number of days between dates and then a cell that adds
up the total of the dates.
For the date calculation I have tried using =datedif(a1,b1,"d") - this
is fine if you enter both dates however, if some are to follow i.e you
have the date to enter in A1 but not b1 then the formula returns #NUM!
and consequently the addition cell will not give any result in respect
of other rows where you have been able to enter both dates - you could
say well dont enter any data until you have both dates however, the
purpose of the spreadsheet is to record info as its received. Ideally
I would like the date formula to return 0 if the date for cell b1 is
not yet known.

Sounds confusing but I dont know how to say any other way - can anyone
help?


--
martins
------------------------------------------------------------------------
martins's Profile:
http://www.excelforum.com/member.php...o&userid=31616
View this thread: http://www.excelforum.com/showthread...hreadid=522849

Martins

I hope I understand, try...

IF(OR(A1="",B1=""),0,DATEDIF(A1,B1,"d"))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"martins" wrote in
message ...

Hi - I'm trying to create a spreadsheet which contains a column that
calculates the number of days between dates and then a cell that adds
up the total of the dates.
For the date calculation I have tried using =datedif(a1,b1,"d") - this
is fine if you enter both dates however, if some are to follow i.e you
have the date to enter in A1 but not b1 then the formula returns #NUM!
and consequently the addition cell will not give any result in respect
of other rows where you have been able to enter both dates - you could
say well dont enter any data until you have both dates however, the
purpose of the spreadsheet is to record info as its received. Ideally
I would like the date formula to return 0 if the date for cell b1 is
not yet known.

Sounds confusing but I dont know how to say any other way - can anyone
help?


--
martins
------------------------------------------------------------------------
martins's Profile:
http://www.excelforum.com/member.php...o&userid=31616
View this thread: http://www.excelforum.com/showthread...hreadid=522849

Thanks guys - -problem solved -


--
martins
------------------------------------------------------------------------
martins's Profile: http://www.excelforum.com/member.php...o&userid=31616
View this thread: http://www.excelforum.com/showthread...hreadid=522849