Formula with 24 hour clock and calculating over 24 hours

**mcoda** · 10-24-2013, 05:17 PM

Good afternoon everyone.
Hi am having difficultly with this formula and I think it is probably easier than I am making it in my brain.
We essentially run jobs that start late at night. I need to calculate if the job met it's expected time....easy right?
so if the job ends at 1:26:55 and the expected is 1:30:00 it passes so the formula looks like this
=if(B2<=C2,"Pass","Fail")
however when the "clock" starts over I get an invalid function...for example
job ends at 23:59:10 and expected is 1:30:00 the same formula gives me a fail because it didn't recognize that is happened 2 hours early.
I need an IF or AND statement that helps it recognize that the job completed the day before....oh and to make it more fun I don't have dates...when the report was written it was just expected that all jobs would complete in the same 12 hour period.

Thanks in advance..
Michelle

**daddylonglegs** · 10-24-2013, 05:26 PM

Do you have a start time for the job or any other information?

**mcoda** · 10-24-2013, 05:28 PM

no...only Finish and expected finish

**daddylonglegs** · 10-24-2013, 05:36 PM

In that case I think you'd have to make some sort of assumption because time is, of course, circular so why can't 23:59 be after 01:30 (if it was 23:59 the next day)?

....but presumably that's unlikely - do all jobs start "late at night" - what time period might that be (could they start after midnight)? What's the latest that a job could finish?

**mcoda** · 10-24-2013, 06:02 PM

Hi again!
All the jobs start after 7pm and finish when they finish. usually between 5-12 hours

**daddylonglegs** · 10-24-2013, 06:14 PM

OK try using this formula

=IF(MOD(B2-19/24,1)<=MOD(C2-19/24,1),"Pass","Fail")

That will work for the examples you gave

**mcoda** · 10-25-2013, 09:22 AM

Great job....thanks for all your help

Formula with 24 hour clock and calculating over 24 hours

LinkBack

Thread Tools

Rate This Thread

Display

Formula with 24 hour clock and calculating over 24 hours

Re: Formula with 24 hour clock and calculating over 24 hours

Re: Formula with 24 hour clock and calculating over 24 hours

Re: Formula with 24 hour clock and calculating over 24 hours

Re: Formula with 24 hour clock and calculating over 24 hours

Re: Formula with 24 hour clock and calculating over 24 hours

Re: Formula with 24 hour clock and calculating over 24 hours

Thread Information

Users Browsing this Thread

Similar Threads

Calculating NETWORKINGDAYS in hours, subtracting lunch hour

[SOLVED] Calculating time on site from a 24 hour clock

Default time from 12 hour clock to 24 hour clock

24 hour clock calculating total hours using midnight

Calculating hours w/ ½ hour break

Bookmarks

Bookmarks

Posting Permissions