Date Formula

Well in those terms you cant!!
You can calculate a range of date differences (date of start of range to expected date) to (date of end of range to expected date)
Or
You can calculate and average date difference (date of midpoint of range to expected date)
All depends on what you want to do with the answer

Any other expert is requested for the answer of this tricky question?

The only way (I think) would be to assign a value to "Beg", "Mid" & "End, e.g. 1, 15 & 31. Then use those in your calculations.

With the data you have, you cannot.

My recomendaton would be to change the estimated dates to incorporate a real live date. maybe, Beg Dec could be 01 Dec, End Nov as 30 Nov, mid Mar as 15 Mar.

Otherwise, you simply can't calculate this- that's not how maths works...

Shehbaz,

Your question is not logical. How can you subtract between a fix value and range of values. Like 20 minus Big (where Big ca be between 1 and 10). Please understand. either you fix the value of Beg (like 1), Mid=15 and End-30 as already suggest in this forum.
Other issue is if cell value is "Beg Dec 14" so it becomes text, which need to be converted in to Date. That can be done by formulas if the year is 2014.

We will help you if your concepts are clear.

Regards,

A.B. Mati
Doha, Qatar

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 are clear now. Now tell me what is the formula for it?

Hi Fairchance,

I think I can see what you're after, but excel is basically a calculator and they're not very good with soft concepts like beg or mid as you've described them. The scenarios you've described in your post above do help. But, for example what would you calculate if the date wer 17 feb if Mid Feb is expected. Based on the data above it can't be +2 because that's been calculated from the end of the range and would in your early example. it would equally be confusing to say it was -3. There are many other undefined scenarios based on such a loose concept as Beg or Mid.

The forum will undoubtedly provide an answer, but the first step is for you to recognise the comment made in post #2, and bring clarity to the calculations you want to perform. It might help if you provide a list of dates from 1st month to the last day and show what you want to be calculated for each day. That will also help you understand the problems described above.

I know this isn't what you want, but a little effort now will pay dividends in the end (ie you'll get an answer).

Regards

Hi pjmorris

Please forget all above. i may clarify you with the following:

A B C
1 Expacted Date Real Date Formula (b-a)
2 beg Jan 14 17-jan-14 +7
3 mid jan 14 30-jan-14 +10
4 end jan 14 15-jan-14 -15
5 15-01-14 25-01-2014 +10

The question is that how the formula will be formed to recognize the values of expected date text i.e. end,mid,beg etc. We must convert these texts into number of ending range value then convert it into date format and then include it within formula. I hope you understand now. So what is the single formula to calculate such mixed values i.e. real date and exacted date? All Excel Expert and Gurus are also invited.

Regards
Fairchance

Insert a new column in-between "Exp Date" & "Act Date"
enter formula
=IF(LEFT(A2,3)="Beg",10&RIGHT(A2,7),IF(LEFT(A2,3)="Mid",20&RIGHT(A2,7),IF(LEFT(A2,3)="End",30&RIGHT(A2,7),A2)))
This will give you new dates to use in your calc.