Hi,
I would like to calculate my working hours in excel. I made some kinf of chart, but I have a problem how to calculate my working hours. I must calculate my working hour and then substract them from montly commitment hours ( sometimes exceeds my working hours and sometimes not). I have problems in cell F35 and consequently in cell F37 and F40. Sorry for my bad english. here is also a chart, please be kind and help.
Thanks in advance.
Workbook 1.xlsWorkbook 1.xls
It would help if the file wasn't password protected...
Yeah my bad, password is mzan, sorry i have tried to edit my first post but you were just to quick
Your problem comes from the fact that you enter decimal numbers and not time.
F.i. in F35 you entered 176 which to XL is 176 days ! enter 176:00:00 instead
The same for F38 02:00:00 and F39 01:35:00
Changed those cells to hours now see if the results are what you expected
Workbook 1-1.xls
Yep. That's it... Excel doesn't always handle negative time well, so this (slightly modified sheet) fixes the bugs, I think.
Re Post #5, there were also some issues with the calculations below the 176:00 that also needed to be sorted out... addressed in post #6
Yessssssssss, thanks guys, big thanks to you Glenn (post #6) that's what I need
Sorry guys, another question, what if is negative value in cell F39 (say that in the last month I didn't reach montly commited hours and I'm 2 hours short and in cell F40 came undertime and that minus hours I take into the next month, I will transfer that minus into february), Thanks
Last edited by klofutaric; 01-04-2014 at 07:25 AM.
While I'm at it... Where does the number in F38 come from??
Number in F38 I put in manually regarding how much overtime will boss pay me in that month. If I have 30 hour overtime my boss will pay me 20 hours or less or nothing if I was under montly commited hours in previous month and I must copensate minus hours from previous month.
I have this sheet for january another sheet for february and if I'm in cell F40 Undertime or overtime I'll paste that total in sheet february in cell F39.
Last edited by klofutaric; 01-04-2014 at 10:20 AM.
set it to use 1904 year then it can use negativ time
workbook ver 1.0.xlsx
try this one.
Hm, seems to me that I can't put in the cel negative number, because if I do that I get an error, but If I put in another cell-, eg I put in cell (E39) formula =-G39 than everything works fine.
workbook ver 1.0.xlsx
Hopefully, this is what you need (finally!!). If it is, don't forget to click the star at the bottom left of this post... If it isn't, well, ask again!
Great job, Glenn, Thank you very much, you really know excel very well, click on the star is the least I can do to thank you.
Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.
In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.
Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Ok, Thanks, I didn't know that, next time I will do that.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks