Conditional formatting

  • Thread starter Thread starter Bobby
  • Start date Start date
B

Bobby

I can't figure this simple out.

my conditional format is in cell a2 =a2<a1+11 format pattern red

in cell a1 put 940 and in cell a2 put 950, now cell a2 will turn red ,
"type 951 in a2 and it turns white" back to 950 and red again which
is correct. a2 is less than 11 of cell a1, this works fine the
problem is change
cell a1 to 950 and cell a2 to 1000, again less than 11 but a2 doesn't
turn red.

Hope I worded this correct.
Thanks for reading this, and the help.
Bob
 
I can't figure this simple out.

my conditional format is in cell a2 =a2<a1+11 format pattern red

in cell a1 put 940 and in cell a2 put 950, now cell a2 will turn red ,
"type 951 in a2 and it turns white" back to 950 and red again which
is correct. a2 is less than 11 of cell a1, this works fine the
problem is change
cell a1 to 950 and cell a2 to 1000, again less than 11 but a2 doesn't
turn red.

Hope I worded this correct.
Thanks for reading this, and the help.
Bob
Click to expand...

a1=950
a1+11=961

a2 = 1000

so a2 NOT less than a1+11 so it should be white.

Your format is working as you programmed it.


--ron
 
Thanks
After you replyed I see my problem now, your right....
The reason I see it wrong is that 950 is 9:50 converted to 24hr
clock. The format is working correctly, but it's not what I really
want. I'm dealing with time, which Excel really needs to do better
with.
Thanks

Bob
 
Hi Bobby,
Excel works fine with times, but 950 is not a time in Excel.
See Chip Pearson's page on Quick Time Entry
http://www.cpearson.com/excel/DateTimeEntry.htm

BTW, if you want to find the difference in time going through midnight
B2: 20:00 C2: 5:30 C2: =C2-B2+(B2>C2)
Time is stored as a fraction of a day. B2>C2 is a logical expression
so either 0 is added or 1 is added.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: Sorting, Some notes on sorting in Excel
Location: http://www.mvps.org/dmcritchie/excel/sorting.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
If you format the fields as time, since that is what your dealing with
then it works fine.

Decimal:
1000 - 950 = 50

Time:
10:00 - 9:50 = 0:10

Excel does fine with time, you just need to have the formats correct.

To
 
Thanks for reading. Thought I had it solved, but failed again....

I still can't figure this problem out.
Column A is custom format as 0":"00
In cell a1 type 930
In cell a2 type 940 both cells should look like time 9:39 & 9:40
Cell a2 is conditional format as
Cell value is less that $a$1+11 and format pattern red.
With these values cell a2 will turn color which is correct.
Now change times cell a1 put 958 and cell a2 put 1008 only 10
Different but cell a2 doesn't change color. I understand
"I think" that cell a1 is only 958 of 999.
But then I formatted column as time as 13:30
And put the same numbers typing the colon is with the same
conditional format cell a2 doesn't change color.
I'm trying to get the lower cell to change color when it's 11 or less
of the cell above it, and I need the colon :
seems like when I go from 1 hour say 9:58 to say 10:08 is where the
condition fails to work.

Thanks for any help. Been working on this sheet for 5 weeks off and on
and this is the only & last problem I have with it.
Thanks for any help.
Bobby
 
In order to work with dates and time in Excel, you will have to
convert your integer numbers to date and time, and you should
be doing that as soon as you receive those numbers. Changing the
format with punctuation characters does not change them to
date and time. Please reread the links I provided.

If you don't convert them to Excel date and time you will be
going around and around in circles for both yourself and
anyone who tries to modify your programs.

You want to keep your conditional formatting formulas simple
because they have to be calculated often.

You can't add one hour to 958 (representing 9:58) working with
base 10 integers.
 
1st, thanks for the help.
I'm just missing something here, and still can't figure it out.
Let me ask this
Have 2 cells formatted as #":"00
Now I want to subtract 9:59 from 10:00 and come out with 1
959 and 1000 are times. I know that it has to be converted
but that's what I can't figure out. I've read all the pages that
refers to this but still stumped.
What is the formula? or is it that simple?
Converting to excel date & time is what I don't understand.
Hope this is the last.

Thanks - a - bunch
Bobby
 
Hi Bobby,

=(TRUNC(A1/100)+RIGHT(A1,2)/60)/24
format as h:mm or as hh:mm or as [h]:mm

But it would be a lot better to have converted the number
to a time when entered.

A date is the number of days past Dec 31, 1899
Time is a fraction of a day 9AM is .25 or 1/4 th of a day.

Read about converting so called 4 digit military time to Excel Time
Date and Time Entry
http://www.cpearson.con/excel/DateTimeEntry.htm

Read more about date and time at
http://www.cpearson.con/excel/datetime.htm
http://www.mvps.org/dmcritchie/excel/datetime.htm
 
Thanks Dave
I'll play with this.
I already tried military time, have the same problem, but I'll read
up on it again. The problem with that was once you get to say 0958
then 0959 the next is 0960 0961 0962 and so on till 0999 Right?


Bobby

Hi Bobby,

=(TRUNC(A1/100)+RIGHT(A1,2)/60)/24
format as h:mm or as hh:mm or as [h]:mm

But it would be a lot better to have converted the number
to a time when entered.

A date is the number of days past Dec 31, 1899
Time is a fraction of a day 9AM is .25 or 1/4 th of a day.

Read about converting so called 4 digit military time to Excel Time
Date and Time Entry
http://www.cpearson.con/excel/DateTimeEntry.htm

Read more about date and time at
http://www.cpearson.con/excel/datetime.htm
http://www.mvps.org/dmcritchie/excel/datetime.htm



Bobby said:
1st, thanks for the help.
I'm just missing something here, and still can't figure it out.
Let me ask this
Have 2 cells formatted as #":"00
Now I want to subtract 9:59 from 10:00 and come out with 1
959 and 1000 are times. I know that it has to be converted
but that's what I can't figure out. I've read all the pages that
refers to this but still stumped.
What is the formula? or is it that simple?
Converting to excel date & time is what I don't understand.
Hope this is the last.

Thanks - a - bunch
Bobby
Click to expand...
Click to expand...
 
It is the so called 4 digit military time that you already have and that
I am trying to get you away from.

Second part that is the problem that you have with your 4 digits they
are not recognized by Excel as time. If you want to understand how
time is indicated in Excel you will have to do some reading. What
we've already replied or what we've supplied as references, or even
the Excel help file.

column represent 4 digit military "time"
column B is the time derived form formula

MILT TIME TIME Formula used for column B and for Column C
0958 9:58 0.41528 =(TRUNC(A2/100)+RIGHT(A2,2)/60)/24
0959 9:59 0.41597 =(TRUNC(A3/100)+RIGHT(A3,2)/60)/24
0960 10:00 0.41667 =(TRUNC(A4/100)+RIGHT(A4,2)/60)/24
0961 10:01 0.41736 =(TRUNC(A5/100)+RIGHT(A5,2)/60)/24
0962 10:02 0.41806 =(TRUNC(A6/100)+RIGHT(A6,2)/60)/24
0963 10:03 0.41875 =(TRUNC(A7/100)+RIGHT(A7,2)/60)/24
0964 10:04 0.41944 =(TRUNC(A8/100)+RIGHT(A8,2)/60)/24

1158 11:58 0.49861 =(TRUNC(A10/100)+RIGHT(A10,2)/60)/24
1159 11:59 0.49931 =(TRUNC(A11/100)+RIGHT(A11,2)/60)/24
1160 12:00 0.50000 =(TRUNC(A12/100)+RIGHT(A12,2)/60)/24
1161 12:01 0.50069 =(TRUNC(A13/100)+RIGHT(A13,2)/60)/24

2358 23:58 0.99861 =(TRUNC(A15/100)+RIGHT(A15,2)/60)/24
2359 23:59 0.99931 =(TRUNC(A16/100)+RIGHT(A16,2)/60)/24
2360 0:00 1.00000 =(TRUNC(A17/100)+RIGHT(A17,2)/60)/24
2361 0:01 1.00069 =(TRUNC(A18/100)+RIGHT(A18,2)/60)/24
2362 0:02 1.00139 =(TRUNC(A19/100)+RIGHT(A19,2)/60)/24



Bobby said:
Thanks Dave
I'll play with this.
I already tried military time, have the same problem, but I'll read
up on it again. The problem with that was once you get to say 0958
then 0959 the next is 0960 0961 0962 and so on till 0999 Right?


Bobby

Hi Bobby,

=(TRUNC(A1/100)+RIGHT(A1,2)/60)/24
format as h:mm or as hh:mm or as [h]:mm

But it would be a lot better to have converted the number
to a time when entered.

A date is the number of days past Dec 31, 1899
Time is a fraction of a day 9AM is .25 or 1/4 th of a day.

Read about converting so called 4 digit military time to Excel Time
Date and Time Entry
http://www.cpearson.con/excel/DateTimeEntry.htm

Read more about date and time at
http://www.cpearson.con/excel/datetime.htm
http://www.mvps.org/dmcritchie/excel/datetime.htm
Click to expand...
Click to expand...
 
and the formatting is

A2: 0000
B2: h:mm
C2: _(* #,##0.00000_);_(* (#,##0.00000);_(* "-"??_);_(@_)
D2: General
 
Thanks Dave, I'm going to hit the books again and try to understand
this.
Bob

It is the so called 4 digit military time that you already have and that
I am trying to get you away from.

Second part that is the problem that you have with your 4 digits they
are not recognized by Excel as time. If you want to understand how
time is indicated in Excel you will have to do some reading. What
we've already replied or what we've supplied as references, or even
the Excel help file.

column represent 4 digit military "time"
column B is the time derived form formula

MILT TIME TIME Formula used for column B and for Column C
0958 9:58 0.41528 =(TRUNC(A2/100)+RIGHT(A2,2)/60)/24
0959 9:59 0.41597 =(TRUNC(A3/100)+RIGHT(A3,2)/60)/24
0960 10:00 0.41667 =(TRUNC(A4/100)+RIGHT(A4,2)/60)/24
0961 10:01 0.41736 =(TRUNC(A5/100)+RIGHT(A5,2)/60)/24
0962 10:02 0.41806 =(TRUNC(A6/100)+RIGHT(A6,2)/60)/24
0963 10:03 0.41875 =(TRUNC(A7/100)+RIGHT(A7,2)/60)/24
0964 10:04 0.41944 =(TRUNC(A8/100)+RIGHT(A8,2)/60)/24

1158 11:58 0.49861 =(TRUNC(A10/100)+RIGHT(A10,2)/60)/24
1159 11:59 0.49931 =(TRUNC(A11/100)+RIGHT(A11,2)/60)/24
1160 12:00 0.50000 =(TRUNC(A12/100)+RIGHT(A12,2)/60)/24
1161 12:01 0.50069 =(TRUNC(A13/100)+RIGHT(A13,2)/60)/24

2358 23:58 0.99861 =(TRUNC(A15/100)+RIGHT(A15,2)/60)/24
2359 23:59 0.99931 =(TRUNC(A16/100)+RIGHT(A16,2)/60)/24
2360 0:00 1.00000 =(TRUNC(A17/100)+RIGHT(A17,2)/60)/24
2361 0:01 1.00069 =(TRUNC(A18/100)+RIGHT(A18,2)/60)/24
2362 0:02 1.00139 =(TRUNC(A19/100)+RIGHT(A19,2)/60)/24



Bobby said:
Thanks Dave
I'll play with this.
I already tried military time, have the same problem, but I'll read
up on it again. The problem with that was once you get to say 0958
then 0959 the next is 0960 0961 0962 and so on till 0999 Right?


Bobby

Hi Bobby,

=(TRUNC(A1/100)+RIGHT(A1,2)/60)/24
format as h:mm or as hh:mm or as [h]:mm

But it would be a lot better to have converted the number
to a time when entered.

A date is the number of days past Dec 31, 1899
Time is a fraction of a day 9AM is .25 or 1/4 th of a day.

Read about converting so called 4 digit military time to Excel Time
Date and Time Entry
http://www.cpearson.con/excel/DateTimeEntry.htm

Read more about date and time at
http://www.cpearson.con/excel/datetime.htm
http://www.mvps.org/dmcritchie/excel/datetime.htm
Click to expand...
Click to expand...
Click to expand...
 
Dave & all
I finally going to give up at this, another 3 hours, reading and
trying and still stump.... the sheet I'm trying to do just isn't going
to work, lot of this time stuff just doesn't make any sense to me is
my problem and I'm not stupid, give me a car engine or a broken
computer and I can fix them, but subtracting time I can't...
5 weeks isn't all lost, I learned lot.

Thanks
Bob
 
Back
Top