Negative Time.....? 1

[Loomah]

Hellooooooo everybody
On the face of it this (I thought) wouldn't be a problem.

What I'm trying to do is run a very simple time sheet (in xl2k). Start time, end time, time for lunch, daily total, weekly and cumulative total. Straight forward.

The problem is comparing hours worked against a running total of standard hours.

Assume a weekly total of 40 hours standard ie 8 a day. Compared to 40 or more actually worked I can get the difference between the two but if only 38 hours worked I get rubbish!

Have tried manipulating the times, the text, the formatting etc. Why can't I just get a negative time??

Loomah
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Wray69]

Loomah,

Check out this site here, it may be able to help you out.

http://www.cpearson.com/excel/overtime.htm

Regards -

Wray

 

[lespaul]

"Compared to 40 or more actually worked I can get the difference between the two but if only 38 hours worked I get rubbish!"

the difference between which two? What is it you are trying to accomplish with this spreadsheet? What kind of "negative time" are you trying to get?



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[onedtent]

what u need to do is use
=TEXT(ABS(A1-B1),"-h:mm:ss&quot
where a1 is less than B1.

u can also workaround the problem by changing the date system to 1904. (tools, Options, Calculation)

to xplain: when u work with time, u r really working with dates in fraction form. when u get negative time u are dealing with dates less than 1-Jan-1900 and XL cannot handle that.

 

[Loomah]

Thanks all
This is a quick and dirty short term problem. For the sake of clarity it's like flexi-time:-

If standard hours supposed to be worked are 8 hours a day, excluding lunch, and the actual worked Mon - Thur is 9 a day I want to see the variance of 4:00 ie 4 more than required (32 required, 36 worked)

However if only 3 hours are worked on Fri then the variance should show -1:00 (40 required, 39 worked.) It's a daily comparison of the total hours that should be worked against the actual total, both running totals.

So, Chip's site doesn't have the soln I was looking for (for once) though I could work on the ideas there. And the formula isn't flexible enough (I'd already tried numerous variations on the idea.)

The simplest suggestion, 1904 date system, produced the desired effect so that's my way forward for now!

Thanks again!
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

Just to add another formulaic solution - you could try this:
=IF(C7>C15,"-" &TEXT((C7-C15),"[hh]:mm:ss&quot,C15-C7)

where C15 is total of ACTUAL hours and C7 is total of STANDARD hours Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur