Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting comparing times
I need to compare a current production release time (that will vary between
regions) to previous months release times by region. If the difference is 29 or greater shad the cell blue. If the difference is -29 or greater shad the cell red. I have tried these approaches in the but get inconsisted results. Condition 1 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*144029 Condition 2 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440<-29 The cells are formatted h:mm AM/PM and I need to keep this format for reporting purposes. Appreciate any help. Thanks, -- Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting comparing times
Based on your statements, change your comparison symbols.
Condition 1 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440=29 Condition 2 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440<=-29 I'm also assuming you meant to shade the cell red when its -29 or LESS, not greater. If this isn't what you were looking for, could you explain more what is going wrong? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Mike" wrote: I need to compare a current production release time (that will vary between regions) to previous months release times by region. If the difference is 29 or greater shad the cell blue. If the difference is -29 or greater shad the cell red. I have tried these approaches in the but get inconsisted results. Condition 1 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*144029 Condition 2 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440<-29 The cells are formatted h:mm AM/PM and I need to keep this format for reporting purposes. Appreciate any help. Thanks, -- Mike |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting comparing times
Hi
I am not sure how you can arrive ot < 29 if you formatted into h:mm the maximum for a day is 24hrs. Would you provide a sample data table and your expected result HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: I need to compare a current production release time (that will vary between regions) to previous months release times by region. If the difference is 29 or greater shad the cell blue. If the difference is -29 or greater shad the cell red. I have tried these approaches in the but get inconsisted results. Condition 1 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*144029 Condition 2 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440<-29 The cells are formatted h:mm AM/PM and I need to keep this format for reporting purposes. Appreciate any help. Thanks, -- Mike |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting comparing times
Note that when you say greater than 29, XL takes that to mean 29 DAYS. For
instance, if you input 1:23 into XL, that's actually a decimal number of about 0.058 So, for OP, if your wanting to find a difference of 29 HOURS, adjust your formula to be =(D5-B5)*14401.208 and =(D5-B5)*1440<-1.208 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "xlmate" wrote: Hi I am not sure how you can arrive ot < 29 if you formatted into h:mm the maximum for a day is 24hrs. Would you provide a sample data table and your expected result HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: I need to compare a current production release time (that will vary between regions) to previous months release times by region. If the difference is 29 or greater shad the cell blue. If the difference is -29 or greater shad the cell red. I have tried these approaches in the but get inconsisted results. Condition 1 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*144029 Condition 2 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440<-29 The cells are formatted h:mm AM/PM and I need to keep this format for reporting purposes. Appreciate any help. Thanks, -- Mike |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting comparing times
Thanks for responding. Still is not working so here is a further explanation.
Current Time is 6:20 AM and Previous Time is 5:44 AM. In this scenario, the current time is <=-29 minutes after the previous time, so I want to shad the previous time blue. Another scenario, current time 6:20 AM, previous time 6:51 AM. In this case the current time is =29 minutes before the previous time and shad will be red. I have tried to convert the times into minutes (D5-B5)*1440=29. This works inconsistently. I can manually look at the times and shad them but I am trying to automate as much as possible. Thanks again and appreciate any help. -- Mike "Luke M" wrote: Based on your statements, change your comparison symbols. Condition 1 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440=29 Condition 2 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440<=-29 I'm also assuming you meant to shade the cell red when its -29 or LESS, not greater. If this isn't what you were looking for, could you explain more what is going wrong? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Mike" wrote: I need to compare a current production release time (that will vary between regions) to previous months release times by region. If the difference is 29 or greater shad the cell blue. If the difference is -29 or greater shad the cell red. I have tried these approaches in the but get inconsisted results. Condition 1 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*144029 Condition 2 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440<-29 The cells are formatted h:mm AM/PM and I need to keep this format for reporting purposes. Appreciate any help. Thanks, -- Mike |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting comparing times
Thanks for responding. Still is not working so here is a further explanation.
Current Time is 6:20 AM and Previous Time is 5:44 AM. In this scenario, the current time is <=-29 minutes after the previous time, so I want to shad the previous time blue. Another scenario, current time 6:20 AM, previous time 6:51 AM. In this case the current time is =29 minutes before the previous time and shad will be red. I have tried to convert the times into minutes (D5-B5)*1440=29. This works inconsistently. I can manually look at the times and shad them but I am trying to automate as much as possible. Thanks again and appreciate any help. -- Mike "xlmate" wrote: Hi I am not sure how you can arrive ot < 29 if you formatted into h:mm the maximum for a day is 24hrs. Would you provide a sample data table and your expected result HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: I need to compare a current production release time (that will vary between regions) to previous months release times by region. If the difference is 29 or greater shad the cell blue. If the difference is -29 or greater shad the cell red. I have tried these approaches in the but get inconsisted results. Condition 1 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*144029 Condition 2 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440<-29 The cells are formatted h:mm AM/PM and I need to keep this format for reporting purposes. Appreciate any help. Thanks, -- Mike |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting comparing times
One suggestion,
Instead of having your formula in the Conditional Formatting, why not have your formula (D5-B5)*1440 in in E5 conditional formatting the column for value if it is <29 or 29 Highlight Column E Go to Conditional Formatting Under Condition 1 : Choose Cell Value Is then select greater than or equal to in the next box type 29 in the last box. Click the Add button for the next condition which is <=-29 Repeat the same for Condition 2 HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: Thanks for responding. Still is not working so here is a further explanation. Current Time is 6:20 AM and Previous Time is 5:44 AM. In this scenario, the current time is <=-29 minutes after the previous time, so I want to shad the previous time blue. Another scenario, current time 6:20 AM, previous time 6:51 AM. In this case the current time is =29 minutes before the previous time and shad will be red. I have tried to convert the times into minutes (D5-B5)*1440=29. This works inconsistently. I can manually look at the times and shad them but I am trying to automate as much as possible. Thanks again and appreciate any help. -- Mike "xlmate" wrote: Hi I am not sure how you can arrive ot < 29 if you formatted into h:mm the maximum for a day is 24hrs. Would you provide a sample data table and your expected result HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: I need to compare a current production release time (that will vary between regions) to previous months release times by region. If the difference is 29 or greater shad the cell blue. If the difference is -29 or greater shad the cell red. I have tried these approaches in the but get inconsisted results. Condition 1 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*144029 Condition 2 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440<-29 The cells are formatted h:mm AM/PM and I need to keep this format for reporting purposes. Appreciate any help. Thanks, -- Mike |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting comparing times
One suggestion,
instead of having your formula in the Conditional Formatting, why not place the formula =(D5-B5)*1440 in cell eg. E5, and copy down Then go to Condirional Formatting In Condition 1 : 1) choose Cell Value Is, 2) select greater than or equal to 3) type 29 4) Click Format and select your format and color, click Ok 5) Click Add button for the next condition Repeat the same for condition of <=29 HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: Thanks for responding. Still is not working so here is a further explanation. Current Time is 6:20 AM and Previous Time is 5:44 AM. In this scenario, the current time is <=-29 minutes after the previous time, so I want to shad the previous time blue. Another scenario, current time 6:20 AM, previous time 6:51 AM. In this case the current time is =29 minutes before the previous time and shad will be red. I have tried to convert the times into minutes (D5-B5)*1440=29. This works inconsistently. I can manually look at the times and shad them but I am trying to automate as much as possible. Thanks again and appreciate any help. -- Mike "xlmate" wrote: Hi I am not sure how you can arrive ot < 29 if you formatted into h:mm the maximum for a day is 24hrs. Would you provide a sample data table and your expected result HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: I need to compare a current production release time (that will vary between regions) to previous months release times by region. If the difference is 29 or greater shad the cell blue. If the difference is -29 or greater shad the cell red. I have tried these approaches in the but get inconsisted results. Condition 1 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*144029 Condition 2 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440<-29 The cells are formatted h:mm AM/PM and I need to keep this format for reporting purposes. Appreciate any help. Thanks, -- Mike |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting comparing times
All the suggestions everyone has given are good. But, the situation I have
seems to be a little more complicated, let me explain. The two cells I am comparing are linked to another worksheet, so I cannot copy down otherwise I lose the link. Cell A is 3:01 AM, B is 2:14 AM in this case cell B is =29 minutes before cell A and cell B needs to be shaded an unique color. If cell B is <=29 after cell A then shad then cell B is shaded a different color. Both Cell A and B are linked to another worksheet so I cannot put a formual in them and I need to use Conidtional Formatting. But I cannot get it to work. All you suggestions do work with different situations. Any clue on my particular need? Thanks again. -- Mike "xlmate" wrote: One suggestion, instead of having your formula in the Conditional Formatting, why not place the formula =(D5-B5)*1440 in cell eg. E5, and copy down Then go to Condirional Formatting In Condition 1 : 1) choose Cell Value Is, 2) select greater than or equal to 3) type 29 4) Click Format and select your format and color, click Ok 5) Click Add button for the next condition Repeat the same for condition of <=29 HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: Thanks for responding. Still is not working so here is a further explanation. Current Time is 6:20 AM and Previous Time is 5:44 AM. In this scenario, the current time is <=-29 minutes after the previous time, so I want to shad the previous time blue. Another scenario, current time 6:20 AM, previous time 6:51 AM. In this case the current time is =29 minutes before the previous time and shad will be red. I have tried to convert the times into minutes (D5-B5)*1440=29. This works inconsistently. I can manually look at the times and shad them but I am trying to automate as much as possible. Thanks again and appreciate any help. -- Mike "xlmate" wrote: Hi I am not sure how you can arrive ot < 29 if you formatted into h:mm the maximum for a day is 24hrs. Would you provide a sample data table and your expected result HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: I need to compare a current production release time (that will vary between regions) to previous months release times by region. If the difference is 29 or greater shad the cell blue. If the difference is -29 or greater shad the cell red. I have tried these approaches in the but get inconsisted results. Condition 1 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*144029 Condition 2 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440<-29 The cells are formatted h:mm AM/PM and I need to keep this format for reporting purposes. Appreciate any help. Thanks, -- Mike |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting comparing times
It's no good repeatedly saying "This works inconsistently" but not
responding to the questions which ask you to tell us what the data values are in cases where you say it doesn't work. Tell us what CF formula you are using [copy and paste from your CF "Formula Is" dialogue box to this newsgroup], what values are in the cells that are referred to in that formula, what format you got from the CF and what you expected. If you have also tried the use of a helper formula in an adjacent cell, such as the suggestion of =(D5-B5)*1440, then please also tell us what formula you used, what result you got, and what result you expected there. It also isn't clear what you meant when you said "The two cells I am comparing are linked to another worksheet, so I cannot copy down otherwise I lose the link." What formulae do you have in those cells, and what can't you copy down? The suggestions which you have been given so far are perfectly sensible, and people are not going to be able to help you sort out where you have gone wrong unless you give them more information. -- David Biddulph "Mike" wrote in message ... All the suggestions everyone has given are good. But, the situation I have seems to be a little more complicated, let me explain. The two cells I am comparing are linked to another worksheet, so I cannot copy down otherwise I lose the link. Cell A is 3:01 AM, B is 2:14 AM in this case cell B is =29 minutes before cell A and cell B needs to be shaded an unique color. If cell B is <=29 after cell A then shad then cell B is shaded a different color. Both Cell A and B are linked to another worksheet so I cannot put a formual in them and I need to use Conidtional Formatting. But I cannot get it to work. All you suggestions do work with different situations. Any clue on my particular need? Thanks again. -- Mike "xlmate" wrote: One suggestion, instead of having your formula in the Conditional Formatting, why not place the formula =(D5-B5)*1440 in cell eg. E5, and copy down Then go to Condirional Formatting In Condition 1 : 1) choose Cell Value Is, 2) select greater than or equal to 3) type 29 4) Click Format and select your format and color, click Ok 5) Click Add button for the next condition Repeat the same for condition of <=29 HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: Thanks for responding. Still is not working so here is a further explanation. Current Time is 6:20 AM and Previous Time is 5:44 AM. In this scenario, the current time is <=-29 minutes after the previous time, so I want to shad the previous time blue. Another scenario, current time 6:20 AM, previous time 6:51 AM. In this case the current time is =29 minutes before the previous time and shad will be red. I have tried to convert the times into minutes (D5-B5)*1440=29. This works inconsistently. I can manually look at the times and shad them but I am trying to automate as much as possible. Thanks again and appreciate any help. -- Mike "xlmate" wrote: Hi I am not sure how you can arrive ot < 29 if you formatted into h:mm the maximum for a day is 24hrs. Would you provide a sample data table and your expected result HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: I need to compare a current production release time (that will vary between regions) to previous months release times by region. If the difference is 29 or greater shad the cell blue. If the difference is -29 or greater shad the cell red. I have tried these approaches in the but get inconsisted results. Condition 1 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*144029 Condition 2 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440<-29 The cells are formatted h:mm AM/PM and I need to keep this format for reporting purposes. Appreciate any help. Thanks, -- Mike |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting comparing times
Thanks David,
Here is some further information that may help. I do appreciate your interest. The value of each cell comes from a different worksheet (ex: ='Reptg Data'!AD150, ='Reptg Data'!AP150, etc, etc.). So if I put a formula into the receiving cell, the link to Reptg Data is lost. Here is an example of data and the result I am looking for: E11 = 3:01 AM F11 = 1:14 AM B11 = 2:14 AM B11 is the current processing end time E11 is a processing end time from the prior month F11 is a processing end time from three months ago In this example, I need to maintain the times above for reporting purposes but also need to color code E11 and F11 when the current processing time (B11) is either 30 minutes before or after E11 or F11. The result here should be E11 will be a unique color (call it blue) because B11 is 30 minutes before E11,,,,and F11 will also be a unique color call it red (different than E11) because B11 is 30 minutes after F11. Some of the Conditional Formatting formulas I have attempted a Condition 1 =TEXT(E11-B11,"h:mm")*1440=30 result should be blue Condition 2 =TEXT(E11-B11,"h:mm")*1440<=-30 result should be no shading Condition 1 =TEXT(F11-B11,"h:mm")*1440=30 result should be no shading Condition 2 =TEXT(F11-B11,"h:mm")*1440<=-30 (negative 30) result should be red Condition 1 =(E11-B11)*1440=30 result should be blue Condition 2 =(E11-B11)*1440<=-30 result should be no shading Condition 1 =(F11-B11)*1440=30 result should be no shading Condition 2 =(F11-B11)*1440<=-30 result should red (I have also tried =) Condition 1 =MINUTE(E11)-MINUTE(B11)=30 result should be blue Condition 2 = MINUTE(E11)-MINUTE(B11)<=-30 result should be no shading Condition 1 =MINUTE(F11)-MINUTE(B11)=30 result should be no shading Condition 2 =MINUTE(F11)-MINUTE(B11)<=-30 result should be red I have also tried putting 30 and -30 in there own cells. Hope this helps. -- Mike "David Biddulph" wrote: It's no good repeatedly saying "This works inconsistently" but not responding to the questions which ask you to tell us what the data values are in cases where you say it doesn't work. Tell us what CF formula you are using [copy and paste from your CF "Formula Is" dialogue box to this newsgroup], what values are in the cells that are referred to in that formula, what format you got from the CF and what you expected. If you have also tried the use of a helper formula in an adjacent cell, such as the suggestion of =(D5-B5)*1440, then please also tell us what formula you used, what result you got, and what result you expected there. It also isn't clear what you meant when you said "The two cells I am comparing are linked to another worksheet, so I cannot copy down otherwise I lose the link." What formulae do you have in those cells, and what can't you copy down? The suggestions which you have been given so far are perfectly sensible, and people are not going to be able to help you sort out where you have gone wrong unless you give them more information. -- David Biddulph "Mike" wrote in message ... All the suggestions everyone has given are good. But, the situation I have seems to be a little more complicated, let me explain. The two cells I am comparing are linked to another worksheet, so I cannot copy down otherwise I lose the link. Cell A is 3:01 AM, B is 2:14 AM in this case cell B is =29 minutes before cell A and cell B needs to be shaded an unique color. If cell B is <=29 after cell A then shad then cell B is shaded a different color. Both Cell A and B are linked to another worksheet so I cannot put a formual in them and I need to use Conidtional Formatting. But I cannot get it to work. All you suggestions do work with different situations. Any clue on my particular need? Thanks again. -- Mike "xlmate" wrote: One suggestion, instead of having your formula in the Conditional Formatting, why not place the formula =(D5-B5)*1440 in cell eg. E5, and copy down Then go to Condirional Formatting In Condition 1 : 1) choose Cell Value Is, 2) select greater than or equal to 3) type 29 4) Click Format and select your format and color, click Ok 5) Click Add button for the next condition Repeat the same for condition of <=29 HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: Thanks for responding. Still is not working so here is a further explanation. Current Time is 6:20 AM and Previous Time is 5:44 AM. In this scenario, the current time is <=-29 minutes after the previous time, so I want to shad the previous time blue. Another scenario, current time 6:20 AM, previous time 6:51 AM. In this case the current time is =29 minutes before the previous time and shad will be red. I have tried to convert the times into minutes (D5-B5)*1440=29. This works inconsistently. I can manually look at the times and shad them but I am trying to automate as much as possible. Thanks again and appreciate any help. -- Mike "xlmate" wrote: Hi I am not sure how you can arrive ot < 29 if you formatted into h:mm the maximum for a day is 24hrs. Would you provide a sample data table and your expected result HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: I need to compare a current production release time (that will vary between regions) to previous months release times by region. If the difference is 29 or greater shad the cell blue. If the difference is -29 or greater shad the cell red. I have tried these approaches in the but get inconsisted results. Condition 1 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*144029 Condition 2 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440<-29 The cells are formatted h:mm AM/PM and I need to keep this format for reporting purposes. Appreciate any help. Thanks, -- Mike |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting comparing times
You still haven't explained to us which cells and CF conditions return the
result you expected and which didn't. =(E11-B11)*1440=30 does indeed get satisfied and shades the cell blue. [You don't need the TEXT function.] If it doesn't shade the cell for you, what does the formula =(E11-B11)*1440 return for you. It should return 47. The =(F11-B11)*1440=30 condition is NOT satisfied. The =(F11-B11)*1440<-30 IS satisfied. Again, if it isn't doing what you think it ought to be doing, tell us what =(F11-B11)*1440 returns; it should be -60. I don't know what you are trying to achieve with your formulae like =MINUTE(E11)-MINUTE(B11)=30 , but you can similarly check whether =MINUTE(E11)-MINUTE(B11) returns -13 and whether =MINUTE(F11)-MINUTE(B11) returns zero. In all these cases where we've talked of using these formulae for diagnostics, format the result of the diagnostic formula as number or general, not as time. I don't understand what you are trying to say when you said: "The value of each cell comes from a different worksheet (ex: ='Reptg Data'!AD150, ='Reptg Data'!AP150, etc, etc.). So if I put a formula into the receiving cell, the link to Reptg Data is lost." You have a formula there which links to Reptg Data. Where is your problem? -- David Biddulph Mike wrote: Thanks David, Here is some further information that may help. I do appreciate your interest. The value of each cell comes from a different worksheet (ex: ='Reptg Data'!AD150, ='Reptg Data'!AP150, etc, etc.). So if I put a formula into the receiving cell, the link to Reptg Data is lost. Here is an example of data and the result I am looking for: E11 = 3:01 AM F11 = 1:14 AM B11 = 2:14 AM B11 is the current processing end time E11 is a processing end time from the prior month F11 is a processing end time from three months ago In this example, I need to maintain the times above for reporting purposes but also need to color code E11 and F11 when the current processing time (B11) is either 30 minutes before or after E11 or F11. The result here should be E11 will be a unique color (call it blue) because B11 is 30 minutes before E11,,,,and F11 will also be a unique color call it red (different than E11) because B11 is 30 minutes after F11. Some of the Conditional Formatting formulas I have attempted a Condition 1 =TEXT(E11-B11,"h:mm")*1440=30 result should be blue Condition 2 =TEXT(E11-B11,"h:mm")*1440<=-30 result should be no shading Condition 1 =TEXT(F11-B11,"h:mm")*1440=30 result should be no shading Condition 2 =TEXT(F11-B11,"h:mm")*1440<=-30 (negative 30) result should be red Condition 1 =(E11-B11)*1440=30 result should be blue Condition 2 =(E11-B11)*1440<=-30 result should be no shading Condition 1 =(F11-B11)*1440=30 result should be no shading Condition 2 =(F11-B11)*1440<=-30 result should red (I have also tried =) Condition 1 =MINUTE(E11)-MINUTE(B11)=30 result should be blue Condition 2 = MINUTE(E11)-MINUTE(B11)<=-30 result should be no shading Condition 1 =MINUTE(F11)-MINUTE(B11)=30 result should be no shading Condition 2 =MINUTE(F11)-MINUTE(B11)<=-30 result should be red I have also tried putting 30 and -30 in there own cells. Hope this helps. It's no good repeatedly saying "This works inconsistently" but not responding to the questions which ask you to tell us what the data values are in cases where you say it doesn't work. Tell us what CF formula you are using [copy and paste from your CF "Formula Is" dialogue box to this newsgroup], what values are in the cells that are referred to in that formula, what format you got from the CF and what you expected. If you have also tried the use of a helper formula in an adjacent cell, such as the suggestion of =(D5-B5)*1440, then please also tell us what formula you used, what result you got, and what result you expected there. It also isn't clear what you meant when you said "The two cells I am comparing are linked to another worksheet, so I cannot copy down otherwise I lose the link." What formulae do you have in those cells, and what can't you copy down? The suggestions which you have been given so far are perfectly sensible, and people are not going to be able to help you sort out where you have gone wrong unless you give them more information. -- David Biddulph "Mike" wrote in message ... All the suggestions everyone has given are good. But, the situation I have seems to be a little more complicated, let me explain. The two cells I am comparing are linked to another worksheet, so I cannot copy down otherwise I lose the link. Cell A is 3:01 AM, B is 2:14 AM in this case cell B is =29 minutes before cell A and cell B needs to be shaded an unique color. If cell B is <=29 after cell A then shad then cell B is shaded a different color. Both Cell A and B are linked to another worksheet so I cannot put a formual in them and I need to use Conidtional Formatting. But I cannot get it to work. All you suggestions do work with different situations. Any clue on my particular need? Thanks again. -- Mike "xlmate" wrote: One suggestion, instead of having your formula in the Conditional Formatting, why not place the formula =(D5-B5)*1440 in cell eg. E5, and copy down Then go to Condirional Formatting In Condition 1 : 1) choose Cell Value Is, 2) select greater than or equal to 3) type 29 4) Click Format and select your format and color, click Ok 5) Click Add button for the next condition Repeat the same for condition of <=29 HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: Thanks for responding. Still is not working so here is a further explanation. Current Time is 6:20 AM and Previous Time is 5:44 AM. In this scenario, the current time is <=-29 minutes after the previous time, so I want to shad the previous time blue. Another scenario, current time 6:20 AM, previous time 6:51 AM. In this case the current time is =29 minutes before the previous time and shad will be red. I have tried to convert the times into minutes (D5-B5)*1440=29. This works inconsistently. I can manually look at the times and shad them but I am trying to automate as much as possible. Thanks again and appreciate any help. -- Mike "xlmate" wrote: Hi I am not sure how you can arrive ot < 29 if you formatted into h:mm the maximum for a day is 24hrs. Would you provide a sample data table and your expected result HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: I need to compare a current production release time (that will vary between regions) to previous months release times by region. If the difference is 29 or greater shad the cell blue. If the difference is -29 or greater shad the cell red. I have tried these approaches in the but get inconsisted results. Condition 1 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*144029 Condition 2 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440<-29 The cells are formatted h:mm AM/PM and I need to keep this format for reporting purposes. Appreciate any help. Thanks, -- Mike |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting comparing times
David,
Once again thanks for your help. I do have it working now. My formula =(E11-B11)*1440=30 was correct all along but what I found out through further analysis is that the two times being compared had been entered differently, one was enter as 2:02 the other entered as 26:02. When a time is enter as 26:02. Excel formats it as 1/1/1900 2:02:00 AM, when a time is entered as 2:02 Excel formats it as 2:02:00 AM. Consequently, after I entered both times as 2:02 the formula worked. What I have to work with is processing end times from all regions across the US with multiple processing centers and time zones. Now that I see the format was inconsistent, all is working...thanks again. Mike "David Biddulph" wrote: You still haven't explained to us which cells and CF conditions return the result you expected and which didn't. =(E11-B11)*1440=30 does indeed get satisfied and shades the cell blue. [You don't need the TEXT function.] If it doesn't shade the cell for you, what does the formula =(E11-B11)*1440 return for you. It should return 47. The =(F11-B11)*1440=30 condition is NOT satisfied. The =(F11-B11)*1440<-30 IS satisfied. Again, if it isn't doing what you think it ought to be doing, tell us what =(F11-B11)*1440 returns; it should be -60. I don't know what you are trying to achieve with your formulae like =MINUTE(E11)-MINUTE(B11)=30 , but you can similarly check whether =MINUTE(E11)-MINUTE(B11) returns -13 and whether =MINUTE(F11)-MINUTE(B11) returns zero. In all these cases where we've talked of using these formulae for diagnostics, format the result of the diagnostic formula as number or general, not as time. I don't understand what you are trying to say when you said: "The value of each cell comes from a different worksheet (ex: ='Reptg Data'!AD150, ='Reptg Data'!AP150, etc, etc.). So if I put a formula into the receiving cell, the link to Reptg Data is lost." You have a formula there which links to Reptg Data. Where is your problem? -- David Biddulph Mike wrote: Thanks David, Here is some further information that may help. I do appreciate your interest. The value of each cell comes from a different worksheet (ex: ='Reptg Data'!AD150, ='Reptg Data'!AP150, etc, etc.). So if I put a formula into the receiving cell, the link to Reptg Data is lost. Here is an example of data and the result I am looking for: E11 = 3:01 AM F11 = 1:14 AM B11 = 2:14 AM B11 is the current processing end time E11 is a processing end time from the prior month F11 is a processing end time from three months ago In this example, I need to maintain the times above for reporting purposes but also need to color code E11 and F11 when the current processing time (B11) is either 30 minutes before or after E11 or F11. The result here should be E11 will be a unique color (call it blue) because B11 is 30 minutes before E11,,,,and F11 will also be a unique color call it red (different than E11) because B11 is 30 minutes after F11. Some of the Conditional Formatting formulas I have attempted a Condition 1 =TEXT(E11-B11,"h:mm")*1440=30 result should be blue Condition 2 =TEXT(E11-B11,"h:mm")*1440<=-30 result should be no shading Condition 1 =TEXT(F11-B11,"h:mm")*1440=30 result should be no shading Condition 2 =TEXT(F11-B11,"h:mm")*1440<=-30 (negative 30) result should be red Condition 1 =(E11-B11)*1440=30 result should be blue Condition 2 =(E11-B11)*1440<=-30 result should be no shading Condition 1 =(F11-B11)*1440=30 result should be no shading Condition 2 =(F11-B11)*1440<=-30 result should red (I have also tried =) Condition 1 =MINUTE(E11)-MINUTE(B11)=30 result should be blue Condition 2 = MINUTE(E11)-MINUTE(B11)<=-30 result should be no shading Condition 1 =MINUTE(F11)-MINUTE(B11)=30 result should be no shading Condition 2 =MINUTE(F11)-MINUTE(B11)<=-30 result should be red I have also tried putting 30 and -30 in there own cells. Hope this helps. It's no good repeatedly saying "This works inconsistently" but not responding to the questions which ask you to tell us what the data values are in cases where you say it doesn't work. Tell us what CF formula you are using [copy and paste from your CF "Formula Is" dialogue box to this newsgroup], what values are in the cells that are referred to in that formula, what format you got from the CF and what you expected. If you have also tried the use of a helper formula in an adjacent cell, such as the suggestion of =(D5-B5)*1440, then please also tell us what formula you used, what result you got, and what result you expected there. It also isn't clear what you meant when you said "The two cells I am comparing are linked to another worksheet, so I cannot copy down otherwise I lose the link." What formulae do you have in those cells, and what can't you copy down? The suggestions which you have been given so far are perfectly sensible, and people are not going to be able to help you sort out where you have gone wrong unless you give them more information. -- David Biddulph "Mike" wrote in message ... All the suggestions everyone has given are good. But, the situation I have seems to be a little more complicated, let me explain. The two cells I am comparing are linked to another worksheet, so I cannot copy down otherwise I lose the link. Cell A is 3:01 AM, B is 2:14 AM in this case cell B is =29 minutes before cell A and cell B needs to be shaded an unique color. If cell B is <=29 after cell A then shad then cell B is shaded a different color. Both Cell A and B are linked to another worksheet so I cannot put a formual in them and I need to use Conidtional Formatting. But I cannot get it to work. All you suggestions do work with different situations. Any clue on my particular need? Thanks again. -- Mike "xlmate" wrote: One suggestion, instead of having your formula in the Conditional Formatting, why not place the formula =(D5-B5)*1440 in cell eg. E5, and copy down Then go to Condirional Formatting In Condition 1 : 1) choose Cell Value Is, 2) select greater than or equal to 3) type 29 4) Click Format and select your format and color, click Ok 5) Click Add button for the next condition Repeat the same for condition of <=29 HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: Thanks for responding. Still is not working so here is a further explanation. Current Time is 6:20 AM and Previous Time is 5:44 AM. In this scenario, the current time is <=-29 minutes after the previous time, so I want to shad the previous time blue. Another scenario, current time 6:20 AM, previous time 6:51 AM. In this case the current time is =29 minutes before the previous time and shad will be red. I have tried to convert the times into minutes (D5-B5)*1440=29. This works inconsistently. I can manually look at the times and shad them but I am trying to automate as much as possible. Thanks again and appreciate any help. -- Mike "xlmate" wrote: Hi I am not sure how you can arrive ot < 29 if you formatted into h:mm the maximum for a day is 24hrs. Would you provide a sample data table and your expected result HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: I need to compare a current production release time (that will vary between regions) to previous months release times by region. If the difference is 29 or greater shad the cell blue. If the difference is -29 or greater shad the cell red. I have tried these approaches in the but get inconsisted results. Condition 1 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*144029 Condition 2 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440<-29 The cells are formatted h:mm AM/PM and I need to keep this format for reporting purposes. Appreciate any help. Thanks, -- Mike |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting comparing times
I did wonder whether you might have whole days floating about, which is why
I had been asking for the values that formulae such as =(E11-B11)*1440 produced, as this would have made it obvious. If you wanted to ignore whole days (or to cope with time intervals going past midnight), you might have considered changing your =(E11-B11)*1440=30 to =MOD(E11-B11,1)*1440=30 -- David Biddulph Mike wrote: David, Once again thanks for your help. I do have it working now. My formula =(E11-B11)*1440=30 was correct all along but what I found out through further analysis is that the two times being compared had been entered differently, one was enter as 2:02 the other entered as 26:02. When a time is enter as 26:02. Excel formats it as 1/1/1900 2:02:00 AM, when a time is entered as 2:02 Excel formats it as 2:02:00 AM. Consequently, after I entered both times as 2:02 the formula worked. What I have to work with is processing end times from all regions across the US with multiple processing centers and time zones. Now that I see the format was inconsistent, all is working...thanks again. Mike "David Biddulph" wrote: You still haven't explained to us which cells and CF conditions return the result you expected and which didn't. =(E11-B11)*1440=30 does indeed get satisfied and shades the cell blue. [You don't need the TEXT function.] If it doesn't shade the cell for you, what does the formula =(E11-B11)*1440 return for you. It should return 47. The =(F11-B11)*1440=30 condition is NOT satisfied. The =(F11-B11)*1440<-30 IS satisfied. Again, if it isn't doing what you think it ought to be doing, tell us what =(F11-B11)*1440 returns; it should be -60. I don't know what you are trying to achieve with your formulae like =MINUTE(E11)-MINUTE(B11)=30 , but you can similarly check whether =MINUTE(E11)-MINUTE(B11) returns -13 and whether =MINUTE(F11)-MINUTE(B11) returns zero. In all these cases where we've talked of using these formulae for diagnostics, format the result of the diagnostic formula as number or general, not as time. I don't understand what you are trying to say when you said: "The value of each cell comes from a different worksheet (ex: ='Reptg Data'!AD150, ='Reptg Data'!AP150, etc, etc.). So if I put a formula into the receiving cell, the link to Reptg Data is lost." You have a formula there which links to Reptg Data. Where is your problem? -- David Biddulph Mike wrote: Thanks David, Here is some further information that may help. I do appreciate your interest. The value of each cell comes from a different worksheet (ex: ='Reptg Data'!AD150, ='Reptg Data'!AP150, etc, etc.). So if I put a formula into the receiving cell, the link to Reptg Data is lost. Here is an example of data and the result I am looking for: E11 = 3:01 AM F11 = 1:14 AM B11 = 2:14 AM B11 is the current processing end time E11 is a processing end time from the prior month F11 is a processing end time from three months ago In this example, I need to maintain the times above for reporting purposes but also need to color code E11 and F11 when the current processing time (B11) is either 30 minutes before or after E11 or F11. The result here should be E11 will be a unique color (call it blue) because B11 is 30 minutes before E11,,,,and F11 will also be a unique color call it red (different than E11) because B11 is 30 minutes after F11. Some of the Conditional Formatting formulas I have attempted a Condition 1 =TEXT(E11-B11,"h:mm")*1440=30 result should be blue Condition 2 =TEXT(E11-B11,"h:mm")*1440<=-30 result should be no shading Condition 1 =TEXT(F11-B11,"h:mm")*1440=30 result should be no shading Condition 2 =TEXT(F11-B11,"h:mm")*1440<=-30 (negative 30) result should be red Condition 1 =(E11-B11)*1440=30 result should be blue Condition 2 =(E11-B11)*1440<=-30 result should be no shading Condition 1 =(F11-B11)*1440=30 result should be no shading Condition 2 =(F11-B11)*1440<=-30 result should red (I have also tried =) Condition 1 =MINUTE(E11)-MINUTE(B11)=30 result should be blue Condition 2 = MINUTE(E11)-MINUTE(B11)<=-30 result should be no shading Condition 1 =MINUTE(F11)-MINUTE(B11)=30 result should be no shading Condition 2 =MINUTE(F11)-MINUTE(B11)<=-30 result should be red I have also tried putting 30 and -30 in there own cells. Hope this helps. It's no good repeatedly saying "This works inconsistently" but not responding to the questions which ask you to tell us what the data values are in cases where you say it doesn't work. Tell us what CF formula you are using [copy and paste from your CF "Formula Is" dialogue box to this newsgroup], what values are in the cells that are referred to in that formula, what format you got from the CF and what you expected. If you have also tried the use of a helper formula in an adjacent cell, such as the suggestion of =(D5-B5)*1440, then please also tell us what formula you used, what result you got, and what result you expected there. It also isn't clear what you meant when you said "The two cells I am comparing are linked to another worksheet, so I cannot copy down otherwise I lose the link." What formulae do you have in those cells, and what can't you copy down? The suggestions which you have been given so far are perfectly sensible, and people are not going to be able to help you sort out where you have gone wrong unless you give them more information. -- David Biddulph "Mike" wrote in message ... All the suggestions everyone has given are good. But, the situation I have seems to be a little more complicated, let me explain. The two cells I am comparing are linked to another worksheet, so I cannot copy down otherwise I lose the link. Cell A is 3:01 AM, B is 2:14 AM in this case cell B is =29 minutes before cell A and cell B needs to be shaded an unique color. If cell B is <=29 after cell A then shad then cell B is shaded a different color. Both Cell A and B are linked to another worksheet so I cannot put a formual in them and I need to use Conidtional Formatting. But I cannot get it to work. All you suggestions do work with different situations. Any clue on my particular need? Thanks again. -- Mike "xlmate" wrote: One suggestion, instead of having your formula in the Conditional Formatting, why not place the formula =(D5-B5)*1440 in cell eg. E5, and copy down Then go to Condirional Formatting In Condition 1 : 1) choose Cell Value Is, 2) select greater than or equal to 3) type 29 4) Click Format and select your format and color, click Ok 5) Click Add button for the next condition Repeat the same for condition of <=29 HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: Thanks for responding. Still is not working so here is a further explanation. Current Time is 6:20 AM and Previous Time is 5:44 AM. In this scenario, the current time is <=-29 minutes after the previous time, so I want to shad the previous time blue. Another scenario, current time 6:20 AM, previous time 6:51 AM. In this case the current time is =29 minutes before the previous time and shad will be red. I have tried to convert the times into minutes (D5-B5)*1440=29. This works inconsistently. I can manually look at the times and shad them but I am trying to automate as much as possible. Thanks again and appreciate any help. -- Mike "xlmate" wrote: Hi I am not sure how you can arrive ot < 29 if you formatted into h:mm the maximum for a day is 24hrs. Would you provide a sample data table and your expected result HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: I need to compare a current production release time (that will vary between regions) to previous months release times by region. If the difference is 29 or greater shad the cell blue. If the difference is -29 or greater shad the cell red. I have tried these approaches in the but get inconsisted results. Condition 1 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*144029 Condition 2 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440<-29 The cells are formatted h:mm AM/PM and I need to keep this format for reporting purposes. Appreciate any help. Thanks, -- Mike |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting comparing times
ok,,,I will give the =MOD(E11-B11,1)*1440=30 a try.....thanks again for your
help.... -- Mike "David Biddulph" wrote: I did wonder whether you might have whole days floating about, which is why I had been asking for the values that formulae such as =(E11-B11)*1440 produced, as this would have made it obvious. If you wanted to ignore whole days (or to cope with time intervals going past midnight), you might have considered changing your =(E11-B11)*1440=30 to =MOD(E11-B11,1)*1440=30 -- David Biddulph Mike wrote: David, Once again thanks for your help. I do have it working now. My formula =(E11-B11)*1440=30 was correct all along but what I found out through further analysis is that the two times being compared had been entered differently, one was enter as 2:02 the other entered as 26:02. When a time is enter as 26:02. Excel formats it as 1/1/1900 2:02:00 AM, when a time is entered as 2:02 Excel formats it as 2:02:00 AM. Consequently, after I entered both times as 2:02 the formula worked. What I have to work with is processing end times from all regions across the US with multiple processing centers and time zones. Now that I see the format was inconsistent, all is working...thanks again. Mike "David Biddulph" wrote: You still haven't explained to us which cells and CF conditions return the result you expected and which didn't. =(E11-B11)*1440=30 does indeed get satisfied and shades the cell blue. [You don't need the TEXT function.] If it doesn't shade the cell for you, what does the formula =(E11-B11)*1440 return for you. It should return 47. The =(F11-B11)*1440=30 condition is NOT satisfied. The =(F11-B11)*1440<-30 IS satisfied. Again, if it isn't doing what you think it ought to be doing, tell us what =(F11-B11)*1440 returns; it should be -60. I don't know what you are trying to achieve with your formulae like =MINUTE(E11)-MINUTE(B11)=30 , but you can similarly check whether =MINUTE(E11)-MINUTE(B11) returns -13 and whether =MINUTE(F11)-MINUTE(B11) returns zero. In all these cases where we've talked of using these formulae for diagnostics, format the result of the diagnostic formula as number or general, not as time. I don't understand what you are trying to say when you said: "The value of each cell comes from a different worksheet (ex: ='Reptg Data'!AD150, ='Reptg Data'!AP150, etc, etc.). So if I put a formula into the receiving cell, the link to Reptg Data is lost." You have a formula there which links to Reptg Data. Where is your problem? -- David Biddulph Mike wrote: Thanks David, Here is some further information that may help. I do appreciate your interest. The value of each cell comes from a different worksheet (ex: ='Reptg Data'!AD150, ='Reptg Data'!AP150, etc, etc.). So if I put a formula into the receiving cell, the link to Reptg Data is lost. Here is an example of data and the result I am looking for: E11 = 3:01 AM F11 = 1:14 AM B11 = 2:14 AM B11 is the current processing end time E11 is a processing end time from the prior month F11 is a processing end time from three months ago In this example, I need to maintain the times above for reporting purposes but also need to color code E11 and F11 when the current processing time (B11) is either 30 minutes before or after E11 or F11. The result here should be E11 will be a unique color (call it blue) because B11 is 30 minutes before E11,,,,and F11 will also be a unique color call it red (different than E11) because B11 is 30 minutes after F11. Some of the Conditional Formatting formulas I have attempted a Condition 1 =TEXT(E11-B11,"h:mm")*1440=30 result should be blue Condition 2 =TEXT(E11-B11,"h:mm")*1440<=-30 result should be no shading Condition 1 =TEXT(F11-B11,"h:mm")*1440=30 result should be no shading Condition 2 =TEXT(F11-B11,"h:mm")*1440<=-30 (negative 30) result should be red Condition 1 =(E11-B11)*1440=30 result should be blue Condition 2 =(E11-B11)*1440<=-30 result should be no shading Condition 1 =(F11-B11)*1440=30 result should be no shading Condition 2 =(F11-B11)*1440<=-30 result should red (I have also tried =) Condition 1 =MINUTE(E11)-MINUTE(B11)=30 result should be blue Condition 2 = MINUTE(E11)-MINUTE(B11)<=-30 result should be no shading Condition 1 =MINUTE(F11)-MINUTE(B11)=30 result should be no shading Condition 2 =MINUTE(F11)-MINUTE(B11)<=-30 result should be red I have also tried putting 30 and -30 in there own cells. Hope this helps. It's no good repeatedly saying "This works inconsistently" but not responding to the questions which ask you to tell us what the data values are in cases where you say it doesn't work. Tell us what CF formula you are using [copy and paste from your CF "Formula Is" dialogue box to this newsgroup], what values are in the cells that are referred to in that formula, what format you got from the CF and what you expected. If you have also tried the use of a helper formula in an adjacent cell, such as the suggestion of =(D5-B5)*1440, then please also tell us what formula you used, what result you got, and what result you expected there. It also isn't clear what you meant when you said "The two cells I am comparing are linked to another worksheet, so I cannot copy down otherwise I lose the link." What formulae do you have in those cells, and what can't you copy down? The suggestions which you have been given so far are perfectly sensible, and people are not going to be able to help you sort out where you have gone wrong unless you give them more information. -- David Biddulph "Mike" wrote in message ... All the suggestions everyone has given are good. But, the situation I have seems to be a little more complicated, let me explain. The two cells I am comparing are linked to another worksheet, so I cannot copy down otherwise I lose the link. Cell A is 3:01 AM, B is 2:14 AM in this case cell B is =29 minutes before cell A and cell B needs to be shaded an unique color. If cell B is <=29 after cell A then shad then cell B is shaded a different color. Both Cell A and B are linked to another worksheet so I cannot put a formual in them and I need to use Conidtional Formatting. But I cannot get it to work. All you suggestions do work with different situations. Any clue on my particular need? Thanks again. -- Mike "xlmate" wrote: One suggestion, instead of having your formula in the Conditional Formatting, why not place the formula =(D5-B5)*1440 in cell eg. E5, and copy down Then go to Condirional Formatting In Condition 1 : 1) choose Cell Value Is, 2) select greater than or equal to 3) type 29 4) Click Format and select your format and color, click Ok 5) Click Add button for the next condition Repeat the same for condition of <=29 HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: Thanks for responding. Still is not working so here is a further explanation. Current Time is 6:20 AM and Previous Time is 5:44 AM. In this scenario, the current time is <=-29 minutes after the previous time, so I want to shad the previous time blue. Another scenario, current time 6:20 AM, previous time 6:51 AM. In this case the current time is =29 minutes before the previous time and shad will be red. I have tried to convert the times into minutes (D5-B5)*1440=29. This works inconsistently. I can manually look at the times and shad them but I am trying to automate as much as possible. Thanks again and appreciate any help. -- Mike "xlmate" wrote: Hi I am not sure how you can arrive ot < 29 if you formatted into h:mm the maximum for a day is 24hrs. Would you provide a sample data table and your expected result HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Mike" wrote: I need to compare a current production release time (that will vary between regions) to previous months release times by region. If the difference is 29 or greater shad the cell blue. If the difference is -29 or greater shad the cell red. I have tried these approaches in the but get inconsisted results. Condition 1 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*144029 Condition 2 Formula is where D5 is the previous release time and B5 is the current. =(D5-B5)*1440<-29 The cells are formatted h:mm AM/PM and I need to keep this format for reporting purposes. Appreciate any help. Thanks, -- Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting for times | Excel Discussion (Misc queries) | |||
Comparing two lists using Conditional Formatting | Excel Worksheet Functions | |||
Conditional formatting/ comparing worksheets | Excel Discussion (Misc queries) | |||
Conditional Formatting-Comparing Two Columns | Excel Discussion (Misc queries) | |||
Conditional Formatting-Comparing Two Columns | Excel Discussion (Misc queries) |