excel统计连续工作天数_使用Excel计算事件发生前的天数

excel统计连续工作天数

( )

Excel treats dates as integers. This means you can add and subtract them, which can be useful for telling you how many days there are until that next deadline or event of yours. In this article, we will use Excel’s DATE, YEAR, MONTH, DAY, and TODAY functions to show you how to calculate the number of days until your next birthday or any other annual event.

Excel将日期视为整数。 这意味着您可以添加和减去它们，这对告诉您下一个截止日期或事件有多少天很有用。 在本文中，我们将使用Excel的DATE，YEAR，MONTH，DAY和TODAY函数向您展示如何计算直到下一个生日或任何其他年度事件的天数。

Excel stores dates as integers. By default, Excel uses “1” to represent 01/01/1900 and each day after that is one greater. Type in 01/01/2000 and switch the format to “Number” and you’ll see “36526” appear. If you subtract 1 from 36526, you can see that there were 36525 days in the 20th century. Alternatively, you could enter a future date and subtract the result of the TODAY function to see how many days away that date is from today.

Excel将日期存储为整数。 默认情况下，Excel使用“ 1”代表01/01/1900，此后的每一天都大于一。 输入01/01/2000并将格式切换为“数字”，您将看到“ 36526”出现。 如果从36526减去1，则可以看到20世纪有36525天。 或者，您可以输入将来的日期，并减去TODAY函数的结果，以查看该日期距离今天还有多少天。

日期相关功能的快速摘要 (A Quick Summary of Date-Related Functions)

Before we dive into some examples, we need to go over several simple date-related functions, including Excel’s TODAY, DATE, YEAR, MONTH, and DAY functions.

在深入研究示例之前，我们需要遍历几个简单的与日期相关的函数，包括Excel的TODAY，DATE，YEAR，MONTH和DAY函数。

今天 (TODAY)

Syntax: =TODAY()

语法：= TODAY()

Result: The current date

结果：当前日期

日期 (DATE)

Syntax: =DATE(year,month,day)

语法：= DATE(年，月，日)

Result: The date designated by the year, month, and day entered

结果：输入的年，月和日指定的日期

年 (YEAR)

Syntax: =YEAR(date)

语法：= YEAR(date)

Result: The year of the date entered

结果：输入日期的年份

月 (MONTH)

Syntax: =MONTH(date)

语法：= MONTH(日期)

Result: The numerical month of the date entered (1 through 12)

结果：输入日期的数字月份(1到12)

天 (DAY)

Syntax: =DAY(date)

语法：= DAY(日期)

Result: The day of the month of the date entered

结果：输入日期的月份中的某天

一些示例计算 (Some Example Calculations)

We will look at three events that occur annually on the same day, calculate the date of their next occurrence, and determine the number of days between now and their next occurrence.

我们将查看每年在同一天发生的三个事件，计算其下一次发生的日期，并确定从现在到下一次发生之间的天数。

Here is our sample data. We’ve got four columns set up: Event, Date, Next_Occurrence, and Days_Until_Next. We have entered the dates of a random birth date, the date taxes are due in the U.S., and Halloween. Dates like birthdays, anniversaries, and some holidays occur on specific days each year and work well with this example. Other holidays—like Thanksgiving—occur on a particular weekday in a specific month; this example does not cover those types of events.

这是我们的样本数据。 我们设置了四列：“事件”，“日期”，“下一次发生”和“天_未过帐_下一步”。 我们输入了一个随机生日的日期，美国的应缴税日期和万圣节。 诸如生日，周年纪念日和某些假期之类的日期发生在每年的特定日期，因此在此示例中效果很好。 其他假期(例如感恩节)发生在特定月份的特定工作日； 本示例未涵盖这些类型的事件。

There are two options for filling in the ‘Next_Occurrence’ column. You can hand-enter each date, but each entry will need to be manually updated in the future as the date passes. Instead, let’s write an ‘IF’ statement formula so that Excel can do the work for you.

可以在“ Next_Occurrence”栏中填写两个选项。 您可以手动输入每个日期，但是将来随着日期的过去，每个条目都需要手动更新。 相反，让我们编写一个“ IF”语句公式，以便Excel可以为您完成工作。

Let’s look at the birthday. We already know the month =MONTH(F3)  and day =DAY(F3) of the next occurrence. That’s easy, but what about the year? We need Excel to know whether the birthday has occurred in this year already or not. First, we need to calculate the date on which the birthday occurs in the present year using this formula:

让我们看看生日。 我们已经知道下一次出现的月份=MONTH(F3)和日期=DAY(F3) 。 那很容易，但是那一年呢？ 我们需要Excel知道生日是否已经在今年发生。 首先，我们需要使用以下公式计算今年生日的日期：

=DATE(YEAR(TODAY()),MONTH(F3),DAY(F3))

Next, we need to know if that date has already passed and you can compare that result to TODAY() to find out. If it is July and the birthday occurs every September, then the next occurrence is in the current year, shown with =YEAR(TODAY()) .  If it is December and the birthday occurs every May, then the next occurrence is in the next year, so =YEAR(TODAY())+1 would give the next year. To determine which to use, we can use an ‘IF’ statement:

接下来，我们需要知道该日期是否已经过去，您可以将该结果与TODAY()进行比较以找出答案。 如果是7月，并且生日在每年的9月发生，则下一次发生在当年，用=YEAR(TODAY()) 。 如果是12月，并且生日在每个5月发生，那么下一次发生在下一年，因此=YEAR(TODAY())+1将给出下一年。 要确定使用哪个，我们可以使用“ IF”语句：

=IF(DATE(YEAR(TODAY()),MONTH(F3),DAY(F3))>=TODAY(),YEAR(TODAY()),YEAR(TODAY())+1)

Now we can combine the results of the IF statement with the MONTH and DAY of the birthday to determine the next occurrence. Enter this formula into cell G3:

现在，我们可以将IF语句的结果与生日的MONTH和DAY结合起来以确定下一次出现。 在单元格G3中输入以下公式：

=DATE(IF(DATE(YEAR(TODAY()),MONTH(F3),DAY(F3))>=TODAY(),YEAR(TODAY()),YEAR(TODAY())+1),MONTH(F3),DAY(F3))

Hit Enter to see the result. (This article was written in late January 2019, so the dates will be…well…dated.)

按下Enter键即可查看结果。 (本文写于2019年1月下旬，因此日期将是...好吧...日期。)

Fill this formula down into the cells below by highlighting the cells and pressing Ctrl+D.

通过突出显示单元格并按Ctrl + D，将公式向下填充到下面的单元格中。

Now we can easily determine the number of days until the next occurrence by subtracting the result of the TODAY() function from the Next_Occurrence results we just calculated. Enter the following formula into cell H3:

现在，我们可以从刚刚计算的Next_Occurrence结果中减去TODAY()函数的结果，轻松确定直到下一次出现的天数。 在单元格H3中输入以下公式：

=G3-TODAY()

Press Enter to see the result and then fill this formula down into the cells below by highlighting the cells and pressing Ctrl+D.

按Enter查看结果，然后通过突出显示单元格并按Ctrl + D将该公式向下填充到下面的单元格中。

You can save a workbook with the formulas in this example to keep track of whose birthday is coming up next or know how many days you have left to finish your Halloween costume. Each time you use the workbook, it will recalculate the results based on the current date because you’ve used the TODAY() function.

您可以在此示例中保存带有公式的工作簿，以跟踪下一个生日即将到来或知道您还剩下多少天来完成万圣节服装。 每次使用工作簿时，由于使用了TODAY()函数，它将根据当前日期重新计算结果。

And yes, these are pretty specific examples that might or might not be useful to you. But, they also serve to illustrate the kinds of things you can do with date-related functions in Excel.

是的，这些都是非常具体的示例，可能对您有用也可能没有用。 但是，它们还可以用来说明您可以使用Excel中与日期相关的功能执行的操作。

翻译自: https://www.howtogeek.com/403419/use-excel-to-calculate-how-many-more-days-there-are-until-your-next-birthday/

excel统计连续工作天数