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

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

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

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

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

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

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

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

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

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