Conditional Formatting by Month

ElaBee

I'm creating a sheet that turns cells red when an item is due for an employee. One item is due anytime during their birth month - how can I format the cell to turn red on the first day of their birth month? Thanks!

Josh Reed

Hi @ElaBee !

I'm fairly new to Smartsheet but I have a solve for you. Maybe someone more experienced will have a more eloquent and concise solution but mine will work in the absence of something better.

We will need three columns for this solve. I will explain my thought process and attach screenshots as well.

Column 1: Employee's DOB (MM/DD/YY)

Column 2: Today- This will be a date column and you will use a column formula to have this column populate today's date for each row. This column can be hidden as it is only used in the formula I will address in the third column. The formula for getting today's date in the cells is simply: =TODAY()

Column 3: A checkbox column I named "Is Today in Birthmonth?" This column can also be hidden once completed as it is only used to trigger the conditional formatting. The formula for this column will tell each cell to automatically check the box or not based on the following criteria: If the two leftmost characters in the DOB column match the two leftmost characters in the TODAY column, then check the box. The formula looks like this:

=IF(LEFT([Birthday (MM/DD/YY)]@row, 2) = LEFT(Today@row, 2), 1).

After these steps, if the month section of both of the columns match the box will be checked, then we build the conditional formatting off of the box being checked or not. The logic is very simple for this, but for reference here is a screenshot:

And this is what the end result should look like on the sheet:

And again, once you have verified that it is working properly, you can hide the two columns on the right (Today and Is Today In Birthmonth) since the column formula will work for any row that has data entered.

Hope this helps! There may be an easier way to do this but this would be my approach. Feel free to reach out with questions 😁

-Josh

Josh Reed

Hi @ElaBee !

I'm fairly new to Smartsheet but I have a solve for you. Maybe someone more experienced will have a more eloquent and concise solution but mine will work in the absence of something better.

We will need three columns for this solve. I will explain my thought process and attach screenshots as well.

Column 1: Employee's DOB (MM/DD/YY)

Column 2: Today- This will be a date column and you will use a column formula to have this column populate today's date for each row. This column can be hidden as it is only used in the formula I will address in the third column. The formula for getting today's date in the cells is simply: =TODAY()

Column 3: A checkbox column I named "Is Today in Birthmonth?" This column can also be hidden once completed as it is only used to trigger the conditional formatting. The formula for this column will tell each cell to automatically check the box or not based on the following criteria: If the two leftmost characters in the DOB column match the two leftmost characters in the TODAY column, then check the box. The formula looks like this:

=IF(LEFT([Birthday (MM/DD/YY)]@row, 2) = LEFT(Today@row, 2), 1).

After these steps, if the month section of both of the columns match the box will be checked, then we build the conditional formatting off of the box being checked or not. The logic is very simple for this, but for reference here is a screenshot:

And this is what the end result should look like on the sheet:

And again, once you have verified that it is working properly, you can hide the two columns on the right (Today and Is Today In Birthmonth) since the column formula will work for any row that has data entered.

Hope this helps! There may be an easier way to do this but this would be my approach. Feel free to reach out with questions 😁

-Josh

ElaBee

Hi Josh-

It worked for me! Thank you so much for your clear explanation and taking the time!

Josh Reed

Hey, anytime!

I had fun, glad it worked for ya!