Time Formula

[Guest]

Hi All,

I'm hoping that someone can help me with this formula

I have a process (D14) then the Start time (G14), End time (H14) and Pass or
Fail (I4), if the specific process takes longer than it should do then it's a
fail (the formula works for this). The time's are formatted dd/mm/yyyy
hh:mm:ss

=IF(OR(D14="process1",D14="process2",D14="process3",D14="process4",IF(H14<=G14+"01:00:00","Pass","Fail"),IF(OR(D14="process5",D14="process6"),IF(H14<=G14+"23:59:59","Pass","Fail")))

The problem is this is....for example "process7" must be completed by 2pm
(14:00) on the same it was started.
Can someone please tell me what formula I need to make this work and how can
I other process with different finishing times?

I hope that I have explained this ok.

Thanks in advance,

Gav.

 

[Bob Phillips]

=IF(OR(D14="process1",D14="process2",D14="process3",D14="process4"),IF(H14<=G14+"01:00:00","Pass","Fail"),
IF(OR(D14="process5",D14="process6"),IF(H14<=G14+"23:59:59","Pass","Fail"),
IF(AND(D14="process7",DAY(H14)=DAY(G14),HOUR(H14)<=14),"Pass","Fail")))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Great it worked, thanks Bob your a star...

Gav.

=IF(OR(D14="process1",D14="process2",D14="process3",D14="process4"),IF(H14<=G14+"01:00:00","Pass","Fail"),
IF(OR(D14="process5",D14="process6"),IF(H14<=G14+"23:59:59","Pass","Fail"),
IF(AND(D14="process7",DAY(H14)=DAY(G14),HOUR(H14)<=14),"Pass","Fail")))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Sorry Bob, I Spoke too soon...

When I enter the time in H14 as 14:01 I'm getting the result as Pass when it
should be a Fail as it is after 14:00.

Any more ideas??

Thanks,

Gav.

 

[Bob Phillips]

Sorry, sill mistake

=IF(OR(D14="process1",D14="process2",D14="process3",D14="process4"),IF(H14<=G14+"01:00:00","Pass","Fail"),
IF(OR(D14="process5",D14="process6"),IF(H14<=G14+"23:59:59","Pass","Fail"),
IF(AND(D14="process7",DAY(H14)=DAY(G14),HOUR(H14)<=13),"Pass","Fail")))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Bob,

Sorry to say but that's not working either...