Date Formula

**fairchance** · 2015-02-23, 02:43 PM

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

**NBVC** · 2015-02-23, 02:48 PM

What should the result for those 3 be then?

**fairchance** · 2015-02-23, 03:05 PM

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?

**NBVC** · 2015-02-23, 03:18 PM

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?

**fairchance** · 2015-02-23, 03:38 PM

This is the actual question what i want i.e. top,bottom,average or other date value which will be put in such formula?

**NBVC** · 2015-02-23, 04:31 PM

Show what your expected results should be in those 3 cells.

**fairchance** · 2015-02-24, 02:49 AM

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