0

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?

1 Answer 1

0

this should solve your problem:

=IF(WEEKNUM(TODAY())=WEEKNUM(A1),B1,CONCAT("x",B1))

(try this in column c)

2
  • Hi Gagan, thanks for your solution. However, I'm looking to update the values in the same column (i.e. column B) instead of adding a separate column C. Not sure if there is a way to achieve this.
    – Kushal
    Jul 11 at 15:29
  • I think it would be better to just hide the column B. I am not sure if you can have a fixed (source) value and a formula in the same cell. maybe you can replace the cell reference of B1 with the actual value (2 places) and keep this formula in column B.
    – Gagan
    Jul 11 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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