Conditional formatting in excel

December 20, 2022 10:30 AM

I'd like to have a spreadsheet highlight a row one color if the date is between the 1st-15th of a month, and another color for the 16th-31st of a month (there will be multiple rows with each date), but I can't figure out how to do it and google is failing me.
posted by BuddhaInABucket to Computers & Internet (5 answers total)

I'm not sure that this can be done directly just on the value of the date -- dates are stored as integers, so you need to colour an arbitrary subset of them where 44915 (today) to 44926 (Dec 31) is one color and 44927 to 44941 are the other. If months were even lengths, you could do some tricky modulus math, but in the real world that'll fall over by the end of February.

What you can do is make a column next to your date that does the actual day-of-month calculation, if the date is in A2, you could put in B2 the formula =DAY(A2). You could then conditionally format that column as you normally would.

Or you could conditionally format the column with the dates in it; select A2...A# (however many dates you have) and apply Conditional Formatting / New Rule / Use a formula to determine which cells to format. In the space for the formula, you then put =B2>15 and then apply whatever format you want in the Format... button. And then you could hide column B.
posted by Superilla at 10:42 AM on December 20, 2022 [1 favorite]

Here's how I did it - can't guarantee this is perfect, but it "works on my machine" as they say. (A Mac, with whatever the most recent version of Excel is.)

• Highlight the cells with the dates in them.
• Go to Format > Conditional Formatting. Hit the little plus sign to make a rule.
• Choose "Style: Classic", then "use a formula to determine which cells to format".
• Type into the box =DAY(A1)<=15 and choose the formatting you want for the first half of the month. (You shouldn't necessarily use A1 there - use the upper-left cell of the range you're trying to format.)
• Duplicate the rule, change the formula to DAY(A1)>15 in the duplicate rule, and choose the formatting you want for the second half.

posted by madcaptenor at 10:44 AM on December 20, 2022 [10 favorites]

(on preview: if that doesn't work, Superilla's method will.)
posted by madcaptenor at 10:45 AM on December 20, 2022 [1 favorite]

Building off of madcaptenor's instructions: If you want the entire row to be formatted as a certain color if the day number in column A is less than or equal to 15, you can do this too. Select all of the columns you want formatted that way, and then use the formula =DAY($A1)<=15 instead. Then when Excel applies the conditional formatting, it looks at the fixed column A rather than "updating" which column it's looking at based on the column of the cell it's formatting.
posted by Johnny Assay at 10:48 AM on December 20, 2022 [4 favorites]

Point of clarification.. is this column in date format? The suggestions above rely on this to be the case.
posted by oceano at 2:24 PM on December 20, 2022