Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 | |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Calculate no. of hours in a stipulated time? | Excel Worksheet Functions | |||
how do i show negative time in hours minutes | Excel Worksheet Functions | |||
How to show negative time (e.g. -10 hours) | Excel Worksheet Functions | |||
calculate difference in time to hours | Excel Worksheet Functions | |||
calculate negative or positve difference in time | Excel Discussion (Misc queries) |