Errors after midnight in formula calculation

[andrewb90]

Hello All,

I am having an issue with accurately counting hours worked. Now, my formulas all work fine until the ending point of a shift reaches the AM. For Example: 6PM - 1AM would be a 7 hour shift, but the result is negative 17 hours. Now I am using two different set of formulas that are being affected.

This one totals the hours worked for the week: The example shift would be deducted entirely from the total.

[/COLOR]=((F87-E87)*24)+((H87-G87)*24)+((J87-I87)*24)+((L87-K87)*24)+((N87-M87)*24)+((P87-O87)*24)+((R87-Q87)*24)[COLOR=#333333]

This one totals the amount of shifts between a certain time (based on values in another cell). The example shift, just gets completely ignored with this formula.

=(SUM(IF(E$87:E$111<$B87,IF(F$87:F$111>$A87,IF(F$87:F$111<$B87,F$87:F$111,$B87)-IF(E$87:E$111>$A87,E$87:E$111,$A87))))*24)

Any help would be awesome!

 

[AhoyNC]

Without a date Excel doesn't know you went past midnight. One way around this is with the MOD function.
This link on time calculations may be of help. It will explain how this function works with time calculations.
https://www.youtube.com/watch?v=btiuexHSvHY

Format cell as General or Number.

Excel Workbook
	A	B	C
1	6:00 PM	1:00 AM	7
Sheet

 

[andrewb90]

Could the date be referenced on another cell? I have the date above each column.

 

[andrewb90]

Thank you for the link! that was extremely helpful!!!

 

[AhoyNC]

You're welcome. If you go to the main TouTube channel for Excel is Fun and type in the search box "hours worked" you will find a number of videos on this subject.

https://www.youtube.com/user/ExcelIsFun/search?query=hours+worked

Not sure how your data is set up, but maybe you could add the date & time as in the example below.
Column E is formatted as number.

Excel Workbook
	A	B	C	D	E
1		10/30/2015	10/31/2015
2		6:00 PM	1:00 AM		7
Sheet