+ Reply to Thread
Results 1 to 4 of 4

excel formula for determining time difference is late or on time not calculating correctly

  1. #1
    Registered User
    Join Date
    07-17-2017
    Location
    New Jersey
    MS-Off Ver
    2013
    Posts
    56

    excel formula for determining time difference is late or on time not calculating correctly

    I have this excel sheet that I have been working with people on this website for the past couple months. I have made serious progress and there seems to be this glitch in the formula i am using. I am attaching an excel sheet withe formulas and the line from the excel sheet that isn't calculating correctly. The basics of this excel are this, I am taking two different times and dates and from that calculating the time difference between the two. Once I have done that I am determine based on a rule that if the time difference is over 12 hours then the item is late if its below 12 hours it is on time. This particular line I am having trouble with is under 12 hours yet it says its late. On top of that it is the only line in the entire excel sheet that has this issue. I have double check the formulas against other lines to make sure it matches and it does. The line with the issue is on the 4 tab of the excel the first page is a sample of what the formulas are doing.


    thank you in advance for anyone who can help me.
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,732

    Re: excel formula for determining time difference is late or on time not calculating corre

    20:00 - 6.43
    thats over 12 hours

    d1-j1
    sheet4
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    07-17-2017
    Location
    New Jersey
    MS-Off Ver
    2013
    Posts
    56

    Re: excel formula for determining time difference is late or on time not calculating corre

    yes sorry i didn't explain well the requested time and date is when something was submitted and the actual time and date is when it was actually completed. If the dates were the same but the actual time was earlier than the requested it is on time. even if the requested was submitted earlier than then the actual as long as it is less than 12 hour it is on time. only when the requested is earlier and the actual time is greater than 12 hours then it is late. For example the problem I have is the the requested date and actual date were the same. But the requested time was 8:00 PM but the actual time i was completed was 6 AM so that mean its on time because the the item was requested at but it was actually completed at 6 that same morning.

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,732

    Re: excel formula for determining time difference is late or on time not calculating corre

    i'm still not sure i understand

    are you saying if its the same day , then the 12 hours does not matter

    d1 = 20:00
    j1 = 06:00

    = 14hrs -
    late if not the same day
    ontime if the same day

    if d1 =8/10/17 20:00

    j1 = previous day (8/9/17) 23:59 = late
    ji = 00:01 same day (8/10/17) then = ontime

+ 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: 6
    Last Post: 05-20-2023, 01:07 AM
  2. Replies: 31
    Last Post: 07-28-2017, 01:48 PM
  3. Time difference calculating formula needed
    By nahidking in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-03-2015, 06:17 AM
  4. [SOLVED] Formula for calculating time difference between two cells
    By mprasanna in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-05-2015, 04:54 AM
  5. [SOLVED] Time formula not calculating correctly
    By brianfromla in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-19-2014, 11:12 AM
  6. [SOLVED] Calculating difference between 2 dates in excel 2003 - formula not working correctly
    By cf7046 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-03-2013, 10:18 PM
  7. Calculating time difference between two days capturing the overnight time.
    By Monica_La in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-08-2007, 11:22 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