0

I want to write a conditional formatting formula that will convert

1-3 to 1-Thursday,

2-2 to 2-Wednesday,

3-1 to 3-Tuesday,

4-0 to 4-Monday.

The rationalization being I have a list of committee meeting dates, which fall on ordered days of the week, not specific days. So I want a date type (as in "second Wednesday") that can be inputted and sorted numerically but read by the user as a string. I already know that the following formula will spit out a "Monday":

=TEXT(WEEKDAY(0),'dddd')

and that I can concatenate specific valued strings such as: =CONCATENATE(CONCATENATE("2,"-"),TEXT(WEEKDAY(0),"dddd"))

to make "2-Monday".

So how can I pose a formula that will interpret a pattern in the form number-hyphen-number to this for arbitrary numeric values (assuming the 3rd character is a number 0-6)?

0

One solution I have come up with does not use conditional formatting. Instead, it uses a column of hyphenated, single-digit numbers to fill another column.

Test case

Cell E5: 1-3

Cell F5: =CONCATENATE(LEFT($E5,2),CHOOSE(RIGHT($E5,1),"MONDAY","TUESDAY","WEDNESDAY","THURSDAY","FRIDAY","SATURDAY","SUNDAY"))

RESULT: 1-WEDNESDAY

I have verified this will work for any digit 1...7 on the right-hand-side. This indexing system was nudged from 0...6 to 1...7 because the CHOOSE function begins with 1, not 0.

A conditional format formula would still be preferable to this because then we would not need the extra column to do the conversion.

| improve this answer | |

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.