Time sheet formula question

[jfix89]

I created a weekly time sheet to calculate my total hours worked, based off of an example I found online. It has been working very well for me for over a year. However, it just occurred to me recently that while the sheet is calculating "overtime hours", it does not calculate remaining hours to get to 40, or "hours short", if you will.

Currently, I have a Column G calculating the total hours for that week (a SUM formula). Column I is 40:00:00 hours. Column J is a IF formula, calculating the hours over 40. I would like to show the hours under 40, as a negative. I can figure out how to get it to calculate the correct hours under 40, but I can't get it to show as a negative, so it still appears to be "overtime" hours.

For this example, the values I have are:
H = 37:45:00
I = 40:00:00
J = 2:15:00

Current Column J formula, that only shows hours worked over 40 hours:
=IF(H87>I87, H87-I87,0)

Column J formula I'm working on that shows hours worked over or under 40, but shows both as a positive time value:
=IF(H87>I87, H87-I87, I87-H87)

I have tried different versions of multiplying I87-H87 * -1 to get it to display as a negative value, but I'm wondering if that doesn't work for time values? It just changes the cell to ##########

 

[DRSteele]

Welcome to the forum.

Excel chokes on negative time values. There are two workarounds.

First, you can alter the Excel general options for the worksheet to use the 1904 data system, which will then allow negatives; unfortunately, it will alter all the dates in your workbook by something like four years and one day, forcing you to re-rig the all the dates.

A better solution is a text solution, like this, where B2-A2 produces a negative: =IF(B2-A2<0, "-" & TEXT(ABS(B2-A2),"hh:mm"), B2-A2) Keep in mind that the algorithm is reporting text, though, and not a serial time number.

 

[jfix89]

Welcome to the forum.

Excel chokes on negative time values. There are two workarounds.

First, you can alter the Excel general options for the worksheet to use the 1904 data system, which will then allow negatives; unfortunately, it will alter all the dates in your workbook by something like four years and one day, forcing you to re-rig the all the dates.

A better solution is a text solution, like this, where B2-A2 produces a negative: =IF(B2-A2<0, "-" & TEXT(ABS(B2-A2),"hh:mm"), B2-A2) Keep in mind that the algorithm is reporting text, though, and not a serial time number.

So it wouldn't be able to figure the negative into an overall calculation if the "-" is just a text piece, correct? At the top of the spreadsheet, I am calculating total overtime hours worked as well as average overtime hours/week worked. This is the reason I am looking to calculate both shortages and overages of hours.

 

[theBardd]

Why not show the hours over/under as decimal, rather than time

=(H87-I87)*24

 

[jfix89]

that works perfect, thank you!