# 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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### 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
7
Views
137
Replies
2
Views
232
Replies
2
Views
130
Replies
0
Views
240
Replies
8
Views
599

1,141,220
Messages
5,705,091
Members
421,378
Latest member
CarlosDuran

### 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.

### Which adblocker are you using?

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

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