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]

Sir, I have tested and found same mistake
result is converted from value to -1 instead of 0 and -19 instead of 12

 

[gaz_chops]

Test (note -19 is correct as "beg" = 10 for calc purposes).

=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(LEFT(A2,3)="beg",B2-(10&RIGHT(A2,7)),IF(LEFT(A2,3)="mid",B2-(20&RIGHT(A2,7)),IF(LEFT(A2,3)="end",B2-(31&RIGHT(A2,7)),B2-A2))))))

 

[fairchance]

Sir, Thank you so much many expert tried on excelguru forum but failed. I really appreciate your caliber and capabilities. Sir, another criteria may please be incorporte with this formula

=IF(NOT(ISBLANK(a2)),(b28-a2),"not applicable")

It is in a case of blank value of expected date

Kind Regards

Shehbaz H.

 

[gaz_chops]

Try
=IF(A2="","Not Applicable",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(LEFT(A2,3)="beg",B2-(10&RIGHT(A2,7)),IF(LEFT(A2,3)="mid",B2-(20&RIGHT(A2,7)),IF(LEFT(A2,3)="end",B2-(31&RIGHT(A2,7)),B2-A2)))))))

 

[fairchance]

Sir it mean if the value of expected date is blank then reply may be filled as "not applicable"

 

[fairchance]

I really salute you sir. You are so nice and genius person in the world of "Excelologist" Do not worry it my self created term
By the way what is your name sir?

 

[gaz_chops]

Simply change the 1st A2 to B2
=IF(B2="","Not Applicable",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(LEFT(A2,3)="beg",B2-(10&RIGHT(A2,7)),IF(LEFT(A2,3)="mid",B2-(20&RIGHT(A2,7)),IF(LEFT(A2,3)="end",B2-(31&RIGHT(A2,7)),B2-A2)))))))

 

[gaz_chops]

Lol took a while but near to what you wanted, there is probably a simpler way, but for now hopefully it's near enough.

Take a look at my post regarding the other calculation you wanted.

Regards
Gaz

 

[fairchance]

Sir Gaz can you guide me about previous post?

 

[gaz_chops]

Do you mean this one?

Leads 1-5	5	$10
Leads 6-10	10	$15
Leads 11+		$20
March
		1	2	3	4	5
John	Qualified	4	5	5	6	6
Sally	Qualified	6	3	18	5	4
Ben	Qualified	7	6	4	5	5
Harry	Qualified	11	13	4	5	13
John		$40	$50	$50	$65	$65
Sally		$65	$30	$285	$50	$40
Ben		$80	$65	$40	$50	$50
Harry		$145	$185	$40	$50	$185
Create the small table in B1:C3
FORMULA in D15
=IF(C9<=$B$1,C9*$C$1,IF(AND(C9>$B$1,C9<=$B$2),((C9-$B$1)*$C$2)+($B$1*$C$1),IF(C9>$B$2,((C9-$B$2)*$C$3)+($B$1*$C$2)+($B$1*$C$1),"")))

<tbody>
</tbody>