I have a list of dates (including previous, current, and future weeks) and there is a number associated with each date. Example:

enter image description here

I want to add 'x' in front of all the values belonging to dates from previous or future weeks. Only values associated with dates from the current week should remain unchanged (i.e. without 'x'). Also, this should get updated automatically as the date shifts to next week and so on. So considering the current week's date range is from Mon, Jul 4 to Fri, Jul 8 - the expected output should look like this:

enter image description here

Any help/suggestions on this?

2 Answers 2


this should solve your problem:


(try this in column c)


Formula in C2

Single array formula that will calculate all rows.

       B2:B, "x"&B2:B),))  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.