How to find hours in any period

userdetails

Hi All,

I need to calculate the MAX of the blank entries in a timecard for any 24 hour period. For example:

Start counting the first row from 00, then it has 12 empty fields under each hour column, and results with 12 hours of non-work..

Next step is to check how many hours of non-work is present starting from first row's 01 column to next day's 01 column.

and goes on like this until the check happens for a 24 hour period. I need the max of non-work hours in any 24 hour period. Spent lot of time but no luck yet and will appreciate any help.

jmyzk_cloudsmart_jp

Hi @userdetails

I first added two helper columns to represent a 48-hour work/non-work pattern with the following formula;

[24 H Pattern] =JOIN([00]@row:[23]@row, ",")

[48 H Pattern] =[24 H Pattern]@row + INDEX([24 H Pattern]:[24 H Pattern], Day@row + 1)

Then, in the Sheet Summary field, [CommaPad], I added commas like ",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,".

https://app.smartsheet.com/b/publish?EQBCT=b7b4d6c6f2c142cb803e6e2baeb331bc

Using the [CommaPad], we can create ",,,,,,,,,," strings of desired length with the LEFT function.

By checking, for example, ",,,,,,,,,,,,,," or 14 commas are contained in the 48 H pattern, if the 14 hours are the max of non-work hours. We do this from the longest max of non-work hours, or 24 hours, down to 6 hours, with the following formula;

[Max of non-work hours] 
=IF(ISBLANK(INDEX([24 H Pattern]:[24 H Pattern], Day@row + 1)), "Needs Next Day Data", 
  IF(CONTAINS(LEFT(CommaPad#, 24), [48 H Pattern]@row), 24, 
  IF(CONTAINS(LEFT(CommaPad#, 23), [48 H Pattern]@row), 23, 
  IF(CONTAINS(LEFT(CommaPad#, 22), [48 H Pattern]@row), 22, 
  IF(CONTAINS(LEFT(CommaPad#, 21), [48 H Pattern]@row), 21, 
  IF(CONTAINS(LEFT(CommaPad#, 20), [48 H Pattern]@row), 20, 
  IF(CONTAINS(LEFT(CommaPad#, 19), [48 H Pattern]@row), 19, 
  IF(CONTAINS(LEFT(CommaPad#, 18), [48 H Pattern]@row), 18, 
  IF(CONTAINS(LEFT(CommaPad#, 17), [48 H Pattern]@row), 17, 
  IF(CONTAINS(LEFT(CommaPad#, 16), [48 H Pattern]@row), 16, 
  IF(CONTAINS(LEFT(CommaPad#, 15), [48 H Pattern]@row), 15, 
  IF(CONTAINS(LEFT(CommaPad#, 14), [48 H Pattern]@row), 14, 
  IF(CONTAINS(LEFT(CommaPad#, 13), [48 H Pattern]@row), 13, 
  IF(CONTAINS(LEFT(CommaPad#, 12), [48 H Pattern]@row), 12, 
  IF(CONTAINS(LEFT(CommaPad#, 11), [48 H Pattern]@row), 11, 
  IF(CONTAINS(LEFT(CommaPad#, 10), [48 H Pattern]@row), 10, 
  IF(CONTAINS(LEFT(CommaPad#, 9), [48 H Pattern]@row), 9, 
  IF(CONTAINS(LEFT(CommaPad#, 8), [48 H Pattern]@row), 8, 
  IF(CONTAINS(LEFT(CommaPad#, 7), [48 H Pattern]@row), 7, 
  IF(CONTAINS(LEFT(CommaPad#, 6), [48 H Pattern]@row), 6, 0))))))))))))))))))))