| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Formula
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","Fa il"),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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time Formula
=IF(OR(D14="process1",D14="process2",D14="process3 ",D14="process4"),IF(H14<=G14+"01:00:00","Pass","F ail"),
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) "Gav123" wrote in message ... 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","Fa il"),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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time Formula
Great it worked, thanks Bob your a star...
Gav. "Bob Phillips" wrote: =IF(OR(D14="process1",D14="process2",D14="process3 ",D14="process4"),IF(H14<=G14+"01:00:00","Pass","F ail"), 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) "Gav123" wrote in message ... 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","Fa il"),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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time Formula
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" wrote: =IF(OR(D14="process1",D14="process2",D14="process3 ",D14="process4"),IF(H14<=G14+"01:00:00","Pass","F ail"), 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) "Gav123" wrote in message ... 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","Fa il"),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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time Formula
Sorry, sill mistake
=IF(OR(D14="process1",D14="process2",D14="process3 ",D14="process4"),IF(H14<=G14+"01:00:00","Pass","F ail"), 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) "Gav123" wrote in message ... 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" wrote: =IF(OR(D14="process1",D14="process2",D14="process3 ",D14="process4"),IF(H14<=G14+"01:00:00","Pass","F ail"), 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) "Gav123" wrote in message ... 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","Fa il"),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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time Formula
Bob,
Sorry to say but that's not working either... "Bob Phillips" wrote: Sorry, sill mistake =IF(OR(D14="process1",D14="process2",D14="process3 ",D14="process4"),IF(H14<=G14+"01:00:00","Pass","F ail"), 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) "Gav123" wrote in message ... 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" wrote: =IF(OR(D14="process1",D14="process2",D14="process3 ",D14="process4"),IF(H14<=G14+"01:00:00","Pass","F ail"), 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) "Gav123" wrote in message ... 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","Fa il"),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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to find Stop Time from Start Time and Total Minutes | Excel Worksheet Functions | |||
| template or formula for start time -finish time -total hours ple | New Users to Excel | |||
| Help with time formula so the time will not change. | Excel Discussion (Misc queries) | |||
| How do I set up a formula on a time sheet to calculate time in 1/. | Excel Discussion (Misc queries) | |||
| Time / Formula to look at time difference | Excel Worksheet Functions | |||
Linear Mode
