| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I have 2 date conditions on the same cell?
|
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif Conditions - Use of conditions that vary by cell value | Excel Discussion (Misc queries) | |||
| Conditional formatting Based on cell A text with conditions in Cell B | Excel Discussion (Misc queries) | |||
| Multiple Logical Conditions With Date and String Comparison Not wo | Excel Worksheet Functions | |||
| how to count 2 date columns conditions. | Excel Discussion (Misc queries) | |||
| Adding today's date if certain conditions apply | Excel Worksheet Functions | |||
Linear Mode
