0

I have an application for a timesheet where I need to look up a multiplier based on the hour of the day. We will have a two clock in and clock out to handle lunch breaks. There will be multiple shifts working across the 24-hour period. I am struggling with how to handle where a clock in might be 9:00pm on day 1 and the clock out would be 2:00am on day 2. I am figuring I need to use MOD or something and have done similar things with days of week when it's not the typical ending on Saturday, but the solution has not clicked in my brain yet.

From the screen snapshots column F is based on the time in J3:M3 and in column F the worked as YES is correct.

The time entered in J9:M9 wrap around midnight and column G hours worked in not correct. Green is correct, Red is not correct and should be no and the Yellow that is no should be YES. I am not handling the wrap around midnight.

I was using the time value but converting to minutes make it a little more straightforward to understand. Here is the formula in column G, column F is the same except points to row 3 instead of 9.

=IF(OR(AND($C2>=$J$11,$C2<$K$11),AND($C2>=$L$11,D$2<$M$11)),"YES","no")

I understand the above formula will not work, just having a hard time coming up with a solution that will doing the wrap and how maybe a MOD might be used.

Spreadsheet1of3

Spreadsheet2of3

Spreadsheet3of3

Improve this question
2
  • 1
    To get total elapsed time, if the date is included, you can use simple subtraction. If you don't include the date, assuming all work is less than 24 hours, you just need a simple comparison =OUT-IN+(IN>OUT). For the multiplier, you'll need to split the minutes into 15 minute segments. You should be able to derive that, but your screenshot is not useful to me in that regard. Commented Jun 24, 2024 at 14:26
  • 1
    Never work with timestamps, but always with datetimestamps (you include the date in your timestamp). Excel has the wonderful property that one day is counted as "1", enabling the usage of datetimestamps. You can use cell formatting for deciding to show datetimestamps as just timestamps, and the calculations will become trivial. Commented Jun 24, 2024 at 14:52

1 Answer 1

Reset to default
2

So, one with mod() and one based on logic:

enter image description here

The logical test checks to see if the end time is smaller than the start time and, if true, adds 1. That covers going across midnight. The same method was also given in a comment above.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I presume the logic based one assumes that False is 0 and True is 1, and that 1 represents a 24 hour day? You should explain that.
@MarkRansom Doesn't Excel always assume True =1 and False = 0? One reason Excel has many reserved words. Worth the OP testing things to find out what works - how some of us had to progress...
Maybe it always does assume those values for True and False, many languages do the same. But that's not universal knowledge so it's a better answer if you can be explicit about it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.