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
 

Some videos you may like

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
Joined
Mar 13, 2006
Messages
1,291
Office Version
  1. 365
Platform
  1. Windows
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.
 

butler.n

New Member
Joined
Oct 13, 2006
Messages
20
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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Oct 13, 2006
Messages
20
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,028
Messages
5,526,332
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top