More Excel help - Fantasy Baseball Cafe 2014 Fantasy Baseball Cafe
100% Deposit Bonus for Cafe Members!

Return to General Talk

More Excel help

Moderator: Baseball Moderators

More Excel help

Postby bigken117 » Fri Feb 20, 2009 7:25 pm

I pull a table from Access into a spreadsheet containing among other things, a MM/DD/YY date. I want to take that date as an MM/YY to chart data by month. By formatting the cell as "Date Mar-01" it displays fine, but still reads the data as MM/DD/YY...pasting special only pastes it as 33000 or whatever number the date configures to. I'm trying to think of an easy way to do it. Anyone got one? I tried making one cell =month(A1) and another =Year(A1) but can't get them together to read Jan-09.

And if anyone has any idea why my charts mysteriously don't appear on my screen anymore I'd be delighted to hear. They just up and disappeared out of nowhere. They display fine on other computers, so it's something with my Excel settings? I have Office 2007.
Image

Jacoby Ellsbury - Sponsored by the Fantasy Baseball Cafe!

Play Pick 3!

Follow the Cafe on Twitter!
Like us on Facebook
bigken117
Bouncer
Bouncer

User avatar
ModeratorCafeholicFantasy ExpertCafe WriterCafe RankerMock(ing) DrafterEagle EyePick 3 Weekly WinnerMatchup Meltdown SurvivorLucky Ladders Weekly Winner
Posts: 12871
(Past Year: 255)
Joined: 15 Feb 2004
Home Cafe: Baseball
Location: posed in an awkward stance

Re: More Excel help

Postby Big Pimpin » Fri Feb 20, 2009 7:34 pm

Ok, so make a table that looks like this:
1 Jan
2 Feb
3 Mar
4 Apr
5 May
6 Jun
7 Jul
8 Aug
9 Sep
10 Oct
11 Nov
12 Dec

I have it in H1:I12 but you can put it on a different tab or whatever (you'll just have to change the range in the formula I give you).

Then the formula is VLOOKUP(MONTH(A1),$H$1:$I$12,2,0)&"-"&RIGHT(YEAR(A1),2)
Big Pimpin
Mod in Retirement
Mod in Retirement

User avatar
EditorCafeholicFantasy ExpertCafe WriterCafe RankerGraphics ExpertMock(ing) DrafterEagle EyeWeb SupporterMatchup Meltdown ChampionPick 3 Weekly WinnerLucky Ladders Weekly Winner
Posts: 13710
(Past Year: 5)
Joined: 20 Apr 2005
Home Cafe: Baseball
Location: Building a metric. And being ignorable and stupid.

Re: More Excel help

Postby bigken117 » Fri Feb 20, 2009 8:39 pm

Cool, I had to tool around with it a little more to suit my needs, (date/text things) but the formula worked perfect, thanks ;-D

Now if only I could see my existing charts :,-(
Ah well, I can just build a new one.

Thanks G.
Image

Jacoby Ellsbury - Sponsored by the Fantasy Baseball Cafe!

Play Pick 3!

Follow the Cafe on Twitter!
Like us on Facebook
bigken117
Bouncer
Bouncer

User avatar
ModeratorCafeholicFantasy ExpertCafe WriterCafe RankerMock(ing) DrafterEagle EyePick 3 Weekly WinnerMatchup Meltdown SurvivorLucky Ladders Weekly Winner
Posts: 12871
(Past Year: 255)
Joined: 15 Feb 2004
Home Cafe: Baseball
Location: posed in an awkward stance


Return to General Talk

Who is online

Users browsing this forum: No registered users and 5 guests

Forums Articles & Tips Sleepers Rankings Leagues


Today's Games
Friday, Apr. 25
(All times are EST, weather icons show forecast for game time)

Kansas City at Baltimore
(7:05 pm)
Miami at NY Mets
(7:05 pm)
Boston at Toronto
(7:05 pm)
Cincinnati at Atlanta
(7:07 pm)
Oakland at Houston
(7:10 pm)
Detroit at Minnesota
(7:35 pm)
Pittsburgh at St. Louis
(8:10 pm)
Chi Cubs at Milwaukee
(8:10 pm)
Tampa Bay at Chi White Sox
(8:10 pm)
Arizona at Arizona
(8:10 pm)
Philadelphia at Philadelphia
(8:15 pm)
LA Dodgers at LA Dodgers
(9:40 pm)
Colorado at Colorado
(10:10 pm)
Texas at Seattle
(10:10 pm)
Cleveland at San Francisco
(10:15 pm)

  • Fantasy Baseball
  • Article Submissions
  • Privacy Statement
  • Site Survey 
  • Contact