分配日期是否为“营业时间"的公式或“非营业时间"在Excel中 [英] Formula to Assign whether a date is "In Business Hours" or "Outside Business Hours" in excel

问题描述

我正在尝试创建一个公式，以根据通话的日期和时间确定在营业时间之内还是之外拨打电话

I'm trying to create a formula to determine whether a phone call was made inside or outside of business hours based on the day and time of the call

营业时间为星期一至星期五上午8点至晚上8点，以及星期六上午8点至下午2点.

Business hours are Monday to Friday 8AM to 8PM and Saturday 8AM to 2PM.

基本上，我需要为其创建公式的是

So essentially what I need to create the formula for is;

如果致电时间是星期一，星期二，星期三，星期四，星期五，并且时间是> 8且< 20，或者呼叫是周六并且时间是> 8且< 14，则显示内部工作时间"

if call is Monday, Tuesday, Wednesday, Thursday, Friday and time is >8 and <20 or call is Saturday and time is >8 and <14 then show "Inside Business Hours"

对于其他所有内容，请显示外部工作时间"

For everything else show "Outside Business Hours"

谢谢

推荐答案

如果日期在单元格A2中开始，请在单元格B2中输入此公式并将其拖到底部.您的日期应采用9/16/2017 1:05:00 PM格式.

If you dates are starting in cell A2 , please enter this formula in cell B2 and drag it to the bottom. your dates should be in this format 9/16/2017 1:05:00 PM.

=IF(WEEKDAY(A2,2)=7,"Outside of Business hours",IF(WEEKDAY(A2,2)=6,IF(AND(MOD(A2,1)<TIME(14,0,0),MOD(A2,1)>TIME(8,0,0)),"Inside Business Hours","Outside of Business Hours"),IF(AND(MOD(A2,1)<TIME(20,0,0),MOD(A2,1)>TIME(8,0,0)),"Inside Business Hours","Outside of Business Hours")))

这篇关于分配日期是否为“营业时间"的公式或“非营业时间"在Excel中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！