Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting End of Quarter
I would like a conditional format that if on the last day of the end of the
quarter that if there is no date within that quarter (ie previous quarter in cell A1), that cell A1 turns red and stays red into the next quarter, until a new date is entered into cell A1. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting End of Quarter
Take out the word "previous" in my question.
"Loadmaster" wrote: I would like a conditional format that if on the last day of the end of the quarter that if there is no date within that quarter (ie previous quarter in cell A1), that cell A1 turns red and stays red into the next quarter, until a new date is entered into cell A1. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting End of Quarter
Maybe some more words should be taken out or be replaced...
What is "...the last day of the end of the quarter..."? It would be easier to understand what you are after if you include a few examples with different data and the expected results for each example. Lars-Åke On Sun, 21 Dec 2008 12:38:00 -0800, Loadmaster wrote: Take out the word "previous" in my question. "Loadmaster" wrote: I would like a conditional format that if on the last day of the end of the quarter that if there is no date within that quarter (ie previous quarter in cell A1), that cell A1 turns red and stays red into the next quarter, until a new date is entered into cell A1. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting End of Quarter
The last day of the end of this quarter is the 31st of Dec. So if on the 31st
of Dec the date within cell A1 is not within the 30th of Sep, not counting the 30th of Sep to the 31st of Dec, counting the 31st of Dec. Cell A1 will format as red and continue showing red into Jan Feb etc ... until a new date is entered into cell A1. The next quarter ends on Mar 31st, if a date is in cell A1 is within 31st Dec to the 31st of Mar then cell A1 will remain white or green whatever I choose. Cell B1 is automatically set up to show the end of the quarter with the following formula: =DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT((MONTH(TO DAY())+2)/3))+1,0). I also had to format the date as dd mm yy as the above formula entered a bunch of digits. I hope this helps you figure out an answer. Sorry for the confusion. "Lars-Ã…ke Aspelin" wrote: Maybe some more words should be taken out or be replaced... What is "...the last day of the end of the quarter..."? It would be easier to understand what you are after if you include a few examples with different data and the expected results for each example. Lars-Ã…ke On Sun, 21 Dec 2008 12:38:00 -0800, Loadmaster wrote: Take out the word "previous" in my question. "Loadmaster" wrote: I would like a conditional format that if on the last day of the end of the quarter that if there is no date within that quarter (ie previous quarter in cell A1), that cell A1 turns red and stays red into the next quarter, until a new date is entered into cell A1. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting End of Quarter
Lets say that you have the date 25th of September i cell A1.
On 29th of December cell A1 is green. On 30th of December cell A1 is still green. On 31th of December cell A1 turns red. On 1st of January cell A1 is still red. On 2nd of January cell A1 is still red. Then a new date is entered in cell A1. If the new date is before 25th of September, what will happen to the color? If the new date is Sept 26th, Sept 27th, Sept 28th, Sept 29th, Sept 30th, Oct 1st, Oct 2nd, ...., December 30th, December 31st, Jan 1st, Jan 2dn, Jan 3rd - I all these cases, "a new date is entered into cell A1". Does it mean that it should turn green in all these cases? I guess there are some "built in" conditions on which type of dates that can be entered into A1 that you know of but is not obvious if you don't what these dates are used for. Maybe entering a "new date" always mean that you enter todays date, or a later date than before, but not neccessarily much later. It could still be a quite "old" date. Lars-Åke On Sun, 21 Dec 2008 18:51:00 -0800, Loadmaster wrote: The last day of the end of this quarter is the 31st of Dec. So if on the 31st of Dec the date within cell A1 is not within the 30th of Sep, not counting the 30th of Sep to the 31st of Dec, counting the 31st of Dec. Cell A1 will format as red and continue showing red into Jan Feb etc ... until a new date is entered into cell A1. The next quarter ends on Mar 31st, if a date is in cell A1 is within 31st Dec to the 31st of Mar then cell A1 will remain white or green whatever I choose. Cell B1 is automatically set up to show the end of the quarter with the following formula: =DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT((MONTH(T ODAY())+2)/3))+1,0). I also had to format the date as dd mm yy as the above formula entered a bunch of digits. I hope this helps you figure out an answer. Sorry for the confusion. "Lars-Åke Aspelin" wrote: Maybe some more words should be taken out or be replaced... What is "...the last day of the end of the quarter..."? It would be easier to understand what you are after if you include a few examples with different data and the expected results for each example. Lars-Åke On Sun, 21 Dec 2008 12:38:00 -0800, Loadmaster wrote: Take out the word "previous" in my question. "Loadmaster" wrote: I would like a conditional format that if on the last day of the end of the quarter that if there is no date within that quarter (ie previous quarter in cell A1), that cell A1 turns red and stays red into the next quarter, until a new date is entered into cell A1. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting End of Quarter
Hypothetically if today is the 31st of Dec €œLast day at the end of the
quarter€ and the date in cell A1 is the 25, 26, 27, 28, 29 or 30 of Sep, cell A1 should be red. Flight crews must obtain a date within the current quarter otherwise they loose there currency. If today is the 1, 2, 3 etc€¦ of Jan and the date in cell A1 still reads a date in Sep or prior, cell A1 remains red until a newer date within the current quarter is entered into cell A1. When a new date is entered into cell A1 it means the aircrew member has just updated his/her qualification and cell A1 can remain neutral, white or green depending on what option I choose for cell fill. If today is the 30th of Dec and the date within cell A1 is within the previous quarter cell A1 can remain neutral, white or green as they still have that day to update there currency prior to the end of the quarter. The new date entered is always within the current quarter. Your colors are all correct on your thread above. I hope this helps in finding a solution. "Lars-Ã…ke Aspelin" wrote: Lets say that you have the date 25th of September i cell A1. On 29th of December cell A1 is green. On 30th of December cell A1 is still green. On 31th of December cell A1 turns red. On 1st of January cell A1 is still red. On 2nd of January cell A1 is still red. Then a new date is entered in cell A1. If the new date is before 25th of September, what will happen to the color? If the new date is Sept 26th, Sept 27th, Sept 28th, Sept 29th, Sept 30th, Oct 1st, Oct 2nd, ...., December 30th, December 31st, Jan 1st, Jan 2dn, Jan 3rd - I all these cases, "a new date is entered into cell A1". Does it mean that it should turn green in all these cases? I guess there are some "built in" conditions on which type of dates that can be entered into A1 that you know of but is not obvious if you don't what these dates are used for. Maybe entering a "new date" always mean that you enter todays date, or a later date than before, but not neccessarily much later. It could still be a quite "old" date. Lars-Ã…ke On Sun, 21 Dec 2008 18:51:00 -0800, Loadmaster wrote: The last day of the end of this quarter is the 31st of Dec. So if on the 31st of Dec the date within cell A1 is not within the 30th of Sep, not counting the 30th of Sep to the 31st of Dec, counting the 31st of Dec. Cell A1 will format as red and continue showing red into Jan Feb etc ... until a new date is entered into cell A1. The next quarter ends on Mar 31st, if a date is in cell A1 is within 31st Dec to the 31st of Mar then cell A1 will remain white or green whatever I choose. Cell B1 is automatically set up to show the end of the quarter with the following formula: =DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT((MONTH(T ODAY())+2)/3))+1,0). I also had to format the date as dd mm yy as the above formula entered a bunch of digits. I hope this helps you figure out an answer. Sorry for the confusion. "Lars-Ã…ke Aspelin" wrote: Maybe some more words should be taken out or be replaced... What is "...the last day of the end of the quarter..."? It would be easier to understand what you are after if you include a few examples with different data and the expected results for each example. Lars-Ã…ke On Sun, 21 Dec 2008 12:38:00 -0800, Loadmaster wrote: Take out the word "previous" in my question. "Loadmaster" wrote: I would like a conditional format that if on the last day of the end of the quarter that if there is no date within that quarter (ie previous quarter in cell A1), that cell A1 turns red and stays red into the next quarter, until a new date is entered into cell A1. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting End of Quarter
So here is the conditions if I got your description right:
Cell A1 should be highlighted if any of the following two conditions are true: 1) Today is the last day of (the end of) the quarter and A1 has a date that is before the first day of the same, current, quarter 2) A1 has a date that is before the first day of the previous quarter. Put this formula in any unused cell =OR(AND(TODAY()=DATE(YEAR(TODAY()),INDEX({3;6;9;12 },INT((MONTH(TODAY())+2)/3))+1,0),A1<DATE(YEAR(TODAY()),INDEX({3;6;9;12},IN T((MONTH(TODAY())+2)/3))-2,1)),A1<DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT(( MONTH(TODAY())+2)/3))-5,1)) all in one line. Then set a conditional formatting on cell A1 to be highlighted the way you choose if the cell with the formula above has the value TRUE. Hope this helps / Lars-Åke On Mon, 22 Dec 2008 10:09:01 -0800, Loadmaster wrote: Hypothetically if today is the 31st of Dec “Last day at the end of the quarter” and the date in cell A1 is the 25, 26, 27, 28, 29 or 30 of Sep, cell A1 should be red. Flight crews must obtain a date within the current quarter otherwise they loose there currency. If today is the 1, 2, 3 etc… of Jan and the date in cell A1 still reads a date in Sep or prior, cell A1 remains red until a newer date within the current quarter is entered into cell A1. When a new date is entered into cell A1 it means the aircrew member has just updated his/her qualification and cell A1 can remain neutral, white or green depending on what option I choose for cell fill. If today is the 30th of Dec and the date within cell A1 is within the previous quarter cell A1 can remain neutral, white or green as they still have that day to update there currency prior to the end of the quarter. The new date entered is always within the current quarter. Your colors are all correct on your thread above. I hope this helps in finding a solution. "Lars-Åke Aspelin" wrote: Lets say that you have the date 25th of September i cell A1. On 29th of December cell A1 is green. On 30th of December cell A1 is still green. On 31th of December cell A1 turns red. On 1st of January cell A1 is still red. On 2nd of January cell A1 is still red. Then a new date is entered in cell A1. If the new date is before 25th of September, what will happen to the color? If the new date is Sept 26th, Sept 27th, Sept 28th, Sept 29th, Sept 30th, Oct 1st, Oct 2nd, ...., December 30th, December 31st, Jan 1st, Jan 2dn, Jan 3rd - I all these cases, "a new date is entered into cell A1". Does it mean that it should turn green in all these cases? I guess there are some "built in" conditions on which type of dates that can be entered into A1 that you know of but is not obvious if you don't what these dates are used for. Maybe entering a "new date" always mean that you enter todays date, or a later date than before, but not neccessarily much later. It could still be a quite "old" date. Lars-Åke On Sun, 21 Dec 2008 18:51:00 -0800, Loadmaster wrote: The last day of the end of this quarter is the 31st of Dec. So if on the 31st of Dec the date within cell A1 is not within the 30th of Sep, not counting the 30th of Sep to the 31st of Dec, counting the 31st of Dec. Cell A1 will format as red and continue showing red into Jan Feb etc ... until a new date is entered into cell A1. The next quarter ends on Mar 31st, if a date is in cell A1 is within 31st Dec to the 31st of Mar then cell A1 will remain white or green whatever I choose. Cell B1 is automatically set up to show the end of the quarter with the following formula: =DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT((MONTH(T ODAY())+2)/3))+1,0). I also had to format the date as dd mm yy as the above formula entered a bunch of digits. I hope this helps you figure out an answer. Sorry for the confusion. "Lars-Åke Aspelin" wrote: Maybe some more words should be taken out or be replaced... What is "...the last day of the end of the quarter..."? It would be easier to understand what you are after if you include a few examples with different data and the expected results for each example. Lars-Åke On Sun, 21 Dec 2008 12:38:00 -0800, Loadmaster wrote: Take out the word "previous" in my question. "Loadmaster" wrote: I would like a conditional format that if on the last day of the end of the quarter that if there is no date within that quarter (ie previous quarter in cell A1), that cell A1 turns red and stays red into the next quarter, until a new date is entered into cell A1. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting End of Quarter
Thank-you very much, it worked.
"Lars-Ã…ke Aspelin" wrote: So here is the conditions if I got your description right: Cell A1 should be highlighted if any of the following two conditions are true: 1) Today is the last day of (the end of) the quarter and A1 has a date that is before the first day of the same, current, quarter 2) A1 has a date that is before the first day of the previous quarter. Put this formula in any unused cell =OR(AND(TODAY()=DATE(YEAR(TODAY()),INDEX({3;6;9;12 },INT((MONTH(TODAY())+2)/3))+1,0),A1<DATE(YEAR(TODAY()),INDEX({3;6;9;12},IN T((MONTH(TODAY())+2)/3))-2,1)),A1<DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT(( MONTH(TODAY())+2)/3))-5,1)) all in one line. Then set a conditional formatting on cell A1 to be highlighted the way you choose if the cell with the formula above has the value TRUE. Hope this helps / Lars-Ã…ke On Mon, 22 Dec 2008 10:09:01 -0800, Loadmaster wrote: Hypothetically if today is the 31st of Dec €œLast day at the end of the quarter€ and the date in cell A1 is the 25, 26, 27, 28, 29 or 30 of Sep, cell A1 should be red. Flight crews must obtain a date within the current quarter otherwise they loose there currency. If today is the 1, 2, 3 etc€¦ of Jan and the date in cell A1 still reads a date in Sep or prior, cell A1 remains red until a newer date within the current quarter is entered into cell A1. When a new date is entered into cell A1 it means the aircrew member has just updated his/her qualification and cell A1 can remain neutral, white or green depending on what option I choose for cell fill. If today is the 30th of Dec and the date within cell A1 is within the previous quarter cell A1 can remain neutral, white or green as they still have that day to update there currency prior to the end of the quarter. The new date entered is always within the current quarter. Your colors are all correct on your thread above. I hope this helps in finding a solution. "Lars-Ã…ke Aspelin" wrote: Lets say that you have the date 25th of September i cell A1. On 29th of December cell A1 is green. On 30th of December cell A1 is still green. On 31th of December cell A1 turns red. On 1st of January cell A1 is still red. On 2nd of January cell A1 is still red. Then a new date is entered in cell A1. If the new date is before 25th of September, what will happen to the color? If the new date is Sept 26th, Sept 27th, Sept 28th, Sept 29th, Sept 30th, Oct 1st, Oct 2nd, ...., December 30th, December 31st, Jan 1st, Jan 2dn, Jan 3rd - I all these cases, "a new date is entered into cell A1". Does it mean that it should turn green in all these cases? I guess there are some "built in" conditions on which type of dates that can be entered into A1 that you know of but is not obvious if you don't what these dates are used for. Maybe entering a "new date" always mean that you enter todays date, or a later date than before, but not neccessarily much later. It could still be a quite "old" date. Lars-Ã…ke On Sun, 21 Dec 2008 18:51:00 -0800, Loadmaster wrote: The last day of the end of this quarter is the 31st of Dec. So if on the 31st of Dec the date within cell A1 is not within the 30th of Sep, not counting the 30th of Sep to the 31st of Dec, counting the 31st of Dec. Cell A1 will format as red and continue showing red into Jan Feb etc ... until a new date is entered into cell A1. The next quarter ends on Mar 31st, if a date is in cell A1 is within 31st Dec to the 31st of Mar then cell A1 will remain white or green whatever I choose. Cell B1 is automatically set up to show the end of the quarter with the following formula: =DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT((MONTH(T ODAY())+2)/3))+1,0). I also had to format the date as dd mm yy as the above formula entered a bunch of digits. I hope this helps you figure out an answer. Sorry for the confusion. "Lars-Ã…ke Aspelin" wrote: Maybe some more words should be taken out or be replaced... What is "...the last day of the end of the quarter..."? It would be easier to understand what you are after if you include a few examples with different data and the expected results for each example. Lars-Ã…ke On Sun, 21 Dec 2008 12:38:00 -0800, Loadmaster wrote: Take out the word "previous" in my question. "Loadmaster" wrote: I would like a conditional format that if on the last day of the end of the quarter that if there is no date within that quarter (ie previous quarter in cell A1), that cell A1 turns red and stays red into the next quarter, until a new date is entered into cell A1. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting on Current or previous Quarter entries | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Formatting cell to show current Quarter | Excel Worksheet Functions | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |