+ Reply to Thread
Results 1 to 16 of 16

(Help) Time Sheet total hours worked per day and overall week

  1. #1
    Registered User
    Join Date
    02-11-2014
    Location
    Norwich
    MS-Off Ver
    Excel 2003
    Posts
    26

    (Help) Time Sheet total hours worked per day and overall week

    Hello,

    I am having difficulty with sorting out a time sheet.

    We have 8 tabs
    Tab 1 - Hours
    Tab 2 - Sunday
    Tab 3 - Monday
    Tab 4 - Tuesday
    Tab 5 - Wednesday
    Tab 6 - Thursday
    Tab 7 - Friday
    Tab 8 - Saturday

    The hours tab is split down for each day and will look up all the names for each day. (staff change over the week or change location on sheet.)

    then on the hours tab we lookup the date and person name to find the start hours and end hours and should then show total time worked

    E.g
    Start Time 3.30pm
    End Time 6.30am
    Total Time 15.00

    Start Time 6.30am
    End Time 3.30am
    Total Time 9.00

    At the moment for this to work on the individual day tab i am having to put Start Time 4/4/10 15:30 End Time 5/4/10 6:30
    Then on the hours i have a tab just after the start and end that gets the number value of the date and is times by 24 then the total is worked out by taking away the numbers. (is there a way where i can just but the hours in with out the date?)

    Then on the hours tab there is a weekly total. This has all the agents names it will then look up each day and fill in the hours for the agents each day.
    It will then show total hours for each day as well as total hours for staff per week along with all staff total hours.

    I would also like it to not show 12:00am if there is no agent or hours worked and would like it if its 0 hours worked to leave the cell blank.

    I have noticed that at moment for like 2 hours 15 mins it will show as 2.25 which is there a way to get it to display as 2 hours 15mins in the total hours box?

    *The Sunday to Saturday tabs would have alot of other data on but to work on this i have just gone with the basic information.

    Time Sheet.xlsx

    Thanks for any help given.
    Last edited by SirDraco; 10-29-2014 at 05:19 AM. Reason: Extra Details
    To be forgotten is worse than death!!!

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: (Help) Time Sheet total hours worked per day and overall week

    I had to do a bit of reformatting to make copy & paste easier. How does this look?
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    02-11-2014
    Location
    Norwich
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: (Help) Time Sheet total hours worked per day and overall week

    Thanks this looks like it will be able to do the task that is required i will test this on the version of report i am working on if any issues will come back if all fine will mark this as solved.

  4. #4
    Registered User
    Join Date
    02-09-2014
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    84

    Re: (Help) Time Sheet total hours worked per day and overall week

    I removed the date from the daily tabs as requested
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-11-2014
    Location
    Norwich
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: (Help) Time Sheet total hours worked per day and overall week

    Quote Originally Posted by vogel997 View Post
    I removed the date from the daily tabs as requested
    This all works well.

    Just trying to see how i could get the hours to work as a vlookup thing as the names may not be on each day so can not just guess which cell they will be in.

    I have tried to display this on the original hours tab.
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: (Help) Time Sheet total hours worked per day and overall week

    Try this version out...
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-09-2014
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    84

    Re: (Help) Time Sheet total hours worked per day and overall week

    week-time-sheet.xlsx
    Quote Originally Posted by SirDraco View Post
    This all works well.

    Just trying to see how i could get the hours to work as a vlookup thing as the names may not be on each day so can not just guess which cell they will be in.

    I have tried to display this on the original hours tab.


    vlookup version

  8. #8
    Registered User
    Join Date
    02-11-2014
    Location
    Norwich
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: (Help) Time Sheet total hours worked per day and overall week

    Thanks Vogel997 this looks great only problem is i am trying to use this on Excel 2003 so the =IFERROR(MOD(D4-C4,1),0) dose not seem to work, what would i do to fix this?

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: (Help) Time Sheet total hours worked per day and overall week

    Why not just use the very simple: =IF(B4="","",(D4-C4)) and make sure formatting is set to h "h "mm "min"

  10. #10
    Registered User
    Join Date
    02-09-2014
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    84

    Re: (Help) Time Sheet total hours worked per day and overall week

    Quote Originally Posted by SirDraco View Post
    Thanks Vogel997 this looks great only problem is i am trying to use this on Excel 2003 so the =IFERROR(MOD(D4-C4,1),0) dose not seem to work, what would i do to fix this?
    change the formula to:

    =IF(C16="-","",MOD(D16-C16,1))

    iferror doesn't work in excel 2003

  11. #11
    Registered User
    Join Date
    02-09-2014
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    84

    Re: (Help) Time Sheet total hours worked per day and overall week

    Quote Originally Posted by SirDraco View Post
    Thanks Vogel997 this looks great only problem is i am trying to use this on Excel 2003 so the =IFERROR(MOD(D4-C4,1),0) dose not seem to work, what would i do to fix this?
    change the formula to:

    =IF(C16="-","",MOD(D16-C16,1))

    iferror doesn't work in excel 2003

  12. #12
    Registered User
    Join Date
    02-11-2014
    Location
    Norwich
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: (Help) Time Sheet total hours worked per day and overall week

    Quote Originally Posted by Glenn Kennedy View Post
    Why not just use the very simple: =IF(B4="","",(D4-C4)) and make sure formatting is set to h "h "mm "min"
    I tried to use that but this seem to not work if the shift hours is for example 6pm till 6am it will now show hours or mins worked it will show as N/A or Nill.

    I have attached the full report i am working on (Not Completed), i am still having issues with the hours, once this is working for 1 agent i can then get it working for the rest just having difficulties working out how to code it correctly to work.)

    I have put same names and hours on here for an example just to show what i am trying to do.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    02-09-2014
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    84

    Re: (Help) Time Sheet total hours worked per day and overall week

    if mod is the problem try this

    =IF(C4="-","",IF(D4<C4,D4-C4+1,D4-C4))

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: (Help) Time Sheet total hours worked per day and overall week

    Try

    =IF(M6<L6,(M6+1)-(L6),(M6-L6))

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: (Help) Time Sheet total hours worked per day and overall week

    Same as vogel 1997, except set for the data in post 12

  16. #16
    Registered User
    Join Date
    02-11-2014
    Location
    Norwich
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: (Help) Time Sheet total hours worked per day and overall week

    Thanks for the help you guys have given me.

    With alot of vlookup references i am able to grab most of the information that is needed.

    This code *=IF(F5="-","",MOD(G5-F5,1))* Works great and if - is in the hours it stays blank and then if there is hours in then works out the hours and mins with no issues at all.

    I have just got the issue with the getting the hours.

    I have used code *=IF($A5="","",IF(ISNA(VLOOKUP($A5,Sunday!$J$4:$BK$39,8,FALSE)),"-",VLOOKUP($A5,Sunday!$J$4:$BK$39,8,FALSE)))*

    Which is A5 is blank then it shows "-" which is fine but i am having the issue that if A5 has a name in it will do the lookup but the look up is blank and instead of staying as "-" it is doing 12:00 AM.

    What can i do to stop it showing 12:00 AM and to show "-" if the cell it is getting the data from is blank?


    I found the answer in the formatting.
    reformatted cells to *h:mm AM/PM;;-* so if lookup is blank will show "-" and if lookup hours will show "3:15 PM"
    Last edited by SirDraco; 11-10-2014 at 06:20 AM. Reason: Worked out answer

+ 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. Total hours worked for the week
    By bud11dy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-15-2013, 09:18 AM
  2. [SOLVED] Creating a population of total hours worked for that week ?
    By calamityjane in forum Excel General
    Replies: 8
    Last Post: 09-13-2012, 06:00 PM
  3. Replies: 0
    Last Post: 05-14-2012, 05:36 PM
  4. Total hours worked based on two time entries
    By avidcat in forum Excel General
    Replies: 6
    Last Post: 07-29-2011, 08:43 AM
  5. [SOLVED] [SOLVED] Getting a total of hours &amp; mins worked per week
    By big_weegie in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 06-16-2005, 07:05 PM

Tags for this Thread

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