如何在员工工作时间中添加单位后缀? [英] How do I add a unit suffix to the hours an employee has worked?

问题描述

我正在寻找一个计算小时数并制定轮班计划的公式 我的想法是让经理，房屋的正面和背面有自己的计划者. 当我尝试计算每个工作人员的工作时间时，我会跌倒，但在打印时，也让读者看得清清楚楚

I'm looking for a formula to count hours and set up a shift plan My idea is for managers, front of house and back of house to have their own planner. Where I fall at is when I try to count hours for each staff member BUT also make it legible to the reader when printed

例如 工作人员4 0830-1600，星期一休息一小时 1200-1900，星期二不休息 我希望它看起来像0830-1600，但从每天的时间中算起整个星期的总小时数

For example Staff member 4 does 0830-1600 with an hour break on Monday 1200-1900 with no break on Tuesday I want it to look like 0830-1600, but in the total hours to count out the whole week from the times put in each day

推荐答案

我认为您需要重新设计日历. Excel喜欢将原始数据拆分为单独的行，因此让我们开始吧.

I think you need to re-design your calendar. Excel loves raw data to be split out onto separate rows so let's start with that.

这是我的最终目标-这是您的简化版本

This is my end goal - it's a simplified version of yours

---

因此，让我们列出将我们带到那里的数据- 成行 .对于此示例，将数据粘贴到单元格A6:

So let's set out the data that will get us there - in rows. For this example, paste the data into cell A6:

Name    Day      Time In  Time Out  Total
Name 1  09/01/17    08:00   18:00   10:00
Name 1  12/01/17    11:00   15:00   04:00
Name 1  13/01/17    12:00   21:00   09:00
Name 2  10/01/17    08:00   13:00   05:00
Name 2  11/01/17    12:00   20:00   08:00
Name 2  13/01/17    10:00   14:00   04:00

然后在末尾添加另外几列，这将有助于获得总计并提高表格的可读性

And then add another few columns onto the end which will be useful in getting totals and making the table more readable

唯一ID 只需将名称和日期连接起来即可:=A7&B7.而 Time String 使用TEXT函数

Unique ID is created simply by concatenating name and day : =A7&B7. And the Time String uses the TEXT function

=UPPER(TEXT(C7,"hAM/PM")&"/"&TEXT(D7,"hAM/PM"))

---

在A1:F3中创建表格(如下所示).无需手动轮班数据，但可以将您的人员和日期标题放在:

Create your table in A1:F3 (like below). No need for manual shift data but do put your people and dates headings in:

在单元格B2中使用此简单公式，向下拖动以填充班次数据

Use this simple formula in cell B2 and drag down and across to populate shift data

=IFERROR(VLOOKUP($A2&B$1,$F$6:$G$12,2,0),"OFF")

类似地，您可以对total hours

=SUMIF($A$7:$A$12,$A2,$E$7:$E$12)*24

---

最终结果如下，可以方便地为更多的人/约会等操作.

The final result is below, and can be easily manipulated for more people/ dates, etc.

如果您对这些公式/技术有疑问-一定要问

这篇关于如何在员工工作时间中添加单位后缀?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！