Google表格中基于[date] + X的条件格式单元格 [英] Conditional format cells in Google Sheets based on [date]+X

问题描述

请在此处查看动态日历：

我现在需要使条件格式动态化，以便日期单元格中的颜色（01、02等）会根据到达日期和客人入住的夜数自动为单元格设置样式（浅红色）。每个日期有3个单元格，一个左，一个中和一个右。旅客抵达/离开时，这里有3个有助于识别事件的信息。展示所需效果的最佳方法是下图。

重要的是，在12月14日，关键转换，中心单元显示为深红色阴影。

我可以使用什么公式使颜色动态变化？

1. 必须考虑深红色的关键转换


2. 关键转换不存在的地方相关的，绿色应该是主要颜色。

我正在努力想出一种方法来计算客人从名单上停留的日期

解决方案

在具有多个合并的单元格的工作表上应用条件格式工作表是一个噩梦。

话虽如此，如果您愿意展示一些技巧，就可以完成工作。

假设您具有以下命名范围：

• 开始日期是查找表中的所有开始日期；


• End_Date 是所有结束日期（即 Start_Date +夜晚）；


• CCO 为所有关键变更日期，如果您已在表中按如下所示命名各列，则可以使用以下公式查找： = IF（MATCH （[@ [结束日期]]，[开始日期]，0）> 0，[@ [结束日期]]，）

然后按照步骤设置条件格式。 请注意，以下操作是在Excel中执行的，而不是在Google工作表中执行。

要设置关键变更日期的格式，您可以在工作表上突出显示以下范围： = $ U $ 5，$ U $ 12，$ U $ 19，$ U $ 26，$ U $ 33，$ U $ 40 ，然后使用以下公式作为格式设置规则：

  = MATCH（V5，CCO，0）> 0 
  

要突出显示日历上的日期，您可以突出显示工作表上的以下范围： = $ B $ 5：$ D $ 5，$ B $ 12：$ D $ 12，$ B $ 19：$ D $ 19，$ B $ 26：$ D $ 26，$ B $ 33：$ D $ 33，$ B $ 40：$ D $ 40 ，然后使用以下公式作为格式设置规则：

  = COUNTIFS（Start_Date，< =& $ D5，End_Date，> =& $ D5）> 0 
  

然后高亮显示 B5：D40 ，使用格式刷将格式复制并粘贴到以下范围一次一个范围 ： E5：G40 ， H5：J40 ， K 5：M40 ， N5：P5 ， Q5：S5

然后您需要在格式规则中为每个范围手动将 $ D5 替换为 $ G5 ， $ J5 ， $ M5 ， $ P5 ， $ S5 。

最后一天（星期六）的格式有些复杂，因为实际上，三个独立的列：T，U和V以及列T 和列V 的规则是不同的。

要设置列T 的格式，您需要突出显示以下范围： = $ T $ 5，$ T $ 12，$ T $ 19，$ T $ 26，$ T $ 33，$ T $ 40 ，然后使用以下公式作为格式设置规则：

  = COUNTIFS（Start_Date，< =& $ S5，End_Date，> =& $ S5）> 0 
  

要设置列V 的格式，您需要突出显示以下范围： = $ V $ 5，$ V $ 12，$ V $ 19，$ V $ 26，$ V $ 33，$ V $ 40 ，然后将以下内容用于穆拉作为格式化规则：

  = COUNTIFS（Start_Date，< =& $ V5，End_Date，> =& $ V5）> 0 
  

列U的格式先前已与关键转换日期相关地进行了解释。

在上面的演示中，我还添加了条件格式来对一周内的日期进行重要更改，而不仅仅是星期六。如果没有必要，您可以忽略。如果还希望进行设置，则可以借鉴为列U 设置格式的想法，突出显示C，F，I，L列中的相关单元格。 ，O和R一起，并使用相同的公式，但将 V5 替换为 D5 。

让我知道您是否有任何疑问。干杯：）

Please view the dynamic calendar here: Please copy and paste for testing

I am transforming a check in list of guests booked in a holiday property into a dynamic calendar. (The list/data is greyed out to the right of the calendar in the shared link above.)

The property is empty for some weeks hence the gaps between some check-in dates. Check-out is calculated from =[check-in]+[Nights]. Critical change overs (CCO) are calculated by the formula =IFERROR(IF(AND(VLOOKUP([Check out of departing guest],[Check In_range],1,0),LEN([Check in of departing guest])>0,LEN([Check in of arriving guest])>0),[Check in of departing guest],""),"")

The month and year can be changed (circled green below) to dynamically change guest names per arrival date.

Critical change overs have been highlighted dynamically in the calendar if the month is changed (circled dark red below).

I now need to make the conditional formatting dynamic so the colours in the date cells (01, 02 etc) automatically style cells if the property is occupied (light red) based upon the arrival date and number of nights the guest is staying for. Each date has 3 cells, one left, one centre and one right. There are 3 to help identify events when guests arrive/depart. The best way to illustrate the desired effect is from the graphic below.

Importantly, on 14th December, there is a critical change-over, and the center cell is shaded deep red.

What formula can I use to make the colours change dynamically?

1. Must account for critical change-overs in dark red
2. Where critical change-overs are not relevant, the green colour should be dominant.

I am struggling to think of a way to count dates when guests are staying from the list on the right hand side and style cells accordingly.

解决方案

Applying conditional formatting on a sheet with multiple merged cells sitting horizontally and vertically across the worksheet is a nightmare to start with.

Having that said, if you are willing to show some craftsmanship, you can get the job done.

Suppose you have the following named ranges:

• Start_Date being all the start dates in your look up table;
• End_Date being all the end dates (which is Start_Date + Nights) in your look up table;
• CCO being all the critical-change-over dates which can be found using the following formula if you have named the columns as shown below in a Table: =IF(MATCH([@[End Date]],[Start Date],0)>0,[@[End Date]],"")

Then follow the steps to set up the conditional formatting. Please note the following are performed in Excel but not google-sheets.

To set up the formatting for the critical-change-over date, you may highlight the following ranges on your worksheet: =$U$5,$U$12,$U$19,$U$26,$U$33,$U$40, then use the following formula as the formatting rule:

=MATCH(V5,CCO,0)>0

To highlight the days on the calendar, you may highlight the following ranges on your worksheet: =$B$5:$D$5,$B$12:$D$12,$B$19:$D$19,$B$26:$D$26,$B$33:$D$33,$B$40:$D$40, then use the following formula as the formatting rule:

=COUNTIFS(Start_Date,"<="&$D5,End_Date,">="&$D5)>0

Then you need to highlight B5:D40, use Format Painter to copy and paste the format to the following ranges one range at a time:E5:G40,H5:J40,K5:M40,N5:P5,Q5:S5

Then you need to manually replace $D5 within the formatting rule for each range with $G5,$J5,$M5,$P5,$S5.

The formatting for the last day (Saturday) is a little bit complicated as there are actually three separate columns: T, U and V and the rules for Column T and Column V are different.

To set up the formatting for Column T, you need to highlight the following ranges: =$T$5,$T$12,$T$19,$T$26,$T$33,$T$40, then use the following formula as the formatting rule:

=COUNTIFS(Start_Date,"<="&$S5,End_Date,">="&$S5)>0

To set up the formatting for Column V, you need to highlight the following ranges: =$V$5,$V$12,$V$19,$V$26,$V$33,$V$40, then use the following formula as the formatting rule:

=COUNTIFS(Start_Date,"<="&$V5,End_Date,">="&$V5)>0

The formatting for Column U has been explained previously in relation to critical-change-over dates.

In the above demonstration, I have also added the conditional formatting for critical change over dates within the week not just for Saturday. If that's not necessary you can ignore. If you also want to have that set up, you can borrow the idea of setting up the formatting for Column U, highlight the relevant cells in Column C, F, I, L, O, and R altogether, and use the same formula but replace V5 with D5.

Let me know if you have any questions. Cheers :)

这篇关于Google表格中基于[date] + X的条件格式单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！