Date Formula

[fairchance]

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

<tbody>
</tbody>

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! <tbody> </tbody>
How can i calculate date difference in case of date relate to particular range? Any one can help please. Regards Shehbaz

<tbody>
</tbody>

 

[fairchance]

yes please guide with input cell range, criteria cell range and output cell range so that i may place the data in right places
Moreover please review my posts in excel guru you will really enjoyed it

Date Formula - Page 2
username fairchance
password register

 

[gaz_chops]

OK but 1st try this formula, (it's a bit long), if the date in B is earlier than A it will use the lower number to calculate, if B is greater than A it will use the higher number.
e.g. A = mid jan 14, B = 24-12-13 then it will use 11 for mid,

=IF(B2="","Not Applicable",IF(ISNUMBER(A2),B2-A2,IF(AND(LEFT(A2,3)="beg",DAY(B2)<=10),0,IF(AND(LEFT(A2,3)="mid",DAY(B2)>10,DAY(B2)<=20),0,IF(AND(LEFT(A2,3)="end",DAY(B2)>=20),0,IF(AND(LEFT(A2,3)="beg",B2<DATEVALUE((1&RIGHT(A2,7)))),B2-(1&RIGHT(A2,7)),IF(AND(LEFT(A2,3)="beg",B2>DATEVALUE((10&RIGHT(A2,7)))),B2-(10&RIGHT(A2,7)),IF(AND(LEFT(A2,3)="mid",B2<DATEVALUE((11&RIGHT(A2,7)))),B2-(11&RIGHT(A2,7)),IF(AND(LEFT(A2,3)="mid",B2>DATEVALUE((20&RIGHT(A2,7)))),B2-(20&RIGHT(A2,7)),IF(AND(LEFT(A2,3)="end",B2<DATEVALUE((21&RIGHT(A2,7)))),B2-(21&RIGHT(A2,7)),IF(AND(LEFT(A2,3)="end",B2>DATEVALUE((31&RIGHT(A2,7)))),B2-(31&RIGHT(A2,7)),)))))))))))

 

[gaz_chops]

I have copied my file to dropbox.
Sheet 1 is your 1st question
Sheet 2 is your 2nd question

https://www.dropbox.com/s/ayempdgzp5km1k8/fairchance.xlsx?dl=0

 

[gaz_chops]

Above formula is wrong (forgot the site doesn't like " < ") copy it from the file in dropbox.

 

[oldbrewer]

first one question, then another different, poor definitions of what is wanted - need to build it one stage at a time

eg if a cell has "mid feb" in it how can I convert it to 14-feb-2015 (which is a normal date value)

or it is midfeb - which is harder to do

then ask questions re the next stage

and do not suddenly switch to a brand new topic

 

[pjmorris]

Hi fairchance,

In your post #20 you said:

Date Range for expected date

Beg 1-10
Mid 11-20
End 21-31

If B4 > Expected date range then B4-A4=B4-last range value
If B4 within Expected date then B4-A4=0

There are three conditions are in formula you know if,than,else

Based on this I would offer the following:

=IF(ISNUMBER(--LEFT(A4,1)),B4-A4,IF(LEFT(A4,1)="B",MAX(0,B4-DATEVALUE("10 "&RIGHT(A4,LEN(A4)-FIND(" ",A4)))),IF(LEFT(A4,1)="E",MIN(0,B4-DATEVALUE("21 "&RIGHT(A4,LEN(A4)-FIND(" ",A4)))),IF(DAY(B4)<11,B4-DATEVALUE("11 "&RIGHT(A4,LEN(A4)-FIND(" ",A4))),IF(DAY(B4)>20,B4-DATEVALUE("20 "&RIGHT(A4,LEN(A4)-FIND(" ",A4))),0)))))

This produces the following results:

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 0
End Dec 14 18-Dec-14 -3
Mid Dec 14 10-Dec-14 -1
Mid Dec 14 23-Dec-14 3

Note that the formatting of the dates as shown above is important for this to work.

Hope this helps a bit!! If you want to understand how it works you'll need to use the 'Evaluate Formula' option on the Formula's ribbon.

Regards

 

[gaz_chops]

Let me know if you can't access the file i uploaded.

Gaz

 

[fairchance]

Dear Sir Gaz
Hi


I have another turn in my date formula story

The scenario is:

If real date is within expected date range then answer is 0
If real date is less than expected date range initial value then answer is real date - initial range value (in all three cases i.e. beg,mid,end)
If real date is greater than expected date range end value then criteria is real date-ending range value ( in all cases i.e. beg,mid,end)

Kindly amend the formula accordingly.

Regards

 

[gaz_chops]

That is how my new formula works! Did you look at the file in dropbox?

As posted earlier "if the date in B is earlier than A it will use the lower number to calculate, if B is greater than A it will use the higher number.
e.g. A = mid jan 14, B = 24-12-13 then it will use 11 for mid"

Gaz

 

[fairchance]

Sir Gaz!!!

How quick you are? The new formula is working correctly.

Thank you. Can i have your email address?

Kind Regards

Shehbaz H.