What should the result for those 3 be then?
Dear Sir,
I am seeking data formula/function that could calculate the number of days from the expected date to actual date.
I can come up with formula to calculate the number of dates between real dates and expected dates. Here is the example parameters:
Range Definition: (days of month) Beg 1-10
mid 11-20
End 21-30
Expacted date for submission Actual Date Formula
21-Dec-14 21-Dec-14 0 21-Dec-14 18-Dec-14 -3 21-Dec-14 27-Dec-14 6 Beg Dec 14 9-Dec-14 #VALUE! End Dec 14 18-Dec-14 #VALUE! Mid Dec 14 27-Dec-14 #VALUE! How can i calculate date difference in case of date relate to particular range? Any one can help please.
Regards
Shehbaz
What should the result for those 3 be then?
I want a formula to calculate the difference between real data and expected date. what will be the formula in such particular case when date is based on some range assumption?
I understand, but each range has about 10 days... do you want to subtract the bottom of the range, the top of the range, which date to use?
This is the actual question what i want i.e. top,bottom,average or other date value which will be put in such formula?
Show what your expected results should be in those 3 cells.
I would need to calculate all possible ranges depending on the actual date, as it will differ in most of the cases.
That is, if for example, I have expected date is Mid Feb and actual date is 23 Feb, it should come out positive value of +3, as the range value for Mid is 11 to 20
- for example If expected date is Mid Feb and actual date is 15 feb, the value should be 0, as it was within the range of Mid
- Expected date is Mid Feb and the actual date is 09 Feb, the value should be -2, as range starts at 11.
I hope you will be clear now now give me the formula
Regards
I think this takes far too much effort. What happens when a month has 31 days or 28/29, using a default end of 30 will return a wrong result.
Sorry, the end rang is 21-31 please correct it.
Correct what, I said it is too complex because months don't all have 31 days. What happens in Feb, or Sep, Apr, Jun and Nov?
Bookmarks