+ Reply to Thread
Results 1 to 12 of 12

Formula to calculate # of hours based on date range and employee ID code

  1. #1
    Registered User
    Join Date
    09-02-2014
    Location
    Florida
    MS-Off Ver
    MS Excel 10
    Posts
    5

    Formula to calculate # of hours based on date range and employee ID code

    Fellow EXCELers,

    Need help with a formula to calculate the nr of hours for an employee within a specified date range.

    Thanks much!

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,213

    Re: Formula to calculate # of hours based on date range and employee ID code

    Posting some data would be helpful. You are basically walking into a Mechanic and saying "There is a problem is with the car, thanks!" and then leaving.
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  3. #3
    Registered User
    Join Date
    09-02-2014
    Location
    Florida
    MS-Off Ver
    MS Excel 10
    Posts
    5

    Re: Formula to calculate # of hours based on date range and employee ID code

    New to the forum and not sure how to do that. Sorry!

  4. #4
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Formula to calculate # of hours based on date range and employee ID code

    Click on "Go Advanced" and then click on the paper clip icon at the top.

  5. #5
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,213

    Re: Formula to calculate # of hours based on date range and employee ID code

    Quote Originally Posted by r0man3 View Post
    New to the forum and not sure how to do that. Sorry!
    Actually, I was thinking even something like:


    "For example;
    Column A contains the ID number for an employee
    Column B contains a start time
    Column C contains an end time
    Column D contains the date of the shift.

    I wish to enter an employee ID into G1, a start time into F1, and an End Time in F2. I need a formula to calculate the number of hours worked by that employee between those two dates"


    Can you modify that or something? I mean your original question is similar to posting on here with "i have a question with a formula" and nothing else. It's so vague it would be impossible for someone to give you an accurate response. Type out some requirements, examples of your data, or go to the "go advanced" section and upload a sample file, help us to help you!
    Last edited by Speshul; 09-02-2014 at 12:32 PM.

  6. #6
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,213

    Re: Formula to calculate # of hours based on date range and employee ID code

    Double post

  7. #7
    Registered User
    Join Date
    09-02-2014
    Location
    Florida
    MS-Off Ver
    MS Excel 10
    Posts
    5

    Re: Formula to calculate # of hours based on date range and employee ID code

    I've attached a sample data of the file i have.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-02-2014
    Location
    Florida
    MS-Off Ver
    MS Excel 10
    Posts
    5

    Re: Formula to calculate # of hours based on date range and employee ID code

    BTW, column A is the employee ID, B is the nr. of hours worked, C is the date

  9. #9
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,213

    Re: Formula to calculate # of hours based on date range and employee ID code

    Enter:

    Employee ID into F2
    Start Date into F3
    End Date into F4

    This formula into F5:

    =IFERROR(SUMPRODUCT(($A$1:$A$19=$F$2)*$B$1:$B$19*(F3<=C1:C19)*(F4>=C1:C19)),"Not Found")

    Let me know if this works for you.

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to calculate # of hours based on date range and employee ID code

    Why the IFERROR?

    Here's another one...

    =SUMIFS(B1:B19,A1:A19,F2,C1:C19,">="&F3,C1:C19,"<="&F4)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  11. #11
    Registered User
    Join Date
    09-02-2014
    Location
    Florida
    MS-Off Ver
    MS Excel 10
    Posts
    5

    Re: Formula to calculate # of hours based on date range and employee ID code

    It worked like a charm!! Thanks much!!

  12. #12
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to calculate # of hours based on date range and employee ID code

    You're welcome. We appreciate the feedback!

    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ 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. Calculate employee utilization based on billed hours
    By simrag01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-11-2014, 04:48 PM
  2. Replies: 1
    Last Post: 06-10-2014, 10:43 PM
  3. [SOLVED] VBA Code to calculate utilization of an Employee using Start & End Date
    By nickydharia in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-08-2014, 10:30 AM
  4. Find and calculate employee hours within date range
    By rockytop80 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-14-2011, 11:43 AM
  5. [SOLVED] Calculate employee hours for employee evaluation?
    By Triesha in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-09-2006, 10:55 AM

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