If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Timesheet Function
I have the following in a spreadsheet for working out time work. Everything
adds up into little columns, however when this entry is put in, it produces a strange result in the over-time column - I know this entry should read as 0, but for some reason states that it is and exponential value of -15. Description or Formula in [] 27/08/2008 Cell F83 Start Time 0630 [Start Time - Just a time entry] F84 Start Lunch 1230 [Start Lunch - Just a time entry] F85 Finish Lunch 1300 [Finish Lunch - Just a time entry] F86 Finish Work 1500 [Finish Work - Just a time entry] F87 Hours Worked 8 [=((F86-F83+(F86F83))-(F85-F84+(F85F84)))*24] F88 Hours Over Time 1.77636E-15 [=MAX(0,F87-$Z$9)] [$Z$9 is a cell stating that normal working hours are 8] Any other times are displayed correctly, for example start at 0630, lunch at 1200 till 1230 and finish work at 1500 produces the result of 8 hours worked and 0 hours over time. The formula and what not is a result of looking through here and also some other related websites. Basically i'm after the reason why it displays 1.77636E-15 INSTEAD of 0 for the over time ONLY when the lunch period or 1230 till 1300 is entered on an 8 hour day. |
#2
|
|||
|
|||
Timesheet Function
It just so happens I'm a computer contractor and have to deal with this sort
of thing all the time in tracking my own hours. I'm pretty sure your problem has to do with the way Excel stores hours, minutes and seconds. You may already be aware that each integer part of the timestamp is the date, and the fractional part is the time, expressed as a fraction of a day -- that is, nnnnn.5 is noon, nnnnn.75 is 18:00 and so on. But it's stored in binary fractions, which cannot store tenths and some other fractions with complete accuracy (for exactly the same reason 1/3 + 2/3 can come out 0.99999 on your calculator). So my guess is that after calculating the hours your guy worked, it came out not exactly to 8 hours but to very slightly more - to 8.00000000000000177636 hours. The usual way to deal with this is to round your calculated figures to the nearest quarter hour, half hour, whatever you think appropriate. The MROUND function is handy for this purpose. --- "Ricardo" wrote: I have the following in a spreadsheet for working out time work. Everything adds up into little columns, however when this entry is put in, it produces a strange result in the over-time column - I know this entry should read as 0, but for some reason states that it is and exponential value of -15. Description or Formula in [] 27/08/2008 Cell F83 Start Time 0630 [Start Time - Just a time entry] F84 Start Lunch 1230 [Start Lunch - Just a time entry] F85 Finish Lunch 1300 [Finish Lunch - Just a time entry] F86 Finish Work 1500 [Finish Work - Just a time entry] F87 Hours Worked 8 [=((F86-F83+(F86F83))-(F85-F84+(F85F84)))*24] F88 Hours Over Time 1.77636E-15 [=MAX(0,F87-$Z$9)] [$Z$9 is a cell stating that normal working hours are 8] Any other times are displayed correctly, for example start at 0630, lunch at 1200 till 1230 and finish work at 1500 produces the result of 8 hours worked and 0 hours over time. The formula and what not is a result of looking through here and also some other related websites. Basically i'm after the reason why it displays 1.77636E-15 INSTEAD of 0 for the over time ONLY when the lunch period or 1230 till 1300 is entered on an 8 hour day. |
#3
|
|||
|
|||
Timesheet Function
I replicated your data and I got exactly 0. I am wondering if your cells
F83:F86 are formulae, and not calculating exact time periods? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ricardo" wrote in message ... I have the following in a spreadsheet for working out time work. Everything adds up into little columns, however when this entry is put in, it produces a strange result in the over-time column - I know this entry should read as 0, but for some reason states that it is and exponential value of -15. Description or Formula in [] 27/08/2008 Cell F83 Start Time 0630 [Start Time - Just a time entry] F84 Start Lunch 1230 [Start Lunch - Just a time entry] F85 Finish Lunch 1300 [Finish Lunch - Just a time entry] F86 Finish Work 1500 [Finish Work - Just a time entry] F87 Hours Worked 8 [=((F86-F83+(F86F83))-(F85-F84+(F85F84)))*24] F88 Hours Over Time 1.77636E-15 [=MAX(0,F87-$Z$9)] [$Z$9 is a cell stating that normal working hours are 8] Any other times are displayed correctly, for example start at 0630, lunch at 1200 till 1230 and finish work at 1500 produces the result of 8 hours worked and 0 hours over time. The formula and what not is a result of looking through here and also some other related websites. Basically i'm after the reason why it displays 1.77636E-15 INSTEAD of 0 for the over time ONLY when the lunch period or 1230 till 1300 is entered on an 8 hour day. |
Thread Tools | |
Display Modes | |
|
|