+ Reply to Thread
Results 1 to 9 of 9

Timesheet with multiple jobs, how to display time per job

  1. #1
    Registered User
    Join Date
    07-02-2012
    Location
    Minneapolis, MN
    MS-Off Ver
    Excel 2011 for Mac version 14.2.2
    Posts
    3

    Timesheet with multiple jobs, how to display time per job

    I work on several jobs during the day, but I often start one, then work on another, then go back to the first one so I have multiple sessions of my time for the same Job.

    I have this:

    Job#, time in, time out, total time, hours

    jobA, 8:00 AM, 9:00 AM, 1:00, 1.00
    jobB, 9:00 AM, 10:00 AM, 1:00, 1.00
    jobA, 10:00 AM, 10:30 AM, 0:30, 0.50
    jobC, 10:30 AM, 11:00 AM, 0:30, 0.50
    jobB, 11:00 AM, 12:00 PM, 1:00, 1
    jobA, 1:00 PM, 4:30 PM, 3:30, 3.50

    I manually enter in the Job#, In, and Out.

    I want to display time spent per job:

    Job, hours
    A, 5
    B, 2
    C, 0.5

    So the hard part is to gather all the multiple sessions of the same job.

    I've attached a the weekly timesheet that I'm using, the part that I'm asking about is the lower section of each day. Currently I have to manually enter the job# and hours per session to get my hours per job. It would be great if the lower section would auto-fill. In fact I wouldn't even need the session section at all, just the job#, and Hours per Job.

    Any help would be greatly appreciated. Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Timesheet with multiple jobs, how to display time per job

    If you fix the Job# strings to match what you have in your Job column for the summary, you can use standard SUMIF formulas.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    07-02-2012
    Location
    Minneapolis, MN
    MS-Off Ver
    Excel 2011 for Mac version 14.2.2
    Posts
    3

    Re: Timesheet with multiple jobs, how to display time per job

    Sorry I'm not that smart. Also sorry but in the typed example above I forgot to make the Job# in the column and summary match, but they'll always match.
    The Job#s have to be whatever they are, they should always match themselves, sometimes the job is "Sicktime" or "CLIENT456-JOB5723" it'll be the same in the top column and in the summary.
    Which SUMIF formulas would work?

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Timesheet with multiple jobs, how to display time per job

    If you change the format of your sheet, you can use pivot table (or formula).

    I made an example for you.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Registered User
    Join Date
    07-02-2012
    Location
    Minneapolis, MN
    MS-Off Ver
    Excel 2011 for Mac version 14.2.2
    Posts
    3

    Re: Timesheet with multiple jobs, how to display time per job

    Pivot tables are interesting, but it appears to me that they are only good for at the end, once I already have all the info in. I would have to create a new pivot table at the end of each week, or whenever I added a new job number. Can pivot tables be somehow "Live" and automatically populated as I add data?

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Timesheet with multiple jobs, how to display time per job

    When you added new data on your table, you can choose:

    Excel 2007 => data => refresh.

    The new data are in the made pivottable.

    I advice you to format your data this way.

    Then you are more flexible (also with formula).

  7. #7
    Registered User
    Join Date
    07-02-2012
    Location
    Liverpool, England
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Timesheet with multiple jobs, how to display time per job

    Good evening breadbowl,

    I have been looking at your spreadsheet and I have nearly constructed a macro that will do what you need it to.

    This may be a bit long winded for what may be resolved with a quick formulae :os

    However, I have noticed that you're using Excel on a Mac, is there any limitation towards macros on a Mac?

    Just wanna check before I plod on :o)

    Many thanks,

    Ken

  8. #8
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Valrico, FL USA
    MS-Off Ver
    Excel 2016
    Posts
    358

    Re: Timesheet with multiple jobs, how to display time per job

    Try the attached workbook.
    Several helper columns were added then hidden on one sheet.
    The formulas were gathered from various sites.
    Attached Files Attached Files

  9. #9
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Timesheet with multiple jobs, how to display time per job

    Getting back to the suggestion made in post #2:

    you just need this simple formula in cell F40 and dragged down - then copied over to the other areas:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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