英文:
Excel: Sum different types of shifts
问题
我不确定这是否容易实现，但任何帮助都会有所帮助。我有一列数据，格式为xx:xx-xx:xx，早班通常是07:00-16:00，晚班是12:00-20:00。有一些变化，例如早班可能会晚30分钟到1小时结束，晚班可能会早30分钟到1小时开始。
我想自动化这个过程，使得在这一列下面有一个单元格告诉我早班和晚班的总人数。我已经使用了TIMEVALUE将这个文本值转换为数值，接下来应该怎么做？我尝试使用IF函数，但似乎不适用于我的方法。
我尝试使用TIMEVALUE(RIGHT(cell,5))来获取结束时间，但不确定如何根据条件递增单元格的值。
示例：
星期一
员工1 07:00-16:00
员工2
员工3 07:00-16:00
员工4 12:00-20:00
员工5 12:00-20:00
员工6
如上所示，有些人上早班，有些人上晚班，有些人不工作。是否有一种方法，可以在这一列下方有两个单元格，一个显示早班总人数，另一个显示晚班总人数，而无需手动输入？
英文:
I'm not sure if this is easily possible but any help will do. I have a column with different shifts in the format xx:xx-xx:xx. Morning shifts are normally 07:00-16:00 and evenings are 12:00-20:00. There are variations, like morning finishing 30 minutes to an hour later and evenings starting 30 minutes to an hour earlier.
I want to automate it so under the column there is a cell which tells me the total number of people on a morning shift and evening shift. I've used TIMEVALUE to convert this TEXT value to a numeric value, what would be my next steps? I've tried working with IF's but it doesn't work with how I do them.
Tried using TIMEVALUE(RIGHT(cell,5)) to get the finishing times but not sure how to increase the value of the cell with each truth.
Example:
Monday
Employee1 07:00-16:00
Employee2
Employee3 07:00-16:00
Employee4 12:00-20:00
Employee5 12:00-20:00
Employee6
As shown, some will be on morning shifts and some on evenings and some won't be working. Is there a way to have 2 cells below this column, one which has the total number of morning shift and one with total number of evening without me having to manually enter them?
答案1
得分: 1
以下是要翻译的内容：
所以，快速尝试只是为了得到早上或晚上：
您可以添加以处理早期启动。
VLOOKUP方法是另一种选择。然后，您可以使用INDEX()与MATCH()来获取名称等。
所以，进一步更新：
英文:
So, a quick attempt just to get Morning or Evening:
You can add to deal with the early starts.
The vlookup method is another option. Then you can use index() with match() to get names etc
So, further update:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论