Date Count

[martins]

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?

[Sandy Mann]

Try:

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

--
HTH

Sandy
[email protected]
[email protected] with @tiscali.co.uk

"martins" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Nick Hodge]

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
[email protected]HIS


"martins" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

Thanks guys - -problem solved -