Date Calculation

[Papi]

There are five cells involved

Cell B1 is date requested (Preference 4)
Cell B2 is date ordered (Preference 3)
Cell B3 is date supplied (Preference 2)
Cell B4 is date the project ends (Preference 1)
Cell B5 is the calculation of dates.

If there is not project end date then the formula needs to count the difference by preference. If there is a date in B1 then it will compare today to that date by the days difference i.e. =today()-B1. But if there is a date in B2 then the preference is to use today()-b2 and if there is a date in B3 then that is the date to be used next. And once a date is entered to complete the project then cell B4 over-rides all the other dates. I hope this makes sense.

 

[Robert Mika]

Try this:

=IF(B4>0,TEXT(B4,"dd mmm yyyy"),IF(B3>0,C1-B3,IF(B2>0,C1-B2,IF(B1>0,C1-B1,""))))

C1=TODAY()

 

[Peter_SSs]

Perhaps this is what you mean?

Excel Workbook
	B
1	05-June-2011
2	23-June-2011
3
4
5	44
Date Calc

 

[Papi]

Thanks for your help. I had to move the sheet around to accomodate another department and have not been able to make it work so attached a snippet of what it would look like. Also, if there are no dates in cells B3, B4, E4 and also E6 the Days then the cell E7 should remain empty.

Shop Date Form.xls
	A	B	C	D	E
1	Form1032	Code	DateSectA		DateSectB
2		B
3	Request	Jul1,11Fri
4	Ordered	Jul15,11Fri		Supplied	Jul28,11Thu
5				Comments
6				EndDate	4-Aug
7				Days
SAW

 

[Peter_SSs]

My previous suggestion will not work witha disjoint range like that. Also, I'm not sure what you meant in your original post by "once a date is entered to complete the project then cell B4 over-rides all the other dates".

Anyway, see it this covers at least part of your requirement.

Excel Workbook
	A	B	C	D	E
1	Form*1032	Code	Date*Sect*A		Date*Sect*B
2		B
3	Request	1/07/11
4	Ordered	15/07/11		Supplied	28/07/11
5				Comments
6				End*Date
7				Days	10
Date Calc

 

[Robert Mika]

Try this:

=IF(E6>0,TEXT(E6,"dd mmm yyyy"),IF(E4>0,TODAY()-B3,IF(B4>0,TODAY()-B4,IF(B3>0,TODAY()-B3,""))))

 

[Papi]

Hello Peter and Robert,

Thanks for responding back. I was just shutting down at work and will be going home and review your work when I get there.

Thanks again and will talk soon...

 

[Peter_SSs]

Try this:

=IF(E6>0,TEXT(E6,"dd mmm yyyy"),IF(E4>0,TODAY()-B3,IF(B4>0,TODAY()-B4,IF(B3>0,TODAY()-B3,""))))

Robert

You may well be right with what the OP wants to do if there is a date in E6.
However, if so, I'm wondering why you would return that result as text and not as a date? Why not this ..

=IF(E6>0,E6, ...

Edit: Perhaps the formatting of the result cell may be the answer to my question.

Also, I suspect you have a typo later in your formula and meant that red bit to be E4?

 

[Robert Mika]

Peter,

Yes the formating is the answer to your question.
From what I assume the OP wants to return number unless there is a date in E6.

Thank you for pointing out at my typo.

Papi,

The formula shoudl be:
=IF(E6>0,TEXT(E6,"dd mmm yyyy"),IF(E4>0,TODAY()-E4,IF(B4>0,TODAY()-B4,IF(B3>0,TODAY()-B3,""))))

or combainig Peter's and my effort:
=IF(B3&B4&E4&E6="","",IF(E6>0,TEXT(E6,"dd mmm yyyy"),TODAY()-CHOOSE(COUNT(B3,B4,E4),B3,B4,E4)))

Peter,
A nice way to deal with the empty cells and I like the COUNT/CHOOSE formula.

 

[Papi]

Thanks again Gents.

This is on track but may be sliding a little bit sideways by my poor way of trying to state what I am in need of. Also, I was wrong on my statement of "once a date is entered to complete the project then cell B4 over-rides all the other dates". What that should have said was cell E6. What I am saying is that once there is a confirmed end date that the formula should look at E6 against one of the other dates but in the sequence that they fall in. Cell E7 will always be a number, not a date, as it looks at the various dates and returns the number between either E6 (if there is a date in E6) or using =today() against one of the three dates in cells E4, B4, or B3.

The part that confuses me is that there are times they may only have one date between cells E4, B4, AND B3. They may dates in E4 and B4 but not in B3 or only in say B3 etc. If B3 showed Jul 1 and the other cells were empty on July 20 the days would be 19. If B3 and B4 had dates of July 1 and July 5 on July 20 then the formula would return 15 days. If B3, B4, and E4 had days of July 1, July 5, and July 10 on July 20 then the days returned would be 10. If an end date was entered on July 17 with all the cells having the numbers mentioned above the days would be 3 days between Jul 17 and July 20.

The roadblock for me is when not all the cells have dates and it gives errors. The sequence of importance to compare is by first looking at E6 and going backwards by finding a date from E6 and if nothing in E6 then looking at E4 and if nothing in E4 then looking at B4 and if nothing in B4 then finally looking at B3. There will always be at least one of the three date cells with a date but not necessarily the other two and not in any given order. Sorry for the confusion but hopefully this makes sense.