+ Reply to Thread
Results 1 to 3 of 3

Positive/Negative Time Formula needed for calculating arrival before/after scheduled shift

  1. #1
    Registered User
    Join Date
    08-10-2015
    Location
    Sarasota, FL
    MS-Off Ver
    2010
    Posts
    63

    Exclamation Positive/Negative Time Formula needed for calculating arrival before/after scheduled shift

    I have to determine how much time before or after the scheduled shift someone clocked in.


    A B C D
    1 Acutal Clock In Scheduled Clock In Difference
    2 8:36:41 AM 8:30:00 am ########
    3 7:55:09 PM 8:00:00 PM 0:04

    I have thousands of lines that I need to do this for.....that I will need to re-do every month to thousands of lines of new data

    If I do b1-c1 I get a number when the clock in is before the scheduled I get a number (row 2), but I get ###### if the clock in is past the scheduled (row 1), when it should return 0:06.

    I'm looking for a formula (if then statement maybe?) that I can copy down almost 2000 rows.

    I'm also doing conditional formatting for the row to highlight when the answer is >=0:15; so if I end up with a negative number I will have to figure out how to change the conditional formatting to +- 0:15

    Thank you in advance,

    CHill

  2. #2
    Forum Contributor
    Join Date
    07-13-2015
    Location
    Quebec, Canada
    MS-Off Ver
    2013
    Posts
    232

    Re: Positive/Negative Time Formula needed for calculating arrival before/after scheduled s

    Have you tried simply doing =ABS(Formula)?
    =ABS(B1-C1)

  3. #3
    Registered User
    Join Date
    08-10-2015
    Location
    Sarasota, FL
    MS-Off Ver
    2010
    Posts
    63

    Re: Positive/Negative Time Formula needed for calculating arrival before/after scheduled s

    I knew it had to be simple. I had tried the =MOD(Formula), but didn't even go to ABS.

    Thank you so very much Jdevil - that worked beautifully!

+ 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 number of patients by arrival time (hr)
    By alowry in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-30-2013, 02:33 PM
  2. need one formula for returning positive or negative time w/out #######
    By completelyhis in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-11-2013, 09:12 AM
  3. Calculating arrival time
    By rshaw1655 in forum Excel General
    Replies: 2
    Last Post: 09-26-2011, 06:54 PM
  4. Replies: 1
    Last Post: 11-20-2008, 01:52 AM
  5. [SOLVED] calculating positive and negative formulas
    By cliford in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM

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