Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Using A Time Figure As The Trigger
Greeting,
I have a spreadsheet where I need to high light rows which have a time value of 0:00 in column B. Column B has many time figures and a few empty cells. I am interested only in those cells with the value of "0:00" (12:00 o'clock midnight) How do I detect this time value in column B and what would a CF formula look like to do this? Any help will be greatly appreciated, thanks. -Minitman |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Using A Time Figure As The Trigger
try using =0 in the Conditional formatting
"Minitman" wrote: Greeting, I have a spreadsheet where I need to high light rows which have a time value of 0:00 in column B. Column B has many time figures and a few empty cells. I am interested only in those cells with the value of "0:00" (12:00 o'clock midnight) How do I detect this time value in column B and what would a CF formula look like to do this? Any help will be greatly appreciated, thanks. -Minitman |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Using A Time Figure As The Trigger
=AND($B1=0,$B1<"")
"Minitman" wrote: Greeting, I have a spreadsheet where I need to high light rows which have a time value of 0:00 in column B. Column B has many time figures and a few empty cells. I am interested only in those cells with the value of "0:00" (12:00 o'clock midnight) How do I detect this time value in column B and what would a CF formula look like to do this? Any help will be greatly appreciated, thanks. -Minitman |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Using A Time Figure As The Trigger
Thanks for the quick and helpful replies.
12:00 o'clock being equal to "0" is what I was missing. And thank you Teethless mama for the code, it works great. -Minitman On Thu, 27 Nov 2008 08:15:01 -0800, Teethless mama wrote: =AND($B1=0,$B1<"") "Minitman" wrote: Greeting, I have a spreadsheet where I need to high light rows which have a time value of 0:00 in column B. Column B has many time figures and a few empty cells. I am interested only in those cells with the value of "0:00" (12:00 o'clock midnight) How do I detect this time value in column B and what would a CF formula look like to do this? Any help will be greatly appreciated, thanks. -Minitman |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Using A Time Figure As The Trigger
I spoke to soon!!
The code works if there is something in B (which is what it is supposed to do). However, if there is nothing in B, then it fires as well (which it is NOT supposed to do). Any ideas as to how to fix this bug? Any help is appreciated/ -Minitman On Thu, 27 Nov 2008 10:54:52 -0600, Minitman wrote: Thanks for the quick and helpful replies. 12:00 o'clock being equal to "0" is what I was missing. And thank you Teethless mama for the code, it works great. -Minitman On Thu, 27 Nov 2008 08:15:01 -0800, Teethless mama wrote: =AND($B1=0,$B1<"") "Minitman" wrote: Greeting, I have a spreadsheet where I need to high light rows which have a time value of 0:00 in column B. Column B has many time figures and a few empty cells. I am interested only in those cells with the value of "0:00" (12:00 o'clock midnight) How do I detect this time value in column B and what would a CF formula look like to do this? Any help will be greatly appreciated, thanks. -Minitman |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Using A Time Figure As The Trigger
Firstly check that you've got TM's formula correctly, and make sure that
you've used CF's "Formula Is", not "Cell Value Is". Secondly, are you sure that B1 is really empty when you say it is? What does =LEN(B1) say? Perhaps you've got one or more spaces in the cell? -- David Biddulph "Minitman" wrote in message ... I spoke to soon!! The code works if there is something in B (which is what it is supposed to do). However, if there is nothing in B, then it fires as well (which it is NOT supposed to do). Any ideas as to how to fix this bug? Any help is appreciated/ -Minitman On Thu, 27 Nov 2008 10:54:52 -0600, Minitman wrote: Thanks for the quick and helpful replies. 12:00 o'clock being equal to "0" is what I was missing. And thank you Teethless mama for the code, it works great. -Minitman On Thu, 27 Nov 2008 08:15:01 -0800, Teethless mama wrote: =AND($B1=0,$B1<"") "Minitman" wrote: Greeting, I have a spreadsheet where I need to high light rows which have a time value of 0:00 in column B. Column B has many time figures and a few empty cells. I am interested only in those cells with the value of "0:00" (12:00 o'clock midnight) How do I detect this time value in column B and what would a CF formula look like to do this? Any help will be greatly appreciated, thanks. -Minitman |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Using A Time Figure As The Trigger
Hey David,
Thanks for the reply. In response to your questions: 1st - I copied and pasted it into the CF. I had to make a couple of adjustments (first row is 2 not 1) other wise just as it was written. And I used the formula is in the CF. 2nd - I tried putting a space into B2 and that untriggered the CF. I then plugged in your =LEN(B2) and it returned 0 if there was nothing in B2 and the CF was triggered. I put in the number 0 into B2 and the CF stayed triggered. I then removed the 0 from B2 and put in a space like you mentioned. That got a response, the CF untriggered back to default (no color if there is anything other then a zero). So why is the CF Triggering on both 0 and ""??? I thought that B2<"" would exclude the empty condition. I guess not. It seems that the only work around is to NOT let that row be empty (if empty, put a space character into the offending cell so it will not be empty). -Minitman On Thu, 27 Nov 2008 19:23:38 -0000, "David Biddulph" <groups [at] biddulph.org.uk wrote: Firstly check that you've got TM's formula correctly, and make sure that you've used CF's "Formula Is", not "Cell Value Is". Secondly, are you sure that B1 is really empty when you say it is? What does =LEN(B1) say? Perhaps you've got one or more spaces in the cell? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Using A Time Figure As The Trigger
Hi Minitman
Just for you to know Midnight is 12.00 am not pm so maybe this is what is giving you problems.Check it out HTH John "Minitman" wrote in message ... Hey David, Thanks for the reply. In response to your questions: 1st - I copied and pasted it into the CF. I had to make a couple of adjustments (first row is 2 not 1) other wise just as it was written. And I used the formula is in the CF. 2nd - I tried putting a space into B2 and that untriggered the CF. I then plugged in your =LEN(B2) and it returned 0 if there was nothing in B2 and the CF was triggered. I put in the number 0 into B2 and the CF stayed triggered. I then removed the 0 from B2 and put in a space like you mentioned. That got a response, the CF untriggered back to default (no color if there is anything other then a zero). So why is the CF Triggering on both 0 and ""??? I thought that B2<"" would exclude the empty condition. I guess not. It seems that the only work around is to NOT let that row be empty (if empty, put a space character into the offending cell so it will not be empty). -Minitman On Thu, 27 Nov 2008 19:23:38 -0000, "David Biddulph" <groups [at] biddulph.org.uk wrote: Firstly check that you've got TM's formula correctly, and make sure that you've used CF's "Formula Is", not "Cell Value Is". Secondly, are you sure that B1 is really empty when you say it is? What does =LEN(B1) say? Perhaps you've got one or more spaces in the cell? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Using A Time Figure As The Trigger
What you are saying doesn't make sense.
You say: "I tried putting a space into B2 and that untriggered the CF. I then plugged in your =LEN(B2) and it returned 0 if there was nothing in B2 and the CF was triggered" Where did you put the =LEN(B2)? How could it return 0 if you had a space in B2, and how could putting =LEN(B2) in another cell affect the CF which is dependent only on the content of B2? To go back to your original question, which I think was saying that you think the CF is being triggered when you believe B2 is empty: Firstly, please copy the formula from your CF Formula Is condition and paste it here into your newsgroup reply. Don't try to retype. While you are at it, please check that you've only got the once condition set. Secondly, what does =LEN(B2) [in another cell] return when you think B2 is empty and when you think that the CF has been triggered? I can assure you that the CF formula suggested *does* work. With =AND($B2=0,$B2<"") as the condition, it will trigger when B2 contains zero, but not when the cell is empty, or contains a space or anything non zero, or when it has a formula returning an empty string "". -- David Biddulph "Minitman" wrote in message ... Hey David, Thanks for the reply. In response to your questions: 1st - I copied and pasted it into the CF. I had to make a couple of adjustments (first row is 2 not 1) other wise just as it was written. And I used the formula is in the CF. 2nd - I tried putting a space into B2 and that untriggered the CF. I then plugged in your =LEN(B2) and it returned 0 if there was nothing in B2 and the CF was triggered. I put in the number 0 into B2 and the CF stayed triggered. I then removed the 0 from B2 and put in a space like you mentioned. That got a response, the CF untriggered back to default (no color if there is anything other then a zero). So why is the CF Triggering on both 0 and ""??? I thought that B2<"" would exclude the empty condition. I guess not. It seems that the only work around is to NOT let that row be empty (if empty, put a space character into the offending cell so it will not be empty). -Minitman On Thu, 27 Nov 2008 19:23:38 -0000, "David Biddulph" <groups [at] biddulph.org.uk wrote: Firstly check that you've got TM's formula correctly, and make sure that you've used CF's "Formula Is", not "Cell Value Is". Secondly, are you sure that B1 is really empty when you say it is? What does =LEN(B1) say? Perhaps you've got one or more spaces in the cell? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Using A Time Figure As The Trigger
Hey David,
My apologizes for not being clear. I plugged =LEN(B2) into C2 as a tester cell. The CF formula in each cell of row 2 is: =AND($B2=0, $B2<"""") When this formula triggers, it turns each cell blue. Column B is formatted in the 24 hour time format. A 0 in B2 is converted to 12:00 o'clock midnight Which is what I need). An empty cell ("") at B2 should NOT also trigger the CF, but it does. As for the space character, it will act like any other character and NOT trigger the CF. What I don't under stand is, with the AND operator, don't both conditions have to be true before the CF triggers? It seems like the CF is treating zero (0) and empty ("") as the same character??? How is this possible? -Minitman On Fri, 28 Nov 2008 01:51:15 -0000, "David Biddulph" <groups [at] biddulph.org.uk wrote: What you are saying doesn't make sense. You say: "I tried putting a space into B2 and that untriggered the CF. I then plugged in your =LEN(B2) and it returned 0 if there was nothing in B2 and the CF was triggered" Where did you put the =LEN(B2)? How could it return 0 if you had a space in B2, and how could putting =LEN(B2) in another cell affect the CF which is dependent only on the content of B2? To go back to your original question, which I think was saying that you think the CF is being triggered when you believe B2 is empty: Firstly, please copy the formula from your CF Formula Is condition and paste it here into your newsgroup reply. Don't try to retype. While you are at it, please check that you've only got the once condition set. Secondly, what does =LEN(B2) [in another cell] return when you think B2 is empty and when you think that the CF has been triggered? I can assure you that the CF formula suggested *does* work. With =AND($B2=0,$B2<"") as the condition, it will trigger when B2 contains zero, but not when the cell is empty, or contains a space or anything non zero, or when it has a formula returning an empty string "". |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Using A Time Figure As The Trigger
Ah, well that explains it.
You are using =AND($B2=0, $B2<"""") when you were told to use =AND($B2=0, $B2<"") There was sure to be a simple answer. :-) -- David Biddulph "Minitman" wrote in message ... Hey David, My apologizes for not being clear. I plugged =LEN(B2) into C2 as a tester cell. The CF formula in each cell of row 2 is: =AND($B2=0, $B2<"""") When this formula triggers, it turns each cell blue. Column B is formatted in the 24 hour time format. A 0 in B2 is converted to 12:00 o'clock midnight Which is what I need). An empty cell ("") at B2 should NOT also trigger the CF, but it does. As for the space character, it will act like any other character and NOT trigger the CF. What I don't under stand is, with the AND operator, don't both conditions have to be true before the CF triggers? It seems like the CF is treating zero (0) and empty ("") as the same character??? How is this possible? -Minitman On Fri, 28 Nov 2008 01:51:15 -0000, "David Biddulph" <groups [at] biddulph.org.uk wrote: What you are saying doesn't make sense. You say: "I tried putting a space into B2 and that untriggered the CF. I then plugged in your =LEN(B2) and it returned 0 if there was nothing in B2 and the CF was triggered" Where did you put the =LEN(B2)? How could it return 0 if you had a space in B2, and how could putting =LEN(B2) in another cell affect the CF which is dependent only on the content of B2? To go back to your original question, which I think was saying that you think the CF is being triggered when you believe B2 is empty: Firstly, please copy the formula from your CF Formula Is condition and paste it here into your newsgroup reply. Don't try to retype. While you are at it, please check that you've only got the once condition set. Secondly, what does =LEN(B2) [in another cell] return when you think B2 is empty and when you think that the CF has been triggered? I can assure you that the CF formula suggested *does* work. With =AND($B2=0,$B2<"") as the condition, it will trigger when B2 contains zero, but not when the cell is empty, or contains a space or anything non zero, or when it has a formula returning an empty string "". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
apply conditional format to time | Excel Worksheet Functions | |||
Conditional date/time format | Excel Discussion (Misc queries) | |||
Trigger Event on Format Change | Excel Discussion (Misc queries) | |||
format a six figure number to a time. | Excel Discussion (Misc queries) | |||
HOW DO I EXCLUDED A FIGURE FROM A CONDITIONAL FORMAT???? | Excel Worksheet Functions |