Time Sheet Help

I have 2 problems with a time sheet that I am working on.

The first is that I cannot get exel to show negetive hour/minute values. For instance - We normally finish work at 00:30 (A1) but finished at 23:45(B1). The calculation I am using is =SUM(B1-A1). This is giving a value of 23:15 instead of -00:45. However if we finished after 00:30 the value is correct.

Next - if we work 28hrs over time then we are paid for example 10.53 per hour for the first 20hrs and then 14.04 for the next 8hrs. What would the formular be to show the total amount due?

Many thanks

Originally Posted by pdizzy

The first is that I cannot get exel to show negetive hour/minute values. For instance - We normally finish work at 00:30 (A1) but finished at 23:45(B1). The calculation I am using is =SUM(B1-A1). This is giving a value of 23:15 instead of -00:45. However if we finished after 00:30 the value is correct.

There are a number of different problems here.

Firstly you can't show negative times in excel unless you switch to the 1904 date system (Tools > Options > Calculation > 1904 date system) but you'd have to be careful using that as it will change any existing dates you have in your workbook.

You could show minus hours as a text value but then it's difficult to use in time calculations.

The easiest way might be to use decimal time, e.g. multiply the result by 24 to get a decimal number of hours, e.g. 0.5 = 30 minutes

The situation you describe is further complicated by times being either side of midnight. If you include the dates with each time in A1 and B1 then you could simply use

=(B1-A1)*24

but assuming you just have times in A1 and B1 and you'll never be more than 12 hours early or late try

=(B1-A1+IF(MOD(B1-A1,1)<0.5,(A1>B1),-(B1>A1)))*24