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

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

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

=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" wrote in message
...
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

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

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




"Fred Smith" wrote:

=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" wrote in message
...
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

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

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


Replace @mailinator.com with @tiscali.co.uk


"Simon2" wrote in message
...
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




"Fred Smith" wrote:

=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" wrote in message
...
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

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

Hey thank you Sandy, that works a treat.

Regards
Simon


"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Simon2" wrote in message
...
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




"Fred Smith" wrote:

=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" wrote in message
...
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

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

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


Replace @mailinator.com with @tiscali.co.uk


"Simon2" wrote in message
...
Hey thank you Sandy, that works a treat.

Regards
Simon


"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Simon2" wrote in message
...
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




"Fred Smith" wrote:

=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" wrote in message
...
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