+ Reply to Thread
Results 1 to 14 of 14

TIMESHEET - time calculation for multiple jobs type

  1. #1
    Registered User
    Join Date
    11-16-2017
    Location
    England
    MS-Off Ver
    2013
    Posts
    9

    Question TIMESHEET - time calculation for multiple jobs type

    Hi

    Have a look at the attached timesheet.

    I'VE ATTACHED WRONG FILE, SORRY GUYS. CORRECT FILE IN POST #4

    I want to be able to calculate times I have spent doing different jobs types.
    There are 5 job types MAINTENANCE, REPAIR, INSTALL, EXTRA WORK and OFFICE WORK and i would like automatically summarise time spent
    on each TYPE of job at the bottom in cells D20, D21, D22, D23, D24
    So I need to find a way for Excel to add all hours I have spent doing MAINTENANCE for example.

    I think that the way to do that is to use a formula that would look for word MAINTENANCE in column J and then add all the hours from column I for the rows with word MAINTENANCE. After adding all those hours the summary would be displayed in cell D20. And then to do same for other types of jobs.

    I just simply haven't got a clue how to do that.

    I will be grateful for any help!
    Attached Files Attached Files
    Last edited by smarczak; 11-17-2017 at 02:54 AM.

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: TIMESHEET - time calculation for multiple jobs type

    Try Toggl.com - free workflow tracking and basic reporting in csv. Also has an app for your phone.

    Might be better than reinventing the wheel.

  3. #3
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: TIMESHEET - time calculation for multiple jobs type

    Hi smarczak

    Welcome to the Forum!!!
    Did you attach the correct File?

    I see no reference to this in your File...
    There are 5 job types MAINTENANCE, REPAIR, INSTALL, EXTRA WORK and OFFICE WORK and i would like automatically summarise time spent
    on each TYPE of job at the bottom in cells D20, D21, D22, D23, D24
    Or this...
    I think that the way to do that is to use a formula that would look for word MAINTENANCE in column J and then add all the hours from column I for the rows with word MAINTENANCE. After adding all those hours the summary would be displayed in cell D20. And then to do same for other types of jobs.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  4. #4
    Registered User
    Join Date
    11-16-2017
    Location
    England
    MS-Off Ver
    2013
    Posts
    9

    Re: TIMESHEET - time calculation for multiple jobs type

    Hi jaslake.

    No, I didn't. I'm sorry.

    Please see correct file attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-16-2017
    Location
    England
    MS-Off Ver
    2013
    Posts
    9
    Quote Originally Posted by kersplash View Post
    Try Toggl.com - free workflow tracking and basic reporting in csv. Also has an app for your phone.

    Might be better than reinventing the wheel.
    I've uploaded wrong file kersplash. Corrected in post #4
    I have been looking at templates from various sources but none of them uses formula I need and I can't figure this out on my own unfortunately.

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: TIMESHEET - time calculation for multiple jobs type

    is that what you want? (with PowerQuery add-in for Ex2013)
    Attached Files Attached Files
    Last edited by sandy666; 11-17-2017 at 03:39 AM.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,807

    Re: TIMESHEET - time calculation for multiple jobs type

    With formula.

    Try this formula in D20 and fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  8. #8
    Registered User
    Join Date
    11-16-2017
    Location
    England
    MS-Off Ver
    2013
    Posts
    9
    Quote Originally Posted by sandy666 View Post
    is that what you want? (with PowerQuery add-in for Ex2013)
    Thanks for that. I won't be able to test that till I get home but when I open it on my mobile it doesn't look right.
    Attached Images Attached Images

  9. #9
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: TIMESHEET - time calculation for multiple jobs type

    you need to set custom format on your mobile:
    for Duration: d.hh:mm:ss
    for Start/Finish: hh:mm

  10. #10
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: TIMESHEET - time calculation for multiple jobs type

    Hi smarczak

    Another Formula approach...

    2017-11-17_11-54-14.jpg

    Returns this...

    2017-11-17_12-02-38.jpg
    Last edited by jaslake; 11-17-2017 at 01:04 PM.

  11. #11
    Registered User
    Join Date
    11-16-2017
    Location
    England
    MS-Off Ver
    2013
    Posts
    9
    Quote Originally Posted by jaslake View Post
    Hi smarczak

    Another Formula approach...

    Attachment 548223

    Returns this...


    Attachment 548225
    Jaslake that looks exactly like what I was after.
    I'm so grateful for your help guys.

    I'm still not home unfortunately (long day) but I'm going to test it when back and report.

  12. #12
    Registered User
    Join Date
    11-16-2017
    Location
    England
    MS-Off Ver
    2013
    Posts
    9

    Re: TIMESHEET - time calculation for multiple jobs type

    I just wanted to say big thank you to everyone and

    BIGGER THANK YOU to jaslake

    the last formula was exactly what I was after and it does the job.

    thank you everyone!

  13. #13
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: TIMESHEET - time calculation for multiple jobs type

    You're welcome...glad I could help. Thanks for the Rep.

  14. #14
    Registered User
    Join Date
    11-16-2017
    Location
    England
    MS-Off Ver
    2013
    Posts
    9

    Re: TIMESHEET - time calculation for multiple jobs type

    again , thanks for help.

    Please see attached the Timesheet file.
    I will be using it on iphone with WPS Office hoping it will make my life and my workmates easier. I jutst hate paperwork

    Sheet is protected and password is 1234

    Everyone is welcome to use it and I would be glad if anyone can suggest any improvements or ideas.

    I wanted to make it as easy as possible and prevent mistakes so a lot of drop down lists in the cells.
    Attached Files Attached Files
    Last edited by smarczak; 11-19-2017 at 08:48 PM.

+ 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. Replies: 3
    Last Post: 09-13-2017, 10:08 AM
  2. Replies: 0
    Last Post: 09-03-2015, 07:31 AM
  3. Timesheet with multiple jobs, how to display time per job
    By breadbowl in forum Excel General
    Replies: 8
    Last Post: 07-03-2012, 07:57 AM
  4. Help with time sheet multiple jobs per day ?
    By skinn3y in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 03-22-2011, 05:31 AM
  5. Replies: 2
    Last Post: 01-05-2011, 06:41 PM
  6. [SOLVED] Time Calculation For A Timesheet To Include Lunch
    By poddys in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-03-2006, 04:10 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