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!
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 😁
It worked for me! Thank you so much for your clear explanation and taking the time!
I had fun, glad it worked for ya!
I'm working on a compensation request form that follows these steps: An HR Business Partner completes the Compensation Request Smartsheet Form that then triggers an alert to our Compensation Manager. Our Compensation Manager will insert the requested information. The HR Business Partner will be alerted when the…
I have many many sheets where column names are the same but data types are different (ex: some "Assigned to" columns are text/numbers and some are contact list, some "Project Name" columns are text/numbers, and some are dropdown list.) The choices in value type are driven by the many teams specific needs. This creates…
Is it possible to format a singular cell in a sheet to total the above numbers add in each cell of the column.
Help shape the future of Smartsheet.
Share your ideas and feature requests.
©2023. All Rights Reserved Smartsheet Inc.