如何在Excel中将日期添加到列中，但每24次重复一次? [英] How can I add dates to column but repeat each 24 times, in Excel?

问题描述

这是我正在查看的数据中的一个示例.

Here is a sample from the data that I am looking at.

Hour Index  Visits
    0   67
    1   22
    2   111
    3   22
    4   0
    5   0
    6   22
    7   44
    8   0
    9   89
    10  22
    11  111
    12  44
    13  89
    14  44
    15  111
    16  177
    17  89
    18  44
    19  44
    20  89
    21  22
    22  89
    23  44
    24  133
    25  44
    26  22
    27  22
    28  44
    29  22
    30  44
    31  44
    32  22

我想做的是添加另一列，其中包含从星期一开始的日期，该日期重复24次，然后转到星期二(重复24次)，依此类推.因此结果应如下所示:

what I want to do is add another column that contains dates starting with Monday which is repeated 24 times then go to Tuesday (repeated 24 times) and so on. So the result should look like:

Hour Index  Visits  Day
0   67  MONDAY
1   22  MONDAY
2   111 MONDAY
3   22  MONDAY
4   0   MONDAY
5   0   MONDAY
6   22  MONDAY
7   44  MONDAY
8   0   MONDAY
9   89  MONDAY
10  22  MONDAY
11  111 MONDAY
12  44  MONDAY
13  89  MONDAY
14  44  MONDAY
15  111 MONDAY
16  177 MONDAY
17  89  MONDAY
18  44  MONDAY
19  44  MONDAY
20  89  MONDAY
21  22  MONDAY
22  89  MONDAY
23  44  MONDAY
24  133 TUESDAY
25  44  TUESDAY
26  22  TUESDAY
27  22  TUESDAY
28  44  TUESDAY
29  22  TUESDAY
30  44  TUESDAY
31  44  TUESDAY
32  22  TUESDAY

我知道如何增加日期，但不重复24次然后增加.有人可以告诉我如何使用Excel进行此操作吗?

I know how to get the dates to increment, but not repeat 24 times then increment. Can someone show me how to do this with Excel?

推荐答案

尝试使用此公式(我想您的小时"列从A2单元格开始):

try to use this formula (I suppose that your Hour column starts from A2 cell):

=TEXT(1+MOD(1+INT(A2/24),7),"dddd")

请注意，如果您的excel日期从01.01.1900开始(通常是PC上excel的默认值)，则该公式会很好地起作用. 如果您使用的是1904年日期系统，则应使用下一个公式:

Note, that formula works well if your excel dates starts from 01.01.1900 (which is usually default for excel on PC). If you are using 1904 date system, you should use next formula:

=TEXT(2+MOD(1+INT(A2/24),7),"dddd")

这篇关于如何在Excel中将日期添加到列中，但每24次重复一次?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！