+ Reply to Thread
Results 1 to 18 of 18

Need help by Timesheet

  1. #1
    Registered User
    Join Date
    02-05-2014
    Location
    Nederland, Den Haag
    MS-Off Ver
    Excel 2016-2019 (nl-NL)
    Posts
    14

    Need help by Timesheet

    I have a timesheet created, with the examples that were in the forum.
    I've gotten a lot of help, but knowledge is invaluable. (sigh)
    I do not get out in the totals of hours.

    1st attachment is how I filled my hours.
    it's a bit clumsy, but for me it was eventually cluttered.
    zonder puntjes.xlsm

    2nd attachment is with the help of fellow forum users become more beautiful.
    But ....
    there are some points that can be better.
    Map3.xlsm

    In column O : normal hours 100 % there is 8 hours .
    but if I have to work 4 hours, 4 hours standing. (Mon - Fri)
    If I work more than 8 hours than the extra hours should be allocated.
    My Employer is flexible with working hours, if total working hours is 8.
    If there is little work, you finish work, clean up and go home. This is less than 8 hours of work.

    In a quarterly census will only work on a Saturday .
    Again, it is the more people count of the faster we go home .

    Column T and U is the total hours worked (= sum of column N)
    Box U4 : I have worked fewer hours , even if I have overtime one day .
    I have 38 hours worked including overtime pay everything as 38 hours .
    I have 40 hours worked including overtime pay everything as 40 hours .
    I have 48 hours worked including overtime pay over 40 hours and 8 hours for holiday savings . ( Leave)

    All times are entered as military format manually. 800 hours is 8:00.

    Please work step by step. that i understand the formule.
    keep the layout the same, if possible.

    many thx/ love,
    Mofasa

    Moderator's note: See also this thread posted in Dutch --6StringJazzer
    Last edited by 6StringJazzer; 02-06-2014 at 12:29 PM.

  2. #2
    Registered User
    Join Date
    02-06-2014
    Location
    Central Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Need help by Timesheet

    MoFaSa

    I once designed a timesheet of my own, and also tore my hair because trying to add the minutes, then divide by 60 for hours and converting the remainder to actual minutes was a problem. The worker using the timesheet would work lots of overtime, or oddball shift lengths

    I note in your examples that you have your times in the format of hh:mm each in one cell

    Split the cells so that hh is in one cell, then the mm is in an adjacent cell. in a hidden calculation, multiply the hours figures by 60 and add the minutes figures to give all working times as minutes. Do all calculating then in minutes, and finally divide by 60 with a 'remainder count' function for the leftover minutes to be displayed in appropriate totals fields

    Hoping I have not gone over previous advice, but its the joy of working with numbers that are not neat multiples of 10

    Same idea would be used for currency calculations when the currency is not decimal, reduce everything to pennies

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need help by Timesheet

    Split the cells so that hh is in one cell, then the mm is in an adjacent cell
    Im sorry, I would seriously question that approach. Time, in excel, is actually a decimal of 1(day), and if it is entered as time, then the calcs become far simpler - otherwise you have to add minutes (which are numbers, not time) up to 60, carry the balance over, convert 60 to 1, add that to the hourse (which, again, are numbers, not time) an d when they get to 24, convert 24 to 1 etc etc

    Once you understand how "time" works in excel, then you see that time is in fact a multiple of 10 (sort of)
    Hoping I have not gone over previous advice, but its the joy of working with numbers that are not neat multiples of 10
    I will take a look ane see what we can come up with
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need help by Timesheet

    I am trying to work my way through your logic, row-by-row.

    change the formula in column O to this...
    =IF(V3=7,"",8/24) ("8:00" is text, not a number)

    Change the formula in Q to this...
    =IF(OR(N3="",O3>N3),"",N3-O3)

    Change the formulas in R (all of them), to this...
    =SUM($N$3:N3)

    Once you have this done, let me know where the "errors" are

  5. #5
    Registered User
    Join Date
    02-06-2014
    Location
    Central Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Need help by Timesheet

    I had looked long agao at how Excel handled time, i just found that my approach was simple and more 'human' for programming the timesheet and for the data entry side of things


  6. #6
    Registered User
    Join Date
    02-05-2014
    Location
    Nederland, Den Haag
    MS-Off Ver
    Excel 2016-2019 (nl-NL)
    Posts
    14

    Re: Need help by Timesheet

    @FDibbins
    dear sir,

    I have made some changes.
    I HAVE Deleted colomn V

    change the formula in column O to this...
    =IF(V3=7,"",8/24) ("8:00" is text, not a number)
    Colomn O - i change it to this
    = MIN(N3;TIJD(8;0;0))


    change the formula in column Q to this...
    =IF(OR(N3="",O3>N3),"",N3-O3)
    -formula is not working

    transfer formula from Q to colomn P (125%)
    =ALS(OF(N3>N3;O3="");"";N3-O3)

    Change the formulas in colomn R (all of them), to this...
    =SoM($N$3:N3)
    working good.


    Errors:
    colomn P (marked in red)
    Overtime in Holland is 125% for 2 hrs
    after that 150% for 2 hrs. (colomn Q marked in red)

    regards,
    Mofasa

    Map3-test.xlsm

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need help by Timesheet

    What are the rules governing OT?

    1.25 is paid from how many hours to how many hours?
    1.5 is paid from how many hours to how many hours?
    Is there any extra payment for sunday?
    etc

  8. #8
    Registered User
    Join Date
    02-05-2014
    Location
    Nederland, Den Haag
    MS-Off Ver
    Excel 2016-2019 (nl-NL)
    Posts
    14

    Re: Need help by Timesheet

    Quote Originally Posted by FDibbins View Post
    What are the rules governing OT?

    1.25% is paid from how many hours to how many hours?
    --> 1.25% Max. 2hrs

    1.50% is paid from how many hours to how many hours?
    --> 150% Max. 2hrs

    Is there any extra payment for sunday?
    --> Sure 200% incl. lunch
    But on sunday we don't work. that why sundays it's blank.


    Map3-dibbins.xlsm

    I found a formula for colomn P and Q
    It's works fine.

    issue No. 2 (highlighted in red)
    at week 2
    If I would suggest not working on Tuesday. (this is not a big problem. I can delete the timebreaks)
    Column O -> There is still 8:00. Is it possible that this is an empty box?
    Column P and Q is 00:00. Is it possible that this is an empty box?


    regards,
    Mofasa

  9. #9
    Registered User
    Join Date
    02-05-2014
    Location
    Nederland, Den Haag
    MS-Off Ver
    Excel 2016-2019 (nl-NL)
    Posts
    14

    Re: Need help by Timesheet

    Quote Originally Posted by MoFaSa View Post
    Attachment 295773

    I found a formula for colomn P and Q
    It's works fine.

    issue No. 2 (highlighted in red)
    Column P and Q is 00:00. Is it possible that this is an empty box?
    If you can see in week 1 and 2 (Red marked)

    Colomn P:
    I found the problem about 00:00 mark.
    problem is this character =

    before in colomn P:
    ALS(OF(O3>N3;N3="");"";ALS(N3-O3>TIJD(2;0;0);TIJD(2;0;0);N3-O3))

    after in colomn P::
    ALS(OF(O3>=
    ALS(OF(O3>=N3;N3="");"";ALS(N3-O3>TIJD(2;0;0);TIJD(2;0;0);N3-O3))

    Problem for colomn P is solved.

    the same formula i tried in colomn Q:

    ALS(OF(P3="";P3<TIJD(2;0;0));"";N3-O3-TIJD(2;0;0))
    ALS(OF(P3>="";P3<TIJD(2;0;0));"";N3-O3-TIJD(2;0;0))
    problem is this character >

    If i change it, I get diffrent figures??
    If i restore the box, there is no recalutation of it.[ctrl+Z]


    Map3-dibbins.xlsm


    regards,
    Mofasa

  10. #10
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: Need help by Timesheet

    Here is a Time Card I recently built try it out.

    Time Card Template (Hourly).xlsm

    It's probably more than you were looking for, but you can rummage through the code and see if anything helps

  11. #11
    Registered User
    Join Date
    02-05-2014
    Location
    Nederland, Den Haag
    MS-Off Ver
    Excel 2016-2019 (nl-NL)
    Posts
    14

    Re: Need help by Timesheet

    @ cplettner
    No thank you.
    There is little information and no explanation how it works.
    Thank you for your attention

    I will hold it in my current trusted layout.

    gr,
    Mofasa

  12. #12
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: Need help by Timesheet

    I understand you hesitation and sorry for the lack of information. It's because the time card is pretty complicated.

    It allows individuals to enter in time easily in 6 different columns for multiple punch in and punch outs 8 for 8:00 am 830 for 830 am. Depending on what column time is entered in will determine what column the default hour AM or PM. It calculates vacation and sick time assuming a 8 hour day. It calculates correct overtime based on a 40 hour work week (this was quite difficult because my company's pay periods are 1st - 15th and 16 - end of month, you can imagine the headache when a week is split in half by the pay period). It will automatically calculate holiday pay based on code. It keeps track of all of this month to month so an employee needs only one time card file per year. etc..............

    Here is the code the "ThisWorkbook" module
    Please Login or Register  to view this content.
    Module that calculates Holidays
    Please Login or Register  to view this content.
    Hopefully that answers your questions, but it doesn't hurt my feelings if you don't look at it either. Just trying to be helpful.

    CP
    If your questions has been answered to your satisfaction please don't forget to do the following:

    Add Reputation ... and ... Mark Thread as Solved

    Thanks,

    Ma 10:8b Freely you have received; freely give.

  13. #13
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: Need help by Timesheet

    More Info...


    There are 9 other modules and 5 UserForms

    Part of the Sheet modules
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    02-05-2014
    Location
    Nederland, Den Haag
    MS-Off Ver
    Excel 2016-2019 (nl-NL)
    Posts
    14

    Re: Need help by Timesheet

    I fill the week list updated where possible.
    I have all the 00:00 o'clock indicator in the blue columns has taken away .

    In Sheet3 I have put an explanation of what I did .
    Time display in military time , I can not do . VBA I do not know.
    I do not know how VBA works, and how to handle them. (sorry for this )

    I do not come out in column T

    In the year 2012 I've saved 88:34:00 hours . (T2)
    But in the yellow columns I see the saved leave hours anymore.
    Although I have worked less , I do not see them.

    Under Box T -11 , I cheated.
    The formula was max 40 hours and the rest is holiday hours ( hours for hours )

    ONLY if really Very busy then the overtime is paying .
    But that is only seasonal periods ( Valentine , mothersday , Christmas , etc )


    problem:
    How can I put in column T for hours .
    Military timestamp fill via VBA .
    (800 automatically 8:00 )
    ( 930 automatically 9:30 )
    (1800 automatically 18:00 )

    the layout please don't changed.

    (thanks to Loius, België)

    g ,
    Mofasa
    Attached Files Attached Files
    Last edited by MoFaSa; 02-15-2014 at 11:46 AM.

  15. #15
    Registered User
    Join Date
    02-05-2014
    Location
    Nederland, Den Haag
    MS-Off Ver
    Excel 2016-2019 (nl-NL)
    Posts
    14

    Talking Re: Need help by Timesheet

    Timesheet 00:00 notation remove, if nothing is.

    In a timesheet I have done almost everything except the last column P.
    Can you help me with a 00:00 format in a time sheet?
    If I do not work one(1) day will show up automatically 00:00
    please note ONLY 0:00 designation
    highlighted in red.

    The blue columns that I can fix with the help of forum members.(mr.dibbins 10 times thx)
    But my knowledge is not so far that i can do. Everything step by step I almost can
    do everything except,VBA,PHP, and column P.

    test16-2-2014.xlsm

    g,
    Mofasa

  16. #16
    Registered User
    Join Date
    02-05-2014
    Location
    Nederland, Den Haag
    MS-Off Ver
    Excel 2016-2019 (nl-NL)
    Posts
    14

    Re: Need help by Timesheet

    totaalweek:
    00:00 notation is gone
    =ALS(SOM($L$3:L3)=0;"";SOM($L$3:L3))

    On week 2:
    Only if I do work on Mondays for 8:00 hrs
    its counts thru vryday

    Monday 7:30 to 16:00 = 8:00 hours
    Tuesday free - box must be empty (no 00:00)
    Wednesday free - box must be empty (no 00:00)
    Thursday - box must be empty (no 00:00)
    Friday - box must be empty (no 00:00)
    Yellow box: is total hours worked. (8:00)

    you understand it a little?

  17. #17
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: Need help by Timesheet

    er zitten toch al veel voorwaardelijke opmaken in je bestand, dus waarom er niet ééntje aan toevoegen
    een PHP voor je kolom P en als letterkleur kies je diezelfde als je achtergrond.
    PHP Code: 
    =OF(P1=0;L1=""
    Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

  18. #18
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Need help by Timesheet

    Here is a simple time-sheet that calculates regular hours, overtime, weekend time, pay.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. timesheet help please
    By texmd in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-06-2012, 02:47 AM
  2. Timesheet
    By greg123 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-26-2012, 03:38 AM
  3. Timesheet Help
    By swoznia in forum Excel General
    Replies: 3
    Last Post: 05-25-2012, 02:51 PM
  4. TimeSheet
    By superwiki in forum Excel General
    Replies: 7
    Last Post: 05-27-2009, 12:22 PM
  5. Timesheet
    By Matt in forum Excel General
    Replies: 3
    Last Post: 09-15-2005, 05:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1