January 2006

In cells a1, b1, etc., I have the month-end date, like 12/31/2005. Now that 
January 2006 has rolled around, I want the column with the date 12/31/2005 to 
highlight using conditional formatting.
The Month function works well within the current year 
(=MONTH(a$1)=MONTH(TODAY())-1, but how do I get the prior year-end month 
(Dec) to highlight when the first month of the current year rolls around?
-- 
Howard
0
DFM (26)
1/3/2006 9:31:01 PM
excel.misc 78881 articles. 5 followers. Follow

12 Replies
540 Views

Similar Articles

[PageSpeed] 20

Similar as what you have now except with Year() added

=AND(MONTH(A$1)=MONTH(TODAY())-1,YEAR(A$1)=YEAR(TODAY())-1)

Regards
J

--
pinmaste
-----------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=626
View this thread: http://www.excelforum.com/showthread.php?threadid=49769

0
1/3/2006 9:55:40 PM
Nevermind, I guess it doesn't work

--
pinmaste
-----------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=626
View this thread: http://www.excelforum.com/showthread.php?threadid=49769

0
1/3/2006 10:00:28 PM
Try this:

=AND(MONTH(A1)=12,MONTH(TODAY())=1,YEAR(TODAY())=YEAR(A1)+1)

HTH
JG


-- 
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6261
View this thread: http://www.excelforum.com/showthread.php?threadid=497695

0
1/3/2006 10:14:08 PM
Thanks! I knew I had to get the year in there somewhere, but I didn't know how.
-- 
Howard


"pinmaster" wrote:

> 
> Similar as what you have now except with Year() added
> 
> =AND(MONTH(A$1)=MONTH(TODAY())-1,YEAR(A$1)=YEAR(TODAY())-1)
> 
> Regards
> JG
> 
> 
> -- 
> pinmaster
> ------------------------------------------------------------------------
> pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6261
> View this thread: http://www.excelforum.com/showthread.php?threadid=497695
> 
> 
0
DFM (26)
1/3/2006 10:16:03 PM
if you are sure that your dates are the true month end dates, try this:
=AND(MONTH(A$1+1)=MONTH(TODAY()),YEAR(A$1+1)=YEAR(TODAY()))

If you don't include the Year then you will highlight the previous month for 
every year on your spreadsheet.

"Howard" wrote:

> In cells a1, b1, etc., I have the month-end date, like 12/31/2005. Now that 
> January 2006 has rolled around, I want the column with the date 12/31/2005 to 
> highlight using conditional formatting.
> The Month function works well within the current year 
> (=MONTH(a$1)=MONTH(TODAY())-1, but how do I get the prior year-end month 
> (Dec) to highlight when the first month of the current year rolls around?
> -- 
> Howard
0
JAdamJ (13)
1/3/2006 10:20:02 PM
It worked! Thanks. I had to chage +1 to -1, since I was going back to the 
previous month.
-- 
Howard


"JAdamJ" wrote:

> if you are sure that your dates are the true month end dates, try this:
> =AND(MONTH(A$1+1)=MONTH(TODAY()),YEAR(A$1+1)=YEAR(TODAY()))
> 
> If you don't include the Year then you will highlight the previous month for 
> every year on your spreadsheet.
> 
> "Howard" wrote:
> 
> > In cells a1, b1, etc., I have the month-end date, like 12/31/2005. Now that 
> > January 2006 has rolled around, I want the column with the date 12/31/2005 to 
> > highlight using conditional formatting.
> > The Month function works well within the current year 
> > (=MONTH(a$1)=MONTH(TODAY())-1, but how do I get the prior year-end month 
> > (Dec) to highlight when the first month of the current year rolls around?
> > -- 
> > Howard
0
DFM (26)
1/3/2006 11:06:03 PM
Oops, it's not working. It highlights the January 31, 2006 column instead of 
the December 31, 2005 column.
-- 
Howard


"JAdamJ" wrote:

> if you are sure that your dates are the true month end dates, try this:
> =AND(MONTH(A$1+1)=MONTH(TODAY()),YEAR(A$1+1)=YEAR(TODAY()))
> 
> If you don't include the Year then you will highlight the previous month for 
> every year on your spreadsheet.
> 
> "Howard" wrote:
> 
> > In cells a1, b1, etc., I have the month-end date, like 12/31/2005. Now that 
> > January 2006 has rolled around, I want the column with the date 12/31/2005 to 
> > highlight using conditional formatting.
> > The Month function works well within the current year 
> > (=MONTH(a$1)=MONTH(TODAY())-1, but how do I get the prior year-end month 
> > (Dec) to highlight when the first month of the current year rolls around?
> > -- 
> > Howard
0
DFM (26)
1/3/2006 11:16:04 PM
I copied and pasted it exactly as I have it written below, and it's 
highlighting the December 31, 2005 column.  Are you sure you need to change 
the +1 to -1?

"Howard" wrote:

> Oops, it's not working. It highlights the January 31, 2006 column instead of 
> the December 31, 2005 column.
> -- 
> Howard
> 
> 
> "JAdamJ" wrote:
> 
> > if you are sure that your dates are the true month end dates, try this:
> > =AND(MONTH(A$1+1)=MONTH(TODAY()),YEAR(A$1+1)=YEAR(TODAY()))
> > 
> > If you don't include the Year then you will highlight the previous month for 
> > every year on your spreadsheet.
> > 
> > "Howard" wrote:
> > 
> > > In cells a1, b1, etc., I have the month-end date, like 12/31/2005. Now that 
> > > January 2006 has rolled around, I want the column with the date 12/31/2005 to 
> > > highlight using conditional formatting.
> > > The Month function works well within the current year 
> > > (=MONTH(a$1)=MONTH(TODAY())-1, but how do I get the prior year-end month 
> > > (Dec) to highlight when the first month of the current year rolls around?
> > > -- 
> > > Howard
0
JAdamJ (13)
1/4/2006 12:16:02 AM
On Tue, 3 Jan 2006 13:31:01 -0800, Howard <DFM@discussions.microsoft.com>
wrote:

>In cells a1, b1, etc., I have the month-end date, like 12/31/2005. Now that 
>January 2006 has rolled around, I want the column with the date 12/31/2005 to 
>highlight using conditional formatting.
>The Month function works well within the current year 
>(=MONTH(a$1)=MONTH(TODAY())-1, but how do I get the prior year-end month 
>(Dec) to highlight when the first month of the current year rolls around?

Well, if, in fact, the date at the header of the column is the last date of the
month, then:

Cell Value Is  Equal To:  =TODAY()-DAY(TODAY())
--ron
0
ronrosenfeld (3122)
1/4/2006 1:27:24 AM
You're right, it works! I must have typed it in incorrectly. So I copied and 
pasted.

Thanks,
-- 
Howard


"JAdamJ" wrote:

> I copied and pasted it exactly as I have it written below, and it's 
> highlighting the December 31, 2005 column.  Are you sure you need to change 
> the +1 to -1?
> 
> "Howard" wrote:
> 
> > Oops, it's not working. It highlights the January 31, 2006 column instead of 
> > the December 31, 2005 column.
> > -- 
> > Howard
> > 
> > 
> > "JAdamJ" wrote:
> > 
> > > if you are sure that your dates are the true month end dates, try this:
> > > =AND(MONTH(A$1+1)=MONTH(TODAY()),YEAR(A$1+1)=YEAR(TODAY()))
> > > 
> > > If you don't include the Year then you will highlight the previous month for 
> > > every year on your spreadsheet.
> > > 
> > > "Howard" wrote:
> > > 
> > > > In cells a1, b1, etc., I have the month-end date, like 12/31/2005. Now that 
> > > > January 2006 has rolled around, I want the column with the date 12/31/2005 to 
> > > > highlight using conditional formatting.
> > > > The Month function works well within the current year 
> > > > (=MONTH(a$1)=MONTH(TODAY())-1, but how do I get the prior year-end month 
> > > > (Dec) to highlight when the first month of the current year rolls around?
> > > > -- 
> > > > Howard
0
DFM (26)
1/4/2006 3:25:02 AM
Ron, 

Thanks! Works nicely!
-- 
Howard


"Ron Rosenfeld" wrote:

> On Tue, 3 Jan 2006 13:31:01 -0800, Howard <DFM@discussions.microsoft.com>
> wrote:
> 
> >In cells a1, b1, etc., I have the month-end date, like 12/31/2005. Now that 
> >January 2006 has rolled around, I want the column with the date 12/31/2005 to 
> >highlight using conditional formatting.
> >The Month function works well within the current year 
> >(=MONTH(a$1)=MONTH(TODAY())-1, but how do I get the prior year-end month 
> >(Dec) to highlight when the first month of the current year rolls around?
> 
> Well, if, in fact, the date at the header of the column is the last date of the
> month, then:
> 
> Cell Value Is  Equal To:  =TODAY()-DAY(TODAY())
> --ron
> 
0
DFM (26)
1/4/2006 3:28:02 AM
On Tue, 3 Jan 2006 19:28:02 -0800, Howard <DFM@discussions.microsoft.com>
wrote:

>Ron, 
>
>Thanks! Works nicely!

You're welcome.  It seems simpler than some of the other solutions, too.

You can also use CF to bold an entire column by using mixed references:

In A1

Format/Conditional Formatting/Formula Is:

=A$1=(TODAY()-DAY(TODAY()))

Then use the format painter to copy that to your entire table.


--ron
0
ronrosenfeld (3122)
1/4/2006 4:18:47 AM
Reply:

Similar Artilces:

Microsoft Security Bulletin Release for January 2004
Follow up set to microsoft.public.security... Today Microsoft released the following Security Bulletins. Note: �www.microsoft.com/technet/security and �www.microsoft.com/security are authoritative in all matters concerning Microsoft Security Bulletins! ANY e-mail, web board or newsgroup posting (including this one) should be verified by visiting these sites for official information. Microsoft never sends security or other updates as attachments. These updates must be downloaded from the microsoft.com download center or Windows Update. See the individual bulletins for details. Because some m...

2006 problem
I recently downloaded the upgrade to 2006 and now when I try to submit any online payment I get "unexpected attempt to change payee name". I called the my bank, but they are clueless. Has anyone else seen this? Bryce With my bank, I can't change the payee name in Money, I must go to the bank website and edit it. "Bryce White" <BryceWhite@discussions.microsoft.com> wrote in message news:3A5BFBFB-AE8A-4956-B288-AB2B8F961A7C@microsoft.com... >I recently downloaded the upgrade to 2006 and now when I try to submit any > online payment I get "unexpe...

Windows 98 & Money Deluxe 2006
I've been using Money Standard 2004. My operating system is still Windows 98. Any problems running a new copy of Money "DELUXE" 2006 which I purchased(deal with taxcut I bought). I'm concerned before I load it as the box doesn't list "W98". If it's ok, then how about compatablity of old data especially since I had been using Money 04 "Standard". Bill F. Your data should be fine. Money 2006 only is supported on Windows 98SE and above (according to the box in front of me), so if you don't have the 'SE' edition, it might not work. Mo...

[ANN] IronPython Training in New York, January 21st 2010
Hello all, In association with Holdenweb Training [1] I'll be doing some IronPython training in New York in January (21st). The course is called "IronPython from the Ground Up": http://hwebipmc01.eventbrite.com/ Course details: Michael Foord is an early adopter of IronPython, having used it for (among other things) the creation of the Resolver One Python-driven spreadsheet product. He is the author of Manning's IronPython in Action and a well-known speaker at Python conferences throughout the world. IronPython is the implementation of Python for the ...

CRJ101,MOD3 January 13-January 20
Hi Prof Lake, This is Markesha Tillman.Here is my assignment for week 2. Chapter 4 1. Does the law really exist today? That is a very important question. MY opinion is , law exist today and will continue to exist in the future. Now , the bigger question is , Do people believe in the law and follow them ? The answer to the is no! If people believed in in the laws and abided by them prejudice people wouldn't exist. The law says we shouldn't discarnate because if race, color, sex, religion, or sex preferences. If you notice there are still hate crimes being committed even trough th...

Money 2006 Portfolio Manager Screen Refresh
Is there anyway to control how Portfolio Manager refreshes the screen display after a update or manual entry like a buy/sell/dividend/interest etc.? Money 2006 always jumps to the last investment in the portfolio and makes you scroll back up to where you were working. This can be a real pain if you have a large number of different investments. Why can't Money just update and repaint the screen with the focus where the user last entered something? Suggestion for 2007?! In microsoft.public.money, Ken wrote: >Is there anyway to control how Portfolio Manager refreshes the scr...

Scrolling account register in Money 2006
When I am trying to scroll the account register entries using my mouse's scroll wheel, the list creeps and crawls and won't stop scrolling even when I've just moved the scroll wheel a tad. What I have also noticed is that it only exhibits this labored, slow behavior when the mouse pointer inside the transactions area. If you move the mouse pointer outside of the list of transactions, for example to the right margin of the screen, or above the list in the column headings or higher, and then use the scroll wheel, the list scrolls normally and swiftly. What's the deal?? ...

life insurance as of January 1
Hello: As far as the age-based HR Life Insurance setup is concerned, this client would like for any revised premiums to change not as of the employees'/dependents' birthdays but as of January 1 of the calendar year. Is that possible, even? If not, is there a 3rd party HR add-on that cna be used? Thanks! childofthe1980s ...

FYI
Since the January 07 IMF update to our Exchange server, we have tried adding 4 new Blackberry's to our BES. None of them would activate wirelessly? I was forced to connect them to the server via usb. Today i figured it out... The IMF was catching the BES activation email being sent. Once i cleaned out the IMF folder of all unwanted email, i tried once again to activate wirelessly, and saw the email being blocked, so i clikced the "resubmit" button, and sure enough... the Blackberry activated! Just thought i would let you all know this, in case you are running into this p...

money 2006 stadard hanging
I just downloaded the 2006 standard upgrade and when I start the application it says "please wait while 2006 checks for new software updates, this may take several minutes" that's as far as it goes and it gets stuck and I have to reboot my computer to get out of the program. Yes, I am having the exact same problem with 2006 Deluxe, working on a XP home edition machine...this is a very similar problem that Turbo Tax 2004 was having last year. I solved this by switching to Tax Cut...will we have to switch to some other financial program??? Come on MS support...give us some ...

Back to 2002 From 2006
I know some people won't agree with me but I've gotten to the point, in only one week, that Money 2006 has to be the buggiest software I've ever encountered. I've been running Money 2002 (OEM version) since 2/02 and still use archived files from my prior Money 1997 (OEM version). I have never had a problem with it. It does everything I need and want. I found it 100% dependable. I decided it was time to upgrade to Money 2006 Deluxe. Bad decision! I've spent the last week trying to fix the following: Can't get the backup files to save in the same directory as MyMoney fi...

Is there an easy way to import the month of January 2008 into my..
Is there an easy way to import the month of January 2008 into my newletter. The month that the template reflects is 2003. I'd rather not have to retype all the numbers each month. I'm sure this is obvious to someone out there. I'm new to Publisher. Thanks anyone who can help. Sharon Hey Sharon, When you have your newsletter or newsletter template opened, when you choose "Page Content" and then under "See also" click on "Insert Page". In the "Insert Newsletter Pages" dialog box there are two drop down boxes (which are probably de...

Go back to Money Small Business 2006?
I didn't upgrade to Money Plus (Business and Personal) until Sept of last year. I still have my backup money file made before the upgrade. I'm wondering if ultimately the best thing to do would be to reinstall Money Small Business 2006 and enter all my data and transactions from the last 9 months into that version. A lot of work, but possible and could maybe avert disaster down the line? I will be building a new computer over the next couple of weeks, and I'm going to put Windows 7 on it. With Money 2006, I should be set for awhile, don't you think? Patty ...

Posting entries to Historical Year (2006)
Hi Guys, I have a situation where i need to pass Journal entries in year 2006 and 2007 in GP 9. Both are now historical year in the system. Would appreciate any suggestions on this. Thanks in advance Shan Hi Mugan GP allows you to post to the most recent historical year only. And in your case, it is 2007 if i understand correctly so You can pass the entries only in 2007. Thanks Janakiram M.P. MCP-GP "Mugan" <Mugan@discussions.microsoft.com> wrote in message news:926353F9-FD8A-4532-8C2B-225872D4191B@microsoft.com... > Hi Guys, > > I have a situation where i ...

Excel: August-00, August-01, etc. show up as January-00, January-.
The dates in my Excel spreadsheet, August-00, August-01...August-04 show up as January-00, January-01...January-04 on the X axis of a corresponding bar chart. My data was collected in the month of August. Under cell format, numbers, the correct date format is selected and the sample reflects August-00, but it still shows January on the axis. How do I change that? The correct date format can be showing, but if the cells have text, not numeric data, the chart doesn't know they're supposed to be dates. Change the format to mm-dd-yy; do the cells still show August-01 etc? Without...

Registry Fix for MS Money 2006 and MS Money for PocketPC 2006 in Windows Vista with Windows Mobile Device Center
Registry Fix for MS Money 2006 and MS Money for PocketPC 2006 in Windows Vista with Windows Mobile Device Center For WHATEVER reason, Microsoft dropped support for one of its most useful, albeit free, utilities I have EVER used, and used EVERY SINGLE DAY, ALL THROUGHOUT THE DAY, since It has been available: synching Microsoft Money 2006 with my PocketPC. I have purchased EVERY VERSION of Microsoft Money since its inception, and have truly used it to my benefit, and was ECSTATIC when they brought about the ability to synch that great product with my PocketPC by installing the freely downloada...

Money: Upgrading from 2003 to 2006
I wrote a long article back in February detailing my experience attempting to upgrade from 2003 to 2005. At the end of this experiment I decided to completely roll back to 2003. What I didn't take into account was that Microsoft had unknownlingly locked me into the 2006 version by hiding a ticking timebomb in my application. Knowing that my downloads were weeks away of coming to a grinding halt, I had no other option. As long as I was upgrading for good though, I decided to write my new experiences for those of you interested. ---The Decisions--- My first task was to decide which option...

Money 2006 #13
Has anyone else experienced the nightmare called Money 2006? I've had nothing but trouble since upgrading from 2004. My accounts still don't work right. Around May 19 or 20, all hell broke loose for the 2nd time as pointed out. Now, they don't update either. I guess I should go back to 2004! KYDog wrote: > Has anyone else experienced the nightmare called Money 2006? I've had > nothing but trouble since upgrading from 2004. My accounts still don't work > right. Around May 19 or 20, all hell broke loose for the 2nd time as pointed > out. Now, the...

Upgrade from 2004 to 2006
I've read a lot of articles in this group describing how you can't upgrade between different localized versions of money. My question is: How do I see what version my 2004 is? Thanks First, where'd you buy it? "Carsten Hoeck" <c_hoeck@hotmail.com> wrote in message news:%23Z1rGaezFHA.2348@TK2MSFTNGP15.phx.gbl... > I've read a lot of articles in this group describing how you can't upgrade > between different localized versions of money. > My question is: How do I see what version my 2004 is? Post back what Help > About says re the vers...

January 2006
In cells a1, b1, etc., I have the month-end date, like 12/31/2005. Now that January 2006 has rolled around, I want the column with the date 12/31/2005 to highlight using conditional formatting. The Month function works well within the current year (=MONTH(a$1)=MONTH(TODAY())-1, but how do I get the prior year-end month (Dec) to highlight when the first month of the current year rolls around? -- Howard Similar as what you have now except with Year() added =AND(MONTH(A$1)=MONTH(TODAY())-1,YEAR(A$1)=YEAR(TODAY())-1) Regards J -- pinmaste -------------------------------------------------...

Excel 2000 and MapPoint 2006
The MP06 Help insists the COM add-in works with Excel 2000, but I'm having problems. * installed MP06 on a system with Excel 2000 already installed * installed the COM add-in * when I click the 'Insert MapPoint Map' item in Excel 2000 it flashes but nothing happens Trying code in the Excel sheet (reference set to MP 13 Object Library) Dim oApp As Object Set oApp = CreateObject("Mappoint.Application") throws Err 429: ActiveX Control can't create object (so does Set oApp = CreateObject("Mappoint.Application.NA.13")) Any ideas? Thanks Your code run...

Lost Sound in Money 2006
MS Money 2006 will no longer play sounds. Money is no longer listed in the Sounds and Audio Property list where you can select sounds to play. How do I get Money sound back. The files can be seen in C:\....MNYCoreFiles.. Thanks. Ozark wrote: > MS Money 2006 will no longer play sounds. Money is no longer listed in > the Sounds and Audio Property list where you can select sounds to play. > How do I get Money sound back. The files can be seen in > C:\....MNYCoreFiles.. > Thanks. P. S. Reinstall did not fix the problem. Ozark wrote: > MS Money 2006 will no longer ...

Ms 2006 ?
Hi, I been using ms money since the start. I stopped upgrading with money 2003... Then Microsoft stopped me from updating my stocks and banking yesterday. So I'm using the trial version 2005. With money 2006 can you turn off all the advertising like I could with 2003? Thanks Jeff In microsoft.public.money, JG wrote: >With money 2006 can you turn off all the advertising like I could with 2003? >Thanks See Tools->Settings->ProgramSettings->TurnOffSponsorshipsAndShoppingLinks Thank you... "Cal Learner-- MVP" <via_newsgroup@please.tnx> wrote in message n...

Overdue Bill is paid in Money 2006
I have a reoccuring bill set as 'estimate because amount varies' and setup through EPAY (electronic payment) It was paid and is indicated as so in my ledger. It was paid through the EPAY service - Money wants me to pay it again! It says it is overdue. I want to get rid of the 'overdue' condition but dont want to delete the information from my bills screen (account#, address, etc). I want to get rid of it because, well, it was paid. I cannot find something that allows me to do this. So my issue (in summary): A bill was paid electronically. The ledger indicates the bill has...

Change 200801 to January 2008
I have a report that is getting a 'CurrentDate' from a static table that I can not change. The currentdate is in this format :yyyymm How do I change my report so it displays January 2008, instead of 200801. Also just using this as an example as it has all kinds of different current dates depending on the parameters I enter... Thanks James, Do you know if CurrentDate's data type is a Date/Time data type with the format set to display "yyyymm" or if it is a text field? If it is actually a date data type, just change the format of your control to "mmmm yyyy&...