+ Reply to Thread
Results 1 to 5 of 5

Calculating time spent between seperate dates/times

  1. #1
    Registered User
    Join Date
    02-05-2014
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    3

    Unhappy Calculating time spent between seperate dates/times

    Hello all!

    First, Thanks for even looking at this. I am trying to learn this stuff on my own but just keep running into trouble lol.

    Background:
    Pretty much my job runs 8am - 5 pm M-F
    Iv been given the job of tracking time spent working on a project.

    My goal would be to calculate and log the following

    Work time should be calculated from Date & Time requested and Date & Time completed
    Ticket Lifetime should be calculated from Date & Time requested and Date & Time closed


    Attached is an example of the kind of data given for me to work with.

    Any guidance would mean the world.
    Attached Files Attached Files

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,537

    Re: Calculating time spent between seperate dates/times

    H3: =(D3+E3)-(B3+C3)
    I3: =(F3+G3)-(B3+C3)

    Put these formulas in and copy down.
    Some of the date formatting in the first few rows needs to be fixed.

    This will return a string of #'s if there is no completed/closed date & time. but that can be taken care of with an IF statement if need be.

    Does that work for you?

    EDIT: Forgot to say, custom format the cells in H and I as [hh]:mm too. This will stop the time looping back round once the total time passes 24 hrs.

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,537

    Re: Calculating time spent between seperate dates/times

    Here's your file with the formulas and formatting done.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-05-2014
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: Calculating time spent between seperate dates/times

    WOW!! i never dreamed of that fast of a reply!!

    BadlySpelledBuoy,

    Is there a way in excel to make it limit the hours counted by our business hours? It looks perfect, and on the same day lines This is EXACTLY what i needed.
    On the overlap days (row 4 for example) it looks like its all hours.

    I hope im making sense.

  5. #5
    Registered User
    Join Date
    02-05-2014
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    3

    Question Re: Calculating time spent between seperate dates/times

    OK,

    Following up on this,

    So after much study, I have moved to using the NetworkDays function and all that wonderfulness! lol

    Now, I think I am right on the verge of final success but have one last hurdle.



    So here we have the Start Date(B33) and Time(C33) and the Date Completed(D33) and Time completed(E33) and the result(H33)
    HTML Code: 
    B33             C33             D33             E33          H33
    02/07/14	12:08 PM	02/07/14	1:01 PM      21:12

    The problem is the answer should be 53 minutes in H33 not 21 hours.
    Any thoughts as to what Im doing wrong with the Code?

    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)

Similar Threads

  1. Calculating working hours between two dates/times!
    By JDGreen17 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-22-2013, 07:46 AM
  2. Replies: 6
    Last Post: 01-06-2013, 07:13 AM
  3. Replies: 0
    Last Post: 09-24-2012, 07:23 PM
  4. Calculating Hours between Dates and Times VBA
    By Crebsington in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2012, 03:34 AM
  5. NEED HELP - Calculating dates and times
    By Sandy in forum Excel General
    Replies: 5
    Last Post: 07-11-2005, 05: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