Excel - Function that zeroes out a specific time period

Žunec Marcel 0
Viewed 31

so I am having this problem at work, I'm not that great with excel so I thought maybe someone could help me here.

enter image description here

You can see the table on this picture. What I need to do is: if the time (column C) was between 21:00 and 3:00, the value in that G column has to be 0 and should be added to the number that's 144 rows below it. Otherwise, if the time was between 7:00 and 21:00, do nothing.

Thank you in advance, I hope you have a great day.

excel
1 Answers

Per my understanding from the question (some clarification is needed), I would recommend read the post: Excel compare time or apply condition on timestamps to understand how to handle timestamps in Excel. Following the steps indicated in referred post this is how it can be done.

The integer number representation of the hours (just format as a Number to obtain the numeric representation of the hour) we are interested are:

  • 9:00 -> 0.29
  • 21:00 -> 0.87

Standardizing the timestamp to date 1/0/1900 as follow:

=IF(A2="","", MOD(A2,1))

Then we can do the comparison to identify the shift in the Shift column:

=IF(A2="","", IF(AND(B2>=0.29, B2<0.87), "Day Shift", "Night Shift"))

Created a helper column for easier identification, but all can be done without helper columns.

Then finally we can do the calculation:

=LET(offsetValue, OFFSET(D2,-144,0), IF(D2="", "", 
  IF(ISNUMBER(offsetValue), D2+offsetValue, "Non Valid Qty")))

If there is no previous information then Non Valid Qty is returned, otherwise the Qty from 144 previous row is added to the current Qty value. Used LET function for a better readability.

Here a screenshot: Screenshot

Here is a public link to sample excel file.

David Leal0
Related