Excel-日期/日期/时间以确定高峰/非高峰 [英] Excel - day/date/time to determine peak/offpeak

问题描述

我正在尝试从.csv文件输出（如下）确定日期/时间是否符合我们的高峰或非高峰时间标准。高峰/非高峰时间取决于星期几和时间。我正在尝试在excel中建立一个公式，让我知道每个日期/时间是高峰还是非高峰。我一直在使用文本到列功能将数据放入两个单元格中，然后使用文本功能确定新单元格中的星期几，然后使用过滤器功能输入峰值/非峰值。这是非常手动且效率低下的。

I'm trying to determine from a .csv file output (below) if the date/time meets our criteria for peak or off-peak. Peak/off-peak is determined by the day of the week and time. I'm trying to build a formula in excel that lets me know if each date/time is peak or off-peak. I have been using the text to column feature to put the data into two cells then use the TEXT function to determine day of the week in a new cell, then filter feature to input peak/off-peak. This is very manual and inefficient. Thanks in advance.

峰值等于：

- mon, tue, wed thurs, fri and hour 6:00-21:00

非高峰时段等于：

- sat, sun and hour 0:00-23:00 
- mon, tue, wed thurs, fri and hour 0:00-5:00 and 22:00-23:00 

文件输出：

8/15/2017 0:00
8/15/2017 1:00
8/15/2017 2:00
8/15/2017 3:00

寻求成果：

Date/Time       peak/off-peak
8/15/2017 0:00  off-peak
8/15/2017 1:00  off-peak
8/15/2017 2:00  off-peak
8/15/2017 3:00  off-peak
8/15/2017 4:00  off-peak
8/15/2017 5:00  off-peak
8/15/2017 6:00  peak
8/15/2017 7:00  peak
8/15/2017 8:00  peak
8/15/2017 9:00  peak
8/15/2017 10:00 peak
8/15/2017 11:00 peak
8/15/2017 12:00 peak
8/15/2017 13:00 peak
8/15/2017 14:00 peak
8/15/2017 15:00 peak
8/15/2017 16:00 peak
8/15/2017 17:00 peak
8/15/2017 18:00 peak
8/15/2017 19:00 peak
8/15/2017 20:00 peak
8/15/2017 21:00 peak
8/15/2017 22:00 off-peak
8/15/2017 23:00 off-peak

推荐答案

您可以编写B1单元格中的以下公式，其中A1是包含日期/时间的单元格：

You can write the following formula in the B1 cell, where A1 is the cell containing the Date/Time:

=IF(AND(WEEKDAY(A1,2)<=5,WEEKDAY(A1,2)>=1,A1-INT(A1)>=0.25,A1-INT(A1)<=0.875),"peak","off-peak")

部分 WEEKDAY（A1,2）<= 5 ，WEEKDAY（A1,2）> = 1 检查日期是否在星期一和星期五之间，而部分 A1-INT（A1）> = 0.25，A1-INT （A1）< = 0.875 检查小时是否在06:00和21:00之间。如果每个条件都为TRUE，则公式将显示 峰值，否则将显示 非峰值。

The part WEEKDAY(A1,2)<=5,WEEKDAY(A1,2)>=1 checks if the day is between Mon and Fri and the part A1-INT(A1)>=0.25,A1-INT(A1)<=0.875 checks if the hour is between 06:00 and 21:00. If every conditions are TRUE, the formula shows "peak" otherwise it shows "off-peak".

这篇关于Excel-日期/日期/时间以确定高峰/非高峰的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！