Conditional Formatting

[calvindjst]

Hello,

I want to find a way for the following:-
If I enter something in column A which is a date format, depending on when it is I would like a value to be put into a separate column

so for example:-
between the 5th December and 10th January 2014 - 30
between the 13th January and 20th January - 20
the rest of January - 10

In column A I put in 19th December then this will auto pupulate column D with the right value from above, which should be 30.

Is this possible?

Calvin

[Pete_UK]

It is not conditional formatting, which relates to changing the FORMAT of a cell depending on some condition - it is a conditional formula that you require. Try this in D2:

=IF(AND(A2>=DATE(2013,12,5),A2<=DATE(2014,1,10)),30,IF(AND(A2>=DATE(2014,1,13),A2<=DATE(2014,1,20)),20,IF(AND(A2>DATE(2014,1,20),A2<=DATE(2014,1,31)),10,"")))

then you can copy this down.

Hope this helps.

Pete

[etaf]

Try
=IF(AND(A2>=DATEVALUE("3/12/2013"),A2<=DATEVALUE("10/1/2014")),30,IF(AND(A2>=DATEVALUE("13/1/2014"),A2<=DATEVALUE("20/1/2014")),20,10))

[calvindjst]

Thanks Pete, that's worked like a charm!

[Pete_UK]

Glad to be of help - thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete