excel日期相减去除周末_在Excel中突出显示周末日期

excel日期相减去除周末

Yes, the weekend is over, but another one is just five days away! To make it easier to keep track of Saturdays and Sundays, you can use conditional formatting to highlight weekend dates in Excel.

是的，周末结束了，但是又只有五天了！ 为了更轻松地跟踪周六和周日，可以使用条件格式在Excel中突出显示周末日期。

In this example, the Excel file has a list of product sales for the first half of July. We'd like to highlight the dates and sales numbers that fall on a weekend.

在此示例中，Excel文件具有7月上半月产品销售的列表。 我们想突出显示一个周末的日期和销售数字。

添加条件格式 (Add Conditional Formatting)

To highlight the weekend sales:

突出周末销售情况：

1. 1. Select all the cells where conditional formatting should be applied, cells H2:J16 in this example. Cell H2 is the active cell in the selection.
      选择应应用条件格式的所有单元格，在此示例中为单元格H2：J16。 单元格H2是所选内容中的活动单元格。

1. 1. On the Ribbon, click the Home tab, then click Conditional Formatting.
      在功能区上，单击“主页”选项卡，然后单击“条件格式”。
   2. Click New Rule, to open the New Formatting Rule dialog box
      单击“新建规则”，以打开“新建格式规则”对话框。

   3. In the Select a Rule Type list, click Use a formula to determine which cells to format.

      在“选择规则类型”列表中，单击“ 使用公式来确定要格式化的单元格” 。

   4. In the Formula box, enter a formula to check the weekday of the date in row 2, which is the active row on the worksheet:

      在“公式”框中，输入公式以检查第2行(工作表上的活动行)中日期的星期几：

      =WEEKDAY($H2,2)>5

      =星期((H2,2)> 5

1. Click the Format button, and select a Fill colour, or other formatting options, then click OK.
   单击格式按钮，然后选择填充色或其他格式设置选项，然后单击确定。
2. Click OK to close the New Formatting Rule dialog box.
   单击“确定”关闭“新建格式规则”对话框。

The weekend rows are highlighted in green.

周末行以绿色突出显示。

WEEKDAY函数 (The WEEKDAY Function)

The WEEKDAY function checks the date in column H, and returns a weekday number, based on a numbering system (2) that starts with Monday as 1. Saturday is 6 and Sunday is 7, so we want to highlight weekday numbers that are greater than 5.

WEEKDAY函数检查H列中的日期，并基于以星期一为1的编号系统(2)返回工作日编号。星期六为6，星期日为7，因此我们要突出显示大于5，

Instead of that numbering system, we could have entered 1 or 3 as the second argument (return_type) in the WEEKDAY function.

代替该编号系统，我们可以在WEEKDAY函数中输入1或3作为第二个参数(return_type)。

If we entered a 1, or omitted the second argument, the numbering would start with Sunday as 1. With a 3, Monday would start as 0, and end with a 6 on Sunday.

如果我们输入1，或省略第二个参数，则编号将以周日开头为1。以3开头，星期一将从0开始，周日以6结尾。

I used the 2 because that groups Saturday and Sunday at the end of the numbering, and we can simply test for >5.

我使用2是因为在编号的末尾将星期六和星期日分组，所以我们可以简单地测试> 5。

有关条件格式的更多信息 (More About Conditional Formatting)

On the Contextures website there are basic instructions for conditional formatting, and other examples of ways you can use it.

在Contextures网站上，有关于条件格式设置的基本说明，以及使用方式的其他示例。

观看视频 (Watch the Video)

Here's a short video that shows the steps in Excel 2007.

这是一个简短的视频，显示了Excel 2007中的步骤。

演示地址

翻译自: https://contexturesblog.com/archives/2009/06/15/highlight-weekend-dates-in-excel/

excel日期相减去除周末