I have a timesheet calculator and when you enter in 8:00 AM then the formula that calculates the hours worked enters -8.00. Once you put in a clock out time then it corrects itself, but why is it doing -8?
e.g.
In / Out / Hours
8:00 / / -8.00
8:00 / 12:00 / 4.00
Workbook is attached that shows the -8.00. I know I could make the formula just be blank until the second entry but why does it return a negative prematurely?
One way, in F7, copied down:
=IF(E7="",24*MAX((C7-B7),0),(MAX((C7-B7),0)+(E7-D7))*24)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Since check out times are blank, Excel treats them as 0.
0-8 is negative 8.
1. Click on the * Add Reputation if you think this helped you
2. Mark your thread as SOLVED when question is resolved
Modytrane
Glenn can you help walk me through this.
Are all you doing is adding essentially the MAX and therefore preventing it from going negative and instead return 0?
Mody, thank you.
Yes, that's pretty much it. If the first end time is blank, the formula returns 0. If the secondf end time is blank, only the duration of the morning session will be returned. Only when the end times are added, will the formula show something other than zero.
Glen, Can you help out with another question regarding my same spreadsheet.
I have the hours copied down to a row and then another row that rounds that for our time tracking software. Issue is that it isn't rolling over after 40 hours like it should. Instead it is SUM the regular hours and then rounding. Hard to explain sorry.
Row 14 is true hours (same as F42:F48)
Row 15 is OT hours based on row 14 (anything over 40)
Row 17 is taking the matching cell in row 14 and rounding it. (it is reducing from two decimals to one)
Row 18 is OT and is taking the matching cell in row 15 and rounding it. (it is reducing from two decimals to one)
this issue is that when you round row 17 you may hit 40 hours quicker as some may round up. I attached another spreadsheet that will help explain better.
I have the two rows because it is showing the employees what they really worked versus what they are to enter into our tracking software
You have become yet another victim of rounding errors when adding/subtracting time in Excel.
Change the formula in B14, and simlilarly across the rest of the row to:
=ROUND(1440*MAX(0,MIN(40,SUM(F4:F4))-SUM(A14:A14)),0)/1440
This removes rounding errors and both sets of figures add up to 40 hr.
Why can I not change it in B14 and drag?
I was getting close darn it. I had the Round but not the 1440.
I also am failing at your formula. I added it in and it doesn't seem to be helping.
Last edited by taylorsm; 08-16-2018 at 01:20 PM.
The way your formula is set up, the first term increments a row and the second a column. I thought that you had twigged that. It's not very nice, is it. It is (of course) fixable. Give me five minutes.
Try this in B14, copied across. Check it carefully. i did get a bit confused about what you are trying to achieve. But.... I think it's OK:
=ROUND(1440*MAX(0,MIN(40,SUM($F4:INDEX($F$4:$F$10,COLUMNS($A:A))))-SUM($A14:A14)),0)/1440
G18 is still saying .2 instead of .3.
Row 14 is the number of hours worked to two decimals. Our punch clock rounds to two decimals however our accounting website (terrible) rounds only to one. Right now with two decimals you get 40 in row 14, but if you round up C17, D17, E17, F17,G17 then you get 40.1 like in row 17.
Row 14 and Row 17 should cap at 40 hours with anything over (overtime) flowing into the next row.
G17 should = 8.7
G18 should = 0.3
Does that make more sense?
Gawd!!
IF I follow what you had done... you had adjusted the formatting to make the numbers LOOK different. However, that doesn't change the underlying data. So... I went back to square 1 and re-did the calculations ROUNDING to 1 dp and 2. Cells shaded blue have all got nice new formulae that can be dragged across/down. Some of yours were very clunky and had to be adjusted ion a cell-by-cell basis. A few verification cells are shown in gold.
I understand (and get) your expected answer.
I did manage to muck up the date cells in column A, but I'm sure that you'll be able to fix that yourself.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks