Calculate negative hours (time)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jolanta - 17 Sep 2007 09:50 GMT

Hi all

I have one employee NOT working 8 hours sometimes.

Monday 08:00 - 16:00 = 8 hours
Tuesday 08:00 - 15:00 = 7 hours
Wednesday 08:00 - 16:30 = 8,5 hours

a1 b1 c1 d1
08:00 16:00 b1-a1 = 8 +8
08:00 15:00 b1-a1 = 7 -1
08:00 16:30 b1-a1 = 8,5 +0.5

I cant figure out the d1 column formula. Any help appreciated. Sorry
if this has been asked before

Thanks in advance!
Jolanta

Reply to this Message

Niek Otten - 17 Sep 2007 10:12 GMT

Hi Jolanta,

Use the 1904 date system. Do read HELP first so you know what the effects are.

Tools>Options>Calculation tab, check "1904 date system"

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi all
|
[quoted text clipped - 14 lines]
| Thanks in advance!
| Jolanta

Reply to this Message

Mike H - 17 Sep 2007 10:36 GMT

Hi,

Apart from the start and finis times I wouldn't work with the cells
formatted as time' i'd format them as numbers and this gets over the negative
time problem.
I.e. columns C, D & E formatted as number

A B C D E
Start Finish Hrs worked Required hrs +/1
08:00 16:00 =(B1-A1)*24 8 =D1-C1

Mike

> Hi all
>
[quoted text clipped - 14 lines]
> Thanks in advance!
> Jolanta

Reply to this Message

Jolanta - 17 Sep 2007 12:27 GMT

Hello

I only have excel 2000 - (SORRY, should have told you) so I don't have
the 1904 date system.

If I formate the cells as numbers excel shows the numbers instead of
the time values. So how can i keep
the right visualization?

Kind regards Jola

> Hi,
>
[quoted text clipped - 29 lines]
>
> - Visa citerad text -

Reply to this Message

Mike H - 17 Sep 2007 12:42 GMT

Jolana,

Isn't that what you need? If an employee works 7 hours instead of 8 then the
difference isn't a time it's a number of hours (-1 hour). I suggest you have
a look here

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

where Chip Pearson has an example workbook you can download.

Mike

> Hello
>
[quoted text clipped - 40 lines]
> >
> > - Visa citerad text -

Reply to this Message

David Biddulph - 17 Sep 2007 13:21 GMT

Your example had 8.5 (not 8:30) in column C, and 0.5 (not 0:30) in column D,
so I don't understand your visualization problem?

Signature

David Biddulph

> Hello

...

> If I formate the cells as numbers excel shows the numbers instead of the
> time values. So how can i keep the right visualization?
>
> Kind regards Jola

>> Hi,
>>
[quoted text clipped - 29 lines]
> > > Thanks in advance!
> > > Jolanta- Dölj

Reply to this Message

Niek Otten - 17 Sep 2007 14:12 GMT

<so I don't have the 1904 date system.>

That system is present many versions of Excel, including yours

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

> Hi,
>
[quoted text clipped - 30 lines]
>
> - Visa citerad text -

Reply to this Message

Jolanta - 18 Sep 2007 12:28 GMT

Hi all, Thanks for your help. I have found a solution. Tread closed :)

> <so I don't have the 1904 date system.>
>
[quoted text clipped - 55 lines]
>
> - Visa citerad text -

Reply to this Message