# shown minus times

#### butler.n

##### New Member
Hi

Im currently trying to build a spreadsheet to record times worked for a particular month.
The guidlined hours for the day are 8 hours but the employees have the option of working both over and under that time. For example if the emplyee worked 08:30 hours that day he would build up 00:30 minutes flexi-time, if the employee worked 07:30 he would have lost - 00:30 minutes flexi-time

Iv currently built a spreadsheet which includes

Time-In Time-Out Days hours Flexi carried forward Flexi built

The trouble is when i use the formular =(days hours-8) to work out the flexi carried forward. If this is a minus value the time will not be displayed.
Another problem i have is that i need to accumulate the flexi time in the flexi built. This is based on the flexi carried forward e.g.

Flexi Carried forward
+00:30
+00:15
-00:50

Therefor flexi built would display

Flexi Built
+00:30
+00:45
-00:05

Is this possible to acheive in Excel, if so i would be gratefull for any help

Many thanks

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### Darren Bartrup

##### Well-known Member
To allow Excel to calculate minus times the best way would be to change to the 1904 date system - Tools~Options & the Calculation tab.

#### butler.n

##### New Member
I've jus tryed that, not really the outcome i wanted.

i wanted to minus 8 hours of the hours an employee worked to get their time variance (Flexi Time)

hours worked
07:30 (-8 hours)

Time varience
-00:30

#### Richard Schollar

##### MrExcel MVP
Hi

If this is only for presentation (ie you don't need to do calculations on the result) then you could use the following formula:

(assumes 07:30 is in eg A1 and 08:00 is in B1)

Code:
``=IF((A1-B1)<0,TEXT(ABS(A1-B1),"\-hh:mm"),A1-B1)``

Best regards

Richard

#### butler.n

##### New Member
Is there a way of encorperating both the positive and negative times in the one formula so i could just leave it in the spread sheet which would work the times as they are entered?

Thanks

Replies
0
Views
64
Replies
0
Views
32
Replies
4
Views
86
Replies
5
Views
82
Replies
19
Views
219