Displaying time

[ruthtwigg]

I'm trying to set up a spreadsheet that calculates peoplesworking hours but I'm getting into a pickle and keep getting the error #######,when trying to work out if people are over or under their expected workinghours.

I have effectively 3 columns, the first shows the number ofhours people are contracted to, the second is a calculation of the total numberof hours worked that week, and the third SHOULD be showing if there is a creditor defecit in the number of hours worked.

In the first column, I have the contracted number of hourspeople are due to work each week (mostly 37.5), so I have that displayed as37:30, under the format [hh]:mm, however everytime I click ok, it adds the date01/01/1900 onto the beginning of the cell, and this appears to be what iscausing issues with my later calculaiton

 

[BiocideJ]

The display showing date shouldn't affect anything because excel stores dates and times as numbers essentially regardless. I suspect your issue lies in the fact that there is no such thing as "negative" time.

Basically, if your output is 1:00 - 2:00 you will get ####### because there is no way to show -1:00 in [hh]:mm format.
If you could post some of your formulas, perhaps we could assist with getting it to LOOK like what you need, however.

As a for instance, this might work....
=IF(SIGN(A2-B2)=-1,"-","") &TEXT(ABS(A2-B2),"[hh]:mm")
where A2 and B2 are the two date/times