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>

 

[gaz_chops]

Did you see my post #17

 

[fairchance]

Dear gaz_chops

You are a genius expert as you gave me half part of formula. Now i want to combine it into single cell based on all hypothesis i explain above.

Regards

Shehbaz

 

[gaz_chops]

You asked
I need a formula to calculate the salespersons pay rate:

Leads 1-5 are paid at $10 each
Leads 6-10 are paid at $15 each
Leads 11+ are paid at $20 each

For example, 7 qualified leads in a day are paid at (5x$10)+(2x$15)=$80, 13 qualified leads in a day are paid at (5x$10)+(5x$15)+(3x$20)=$185.

March

		1	2	3	4	5
John	Qualified	4	5	5	6	6
Sally	Qualified	6	3	5	5	4
Ben	Qualified	5	6	4	5	5

<tbody>
</tbody>

The answer is:

John		40	50	50	65	65
Sally		65	30	50	50	40
Ben		50	65	40	50	50

<tbody>
</tbody>

What is the formula?

I replied.

 

[fairchance]

Thank you gaz_chop for reply of my previous post. Currently, i am stuck with my first post which is still important for me; the post i just publish regarding complex formula into single cell. I would be highly grateful to you if you would combine such formula in single cell.

Kind Regards

Shehbaz H.

 

[fairchance]

Dear Sir Gaz_chop

I have a clue from other source as is not working correctly:

=IF(ISNUMBER(A2),B2-A2,DAY(B2)-DAY(LOOKUP(LEFT(A2,3),{"Beg","End","Mid"},{0,11,21})))

It does not fulfill the following criteria:

If B4 within Expected date range then B4-A4=0

Regards

Shehbaz H.

 

[gaz_chops]

Test this
=IF(AND(LEFT(A2,3)="beg",DAY(B2)>10),B2-(10&RIGHT(A2,7)),IF(AND(LEFT(A2,3)="mid",OR(DAY(B2)<11,DAY(B2)>20)),B2-(20&RIGHT(A2,7)),IF(AND(LEFT(A2,3)="end",DAY(B2)<21),B2-(31&RIGHT(A2,7)),B2-A2)))

 

[fairchance]

Thank you so much sir, we are much near to the solutions. Currently the following are incorrects:

Beg Dec 14

9-Dec-14

-1

0

#VALUE!

<tbody>
</tbody>

Beg Jan 15

22-Dec-14

-19

0

-19

<tbody>
</tbody>

The correct answer is 0 instead of #value! and 12 instead of -19

Kindly look into it

Thanks

 

[fairchance]

Thank you so much sir, we are much near to the solutions. Currently the following are incorrects:

Beg Dec 14

9-Dec-14

-1

0

#VALUE!

<tbody>
</tbody>

Beg Jan 15

22-Dec-14

-19

0

-19

<tbody>
</tbody>

The correct answer is 0 instead of #value! and 12 instead of -19

Kindly look into it

Thanks

 

[fairchance]

Thank you so much sir, we are much near to the solutions. Currently the following are incorrect:Beg Dec 14 9-Dec-14 #VALUE!Beg Jan 15 22-Dec-14 -19 -19The correct answer is 0 instead of #value! and 12 instead of -19 Kindly look into itThanks

 

[gaz_chops]

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