How can I have 2 date conditions on the same cell?

[Simon2]

In B2 i have a date 25/05/2008 the date an enquiry opened.
In B3 i have formula =DATEDIF(B3,TODAY(),"d")&" " this shows the days
elapsed since the enquiry opened.

I want to in B4 add the closure date of the enquiry, but then also as the
closure date is added have B3 stop counting, so the result shows the number
of days to complete the enquiry in B3, so B3 would then =B4-B2 and the rest
on Column 3 would continue to use the above formula until they were closed.

Any ideas? I am using Excel2007.
Many Thanks
Simon

 

[Fred Smith]

=if(b4="",today()-b2,b4-b2)

The above assumes that your formula listed for B3 is a typo, as it would be
a circular reference. Also, you don't need Datedif to calculate the
difference in days -- simple subtraction will suffice.

Regards,
Fred.

 

[Simon2]

I cant get that formula to work Fred.

My example may have been misleading , try this one please.

In B3 i have a date 25/05/2008 the date an enquiry opened.
In C3 i have formula =DATEDIF(B3,TODAY(),"d")&" " this shows the days
elapsed since the enquiry opened. (Showing 77 days today)

I want to in D3 add the closure date of the enquiry manually, but then also
as the closure date is added have C3 stop counting, so the result shows the
number of days to complete the enquiry in C3, so C3 would then =D3-B3 and the
rest of Column 3 would continue to use the above formula until they were
closed.

I hope that makes more sense, I switched columns/rows in my mind when
writing it I think!
Thanks for your help
Simon

 

[Sandy Mann]

Try something like:

=MIN(TODAY(),D3)-B3&" Days "&IF(D3="","today","on closure")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Simon2]

Hey thank you Sandy, that works a treat.

Regards
Simon

 

[Sandy Mann]

You are very welcome. Thanks for thaking the trouble to post back and let
us know.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk