Calculate negative hours (time)
 

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

Calculate negative hours (time)
 

Hi Jolanta,

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

ToolsOptionsCalculation tab, check "1904 date system"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Jolanta" wrote in message oups.com...
| 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
|

Calculate negative hours (time)
 

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


"Jolanta" wrote:

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

Calculate negative hours (time)
 

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


On 17 Sep, 11:36, Mike H wrote:
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



"Jolanta" wrote:
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- Dölj citerad text -

- Visa citerad text -

Calculate negative hours (time)
 

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

"Jolanta" wrote:

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


On 17 Sep, 11:36, Mike H wrote:
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



"Jolanta" wrote:
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- Dvlj citerad text -

- Visa citerad text -

Calculate negative hours (time)
 

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?
--
David Biddulph

"Jolanta" wrote in message
oups.com...
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

On 17 Sep, 11:36, Mike H wrote:
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



"Jolanta" wrote:
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- Dölj

Calculate negative hours (time)
 

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

That system is present many versions of Excel, including yours

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Jolanta" wrote in message oups.com...
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


On 17 Sep, 11:36, Mike H wrote:
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



"Jolanta" wrote:
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- Dölj citerad text -

- Visa citerad text -

Calculate negative hours (time)
 

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


On 17 Sep, 15:12, "Niek Otten" wrote:
<so I don't have the 1904 date system.

That system is present many versions of Excel, including yours

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Jolanta" wrote in ooglegroups.com...

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

On 17 Sep, 11:36, Mike H wrote:



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

"Jolanta" wrote:
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- Dölj citerad text -

- Visa citerad text -- Dölj citerad text -

- Visa citerad text -