CODEWITHSUNDEEP
excelexcel-formula
Anthony Holland
Anthony Holland

Reputation: 23

Excel Time Calculation Across Midnight

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

Upvotes: 0

Views: 429

Answers (1)

Solar Mike
Solar Mike

Reputation: 8375

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.

Upvotes: 2

Related Questions