Discount % Between 2 Dates

[joaniemic]

I am trying to have a 5% discount appear if an order is placed between (and
including) two dates...
If C41 is between (and including) 11/10/2008 and 01/02/2009 and E30 is equal
to or greater than $600, B32 is 5%, otherwise B32 is at 0%.

 

[Sandy Mann]

Try:

=E30-(5%*E30*(C41>=DATE(2008,11,10))*(C41<=DATE(2009,2,1)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[T. Valko]

Entered in B32:

=IF(AND(C41>=DATE(2008,11,10),C41<=DATE(2009,1,2),E30>=600),5%,0%)

Or:

=(C41>=DATE(2008,11,10))*(C41<=DATE(2009,1,2))*(E30>=600)*5%

 

[JE McGimpsey]

One way:

B32: = 5% * AND(C41>=Date(2008,11,10), C41<=DATE(2009,1,2), E30>=600)

Note: Your dates are ambiguous - I assumed MM/DD/YYYY rather than
DD/MM/YYYY

 

[joaniemic]

I have my date formatted as 11/10/08 (for November 10, 2008). Would your
formula change since this is the case? It doesn't seem to be working. Thanks!

 

[Sandy Mann]

DATE() uses the arguments Year, Month, Day so it should not matter what date
system you have set. What would have helped was if I had the month and day
the right way round in the second date try:


=E30-(5%*E30*(C41>=DATE(2008,11,10))*(C41<=DATE(2009,1,2)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Oops! I see what you mean I got so carried away with the 5% I forgot about
the >=600 part. Make that:

=E30-(5%*E30*(C41>=DATE(2008,11,10))*(C41<=DATE(2009,1,2))*(E30>=600))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk