Google表格-Arrayformula的夜间工作时间，Arrayformula的周末工作时间 [英] Google Sheets - night shift hours worked with Arrayformula, weekends hours worked with Arrayformula
问题描述
我有google文档列表: https://docs.google.com/spreadsheets/d/1gB-t47BKusiwVM53lZi1dGjaDcscILP4yCMtEdtaoao/edit?usp = sharing
我在哪里自动将短信从手机发送到桌子.我以前已经问过那里，如何解决一些问题也得到了很大的帮助，但是不幸的是，我再次陷入了另一个困境.
我有几个问题:
公式本身不能位于单元格中，而必须位于单元格的"Arrayformula"中.因为否则自动SMS会在收到消息后将公式推低，因此我在这里找到了有关此+索引和计数功能的建议.
现在开始讨论我的问题: 我需要在M列中有几个小时的低谷工作时间.夜班开始于22:00，结束于06:00.波纹管是桌子，波纹管是桌子，我要把我拥有的公式放进去.
| | D | F | G | I | J | M |
|----+-----------------+----------------+--------------+------------+-------------------+------------+
| 1> | Start work date | Start of work |End work date |End of work | Total hours worked| Night shift|
| 2> | 11.09.2020 | 16:00 | 12.09.2020 | 04:00 | | |
| 3> | 10.09.2020 | 07:00 | 12.09.2020 | 07:00 | | |
| | N |
|----+--------------------+
| 1> |Worked over weekend |
| 2> | |
| 3> | |
对于工作时间(J列)，我有这个公式，但是问题是它不会在24小时内出现. J3的总工作时间为48小时.
=ARRAYFORMULA({"Hours worked";--(DATEVALUE(G2:INDEX(G:G,Counta(D:D)))+TIMEVALUE(I2:INDEX(I:I,Counta(I:I))))-(DATEVALUE(D2:INDEX(D:D,Counta(D:D)))+TIMEVALUE(F2:INDEX(F:F,COUNTA(F:F))))})
那么我有这个，但是它不能正确计数(M列)夜间应该是22:00-06:00.
=(J2-(
If(F2<I2;
MAX(0;MIN(I2;TIMEVALUE("22:00"))-MAX(F2;TIMEVALUE("06:00")));
MAX(0;TIMEVALUE("22:00")-F2)+MAX(0;I2-TIMEVALUE("06:00"))
)
)*24)
M2应该是06:00. M3应该是16
我不太确定cells，ets的格式.它在上面的表格中链接.周末的营业时间是从星期六至星期日.我坚信如果条件允许的话我会这样做的，因为我有几周的时间可以这样做? N2的周末时间应为4小时.
对于任何反馈，我都会很高兴.
您遇到的基本问题是，您正在尝试使用文本，而不是立即将其转换为 numbers 跟踪时间和日期.我在工作表上新建了一个名为MK.help的标签，并将以下公式放在单元格M1中.
=ARRAYFORMULA({"night shift";IF(C2:C="";;IF((1-F2:F)*(I2:I<F2:F)>2/24;2/24*(I2:I<F2:F);(1-F2:F)*(I2:I<F2:F))+IF(I2:I*(I2:I<F2:F)>6/24;6/24*(I2:I<F2:F);I2:I*(I2:I<F2:F)))})
您会注意到，我还更改了您使用的所有其他公式，以从系统中提取时间和日期.您的公式过于夸张，它们似乎依赖大量文本，而不是数字操纵.
I have google docs list: https://docs.google.com/spreadsheets/d/1gB-t47BKusiwVM53lZi1dGjaDcscILP4yCMtEdtUTao/edit?usp=sharing
Where I get SMS texts from phone to the table automaticly. I've already asked there before, and got great help, how to solve some issues, but unfortunatelly I'm stucked there again with another fomulas.
I have couple of issues:
The formulas can't be in the cells themself, but has to be in the "Arrayformula" Because otherwise the automatic SMS push the formulas down when recieved, therefore I get there the advice of this + Index and count function.
So now to my Issues: I would need there in column M to be hours worked trough night. Night work starts at: 22:00 and end at 06:00. Bellow is the table and bellow the table I'll put the formulas that I have sofar.
| | D | F | G | I | J | M |
|----+-----------------+----------------+--------------+------------+-------------------+------------+
| 1> | Start work date | Start of work |End work date |End of work | Total hours worked| Night shift|
| 2> | 11.09.2020 | 16:00 | 12.09.2020 | 04:00 | | |
| 3> | 10.09.2020 | 07:00 | 12.09.2020 | 07:00 | | |
| | N |
|----+--------------------+
| 1> |Worked over weekend |
| 2> | |
| 3> | |
For the hours worked (column J) I have this formula, but the issues with it is that it won't come over 24 hours. The total for J3 should be 48 hours worked.
=ARRAYFORMULA({"Hours worked";--(DATEVALUE(G2:INDEX(G:G,Counta(D:D)))+TIMEVALUE(I2:INDEX(I:I,Counta(I:I))))-(DATEVALUE(D2:INDEX(D:D,Counta(D:D)))+TIMEVALUE(F2:INDEX(F:F,COUNTA(F:F))))})
Then I have this, but it won't count correctly ( column M) Night hours should be 22:00-06:00.
=(J2-(
If(F2<I2;
MAX(0;MIN(I2;TIMEVALUE("22:00"))-MAX(F2;TIMEVALUE("06:00")));
MAX(0;TIMEVALUE("22:00")-F2)+MAX(0;I2-TIMEVALUE("06:00"))
)
)*24)
M2 should be 06:00. M3 should be 16
I'm not really sure about the formating of celles, ets. It's in the table above linked. And the weekend hours are from Saturday - Sunday. I've tought that I would do it with if conditions, since I have in the table days of weeks if that would be possible? The weekend hours for N2 should be 4 hours.
For any feedback I would be really glad.
The fundamental problem you're having I think is that you're trying to work with text rather than converting immediately into numbers to keep track of time and dates. I made a new tab on your sheet called MK.help and put the following formula in cell M1.
=ARRAYFORMULA({"night shift";IF(C2:C="";;IF((1-F2:F)*(I2:I<F2:F)>2/24;2/24*(I2:I<F2:F);(1-F2:F)*(I2:I<F2:F))+IF(I2:I*(I2:I<F2:F)>6/24;6/24*(I2:I<F2:F);I2:I*(I2:I<F2:F)))})
You'll note that I changed all of the other formulas you were using as well to extract the times and dates from your system. Your formulas were overkill and they seemed to rely on a lot of text, rather than number manipulation.
这篇关于Google表格-Arrayformula的夜间工作时间，Arrayformula的周末工作时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
