Google Sheets - night shift hours worked with Arrayformula, weekends hours worked with Arrayformula

0
votes

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.

2
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Your locale? In file spreadsheet settings? - TheMaster
Hi - sorry don't understand that. You mean language? I've changed it to US. The formulas I've tried to translate, hopefully correct. - Patrik Gremlica
Localse. File> Spreadsheet settings > The first oneb location: "Denmark" or "US". Or if you changed it to US, Edit your question to provide dates correctly. For eg, 11.09.2020 is not a valid date in US, but it is valid in some European/russian countries. Alternatively, provide date of format yyyy-mm-dd ( It's universally accepted ) - TheMaster
the sample sheet is not accessible. - MattKing
Hi, thank you for the reply. It should be accesible now: docs.google.com/spreadsheets/d/… - Patrik Gremlica

2 Answers

3
votes

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.

3
votes

M1:

={"Night shift";ARRAY_CONSTRAIN(ARRAYFORMULA(IF(G2:G-D2:D>=1,IF(--F2:F>--"22:00",1-F2:F,"2:00")+IF(--I2:I<--"6:00",I2:I,"6:00")+(G2:G-D2:D-1)*"8:00")),COUNTA(D:D)-1,1)}

Explanation:

={"Night shift";                                             
    ARRAY_CONSTRAIN(                      //constrain output                   
    ARRAYFORMULA(                         //enforce array context                   
      IF(G2:G-D2:D>=1,                    //if end work date > start                   
        IF(--F2:F>--"22:00",1-F2:F,"2:00")//if start time >22, get hours left else 2 hours                   
        +IF(--I2:I<--"6:00",I2:I,"6:00")  //if end time < 6 use hours worked else cap it                   
        +(G2:G-D2:D-1)*"8:00"             //8 hours for full days                   
      )                                                      
    )                                                        
    ,COUNTA(D:D)-1,1)                     //constrain rows to count                    
}

Notes:

  • INDEX/COUNTA is still preferred but I've used ARRAY_CONSTRAIN instead to limit formula size.
  • You should format the resulting column as "Duration"
  • All date time columns should be formatted and recognised as date and time