shown minus times

butler.n

New Member
Joined
Oct 13, 2006
Messages
20
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
To allow Excel to calculate minus times the best way would be to change to the 1904 date system - Tools~Options & the Calculation tab.

This will add 4 years to any dates already entered though.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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
Back
Top