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

1. ## 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. ## Re: Positive/Negative Time Formula needed for calculating arrival before/after scheduled s

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

3. ## 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!

#### Thread Information

##### Users Browsing this Thread

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

#### 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