I received a question from a customer, "How do I change the color of the task bar on Saturdays and Sundays?"
Basically,Conditional formatting features (Conditional Formatting) will be used to change the color of the taskbar in Gantt view and calendar view.How to specify bar colorIf you don't know, please learn how to set the taskbar color conditionally in this tutorial article.
In addition, since Saturdays, Sundays, and Daan are the conditions here, we will also explain how to determine Saturdays, Sundays, and Daan using functions.
How to specify taskbar color in conditional formatting
Below is the conditional formatting screen.
First, click the "Task Bar" at the bottom and select the color you want to display when the specified condition is met.
Second, specify to display all rows.
As a result, Saturdays and Sundays are displayed on the taskbar in the specified color, although it is slightly visible on the right.
Please refer tovideoThere is an explanation after about XNUMX minutes, so please take a look at that as well.
Judgment on Saturdays and Sundays
WEEKDAY() function
First of all, the judgment of Saturday and Sunday is as follows,WEEKDAY( )Get the WEEK number from a date column using a number.
(In this case, Start is used as the date column.
=WEEKDAY(Start@row)
Next, prepare the day of the week so that it can be displayed with the day of the week attached to the task name as shown below.
task name with days
In addition, the task name with the day of the week is created by the formula like the lower right.
Specify as an item to be displayed on the right side of the taskbar
To display the task name with the day of the week created above on the taskbar (actually, there is also a Daan display, so the column name is Task Daya), specify the column in the display label from the project settings .
Converting day numbers to days of the week
Use the INDEX( ) & MATCH( ) or VLOOKUP( ) function to convert the WEEK number to the day of the week based on the separately prepared table below.
=INDEX({day of the week}, MATCH(Weekday@row, {Weekday Number}, 0), 1)
In the above formula, the INDEX( ) function refers to the day column of the "Weekday to Day of Week sheet" as {{day of the week}}, and the MATCH( ) function, based on the WEEK number calculated above, Get the corresponding number of rows by {{Weekday Number}}, which refers to the Weekday column of the "Weekday to Day of Week sheet" earlier, and use the INDEX() function to determine which row of the week to bring. specified.
Daan Judgment and Daan Display in Taan Row
Taian is considered an auspicious day for business, and even customers who have asked questions choose Taian when delivering expensive products.
Taian appears every six days in the lunar calendar called "Rokuyo", which originated in China.Therefore, a day with a remainder of XNUMX when dividing (the date you want to check - the reference date) by XNUMX is judged as a great low.
I think there are various ways to do this, but here we record the first Butsumetsu day of the year (the day after Butsumetsu is the day after Butsumetsu) in the Sheet Summary, and use the NETDAYS( ) function to calculate how many days there are from that day to the start date. Then, the rokuyo (number) is obtained, and the result is divided by XNUMX and the remainder (in this case, XNUMX) is used to determine the taian.
(Because it is not enough to make a table here,=IF(rokuyou@row = 1, “daan”, “”) to display the Daan. )
Summary
This time, from the questions from customers, we understood that there is a high business need to display the day of the week and the day of the week on the Kant chart and calendar.
As a technology of Smartsheet, conditional formatting, among them, specifying the color of the taskbar, furthermore, using the WEEKDAY( ) function, etc. to find the day of the week and the day of the day.
At first, I thought I should use the DAY( ) function to determine which day of the month the start date is, and use MOD( ) to find the remainder of XNUMX. I took a method like this.
In addition, it returns from XNUMX on New Year's Day to XNUMX on New Year's Eve.YEARDAY( date )I think there is also a way to use the function