Time sheet formula question

jfix89

New Member
Joined
May 9, 2016
Messages
3
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 ##########
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,409
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

jfix89

New Member
Joined
May 9, 2016
Messages
3
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

Rules violation
Joined
Jan 21, 2012
Messages
912
Why not show the hours over/under as decimal, rather than time

=(H87-I87)*24
 

Forum statistics

Threads
1,144,374
Messages
5,723,989
Members
422,529
Latest member
mbilal429

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top