Calculate "night shift" hours

[Dialect_Junk]

Hello. I have a timecard app that captures hours worked via date/timestamp columns - "Clock In" & "Clock Out."

We recently implemented "night shift" pay where if an employee works any hours between 8pm and 4am they receive upgrade pay for those hours.

For example, if an employee works from 7am - 9pm, they'd be entitled to 1 hour of upgrade pay.  Work 8pm - 6am, they'd be entitled to 8 hours of upgrade pay.

I'm trying to figure out a way for the app to automatically calculate these night shift hours. I've looked through forum and documentation and can't quite get a handle on a starting point.

Any guidance is appreciated. Thanks!

[Joseph_Seddik]

Is "upgrade pay" an incremental or absolute amount?

[Dialect_Junk]

"Upgrade Pay" is an absolute amount. 

[Joseph_Seddik]

"Clock In" & "Clock Out" columns should be of DateTime type. 

[normalPay] * HOUR([Clock Out] - [Clock In])
+
IF( OR(20 <= HOUR([Clock In]), HOUR([Clock In]) < 4),
  IF( OR(20 < HOUR([Clock Out]), HOUR([Clock Out]) <= 4),
    HOUR([Clock Out] - [Clock In]) * ([upgradePay] - [normalPay]),
    IF( HOUR([Clock In]) < 4,
      (4 - HOUR([Clock In])) * ([upgradePay] - [normalPay]),
      (4 - HOUR([Clock In]) + 24) * ([upgradePay] - [normalPay])
    )
  ), 
  IF( OR(20 < HOUR([Clock Out]), HOUR([Clock Out]) <= 4),
    IF( HOUR([Clock Out]) <= 4,
      (4 + HOUR([Clock Out])) * ([upgradePay] - [normalPay]),
      (HOUR([Clock Out]) - 20) * ([upgradePay] - [normalPay])
    ),
    0
  )
)

[Dialect_Junk]

Thank you! I will give this a try. 

[Dialect_Junk]

Joseph, thanks for the time you put into this. I'm trying to modify this expression to only calculate night shift hours, not taking any pay into account. Ideally it would look something like this:

Clock In: 4/26/2022 19:00:00
Clock Out: 4/27/2022 5:00:00
Regular hours: 10
Night Shift Hours (between 8pm-4am): 2

Thanks for the help!

[1minManager]

If @Joseph_Seddik option doesn't work then message me and I'll try to help.  As I've done this before in a few Apps.

Simon@1minManager.com

[Joseph_Seddik]

Total hours:

HOUR([Clock Out] - [Clock In])

Night Shift hours:

IF( OR(20 <= HOUR([Clock In]), HOUR([Clock In]) < 4),
  IF( OR(20 < HOUR([Clock Out]), HOUR([Clock Out]) <= 4),
    HOUR([Clock Out] - [Clock In]),
    IF( HOUR([Clock In]) < 4,
      4 - HOUR([Clock In]),
      4 - HOUR([Clock In]) + 24
    )
  ), 
  IF( OR(20 < HOUR([Clock Out]), HOUR([Clock Out]) <= 4),
    IF( HOUR([Clock Out]) <= 4,
      4 + HOUR([Clock Out]),
      HOUR([Clock Out]) - 20
    ),
    0
  )
)

Regular hours: The difference between the two.

[Dialect_Junk]

I'm getting the following error message: "Parameter 1 of function HOUR is of the wrong type."

I looked into the documentation for HOUR() and found it's a common problem:

"The argument must be a Duration value, not a DateTime or Time value. To fix, convert a DateTime value to a Time value with TIME(), then subtract a zero time to convert a Time value to a Duration value: HOUR(TIME(NOW()) - "00:00:00")."

I'll have to tinker with this some more. Thanks for all the help!

[Joseph_Seddik · Accepted solution]

Oh yes, I overlooked this! However, don't convert with TIME() indiscriminately, and you have to leave both values as DateTime.

Instead do this:

This expression, with subtraction, should be left as is:

HOUR([Clock Out] - [Clock In])

Replace every HOUR([Clock In]) with:

HOUR([Clock In] -  DATE([Clock In]))

Similarly for [Clock OUT].

That would be sufficient. 

[Dialect_Junk]

Sorry for the delayed response. I've been working the aforementioned night shifts and am finally back on days. Phew!

I tried the suggested changes and they worked great! Thank you!

The only wrinkle is that the resulting number is being rounded. We were hoping for more accuracy. This seems like it would add an altogether higher level of complexity(?)

Thanks, again, for the help!

[Joseph_Seddik]

It is not rounded, rather only whole hours are counted. 

Fortunately it is easy to include fractions of an hour in the calculation. You'd just have to replace HOUR() with TOTALHOURS().

[Dialect_Junk]

😲Amazing. Thank you!!

[Fabisbv]

muchas gracias, lo resolviste!!

[Joseph_Seddik]

Thanks also to the wonderful team behind the amazing AppSheet 🙂