Members Login
Username 
 
Password 
    Remember Me  
The Jammie Dodger Bored Board -> JDBB -> The Jammie Dodger Bored Board -> Quick Excel Question
Post Info TOPIC: Quick Excel Question
JonnyStead


"I'm Lois!"


Status: Offline
Posts: 4979
Date: Jun 27, 2006
Quick Excel Question
Permalink  

I need to extract just the month and year from a date field for a separate column in excel and tried to use the mid command without success - does anyone have any ideas? Below is what i need it to look like...


Present Date field        New Date Field


24/6/1973                   6/1973


 


I cant just copy and paste and reformat the new field because when I put the data into a pivot table it treats the new reformatted data as being dd/mm/yy still - which is not use to me...



__________________

I'll take arrogance and the inevitable hubris over self-doubt and lack of confidence.

"Everyone has a plan, until they get punched in the face" - Mike Tyson
bonniepirateanne


I ain't a Pirate and I ain't called Anne, but I sure am Bonnie!

(Mrs)




Status: Offline
Posts: 3266
Date: Jun 27, 2006
Permalink  

where is nate when you need him?

__________________
current location: Antrim. I like it.
BrianS


Still Number One

Status: Offline
Posts: 576
Date: Jun 27, 2006
Permalink  

As long as all you need is text, then this will work

=CONCATENATE(MONTH(TEXT(A1,"@")),"/", YEAR(TEXT(A1,"@")))

just change the cells referenced

__________________
ddvmor


I am the Jammie King!




Status: Offline
Posts: 12736
Date: Jun 27, 2006
Permalink  

Cor.  A nicely formed formula is so damn sexy!


 


Er...



__________________
The King has spoken... But nobody listened.
BrianS


Still Number One

Status: Offline
Posts: 576
Date: Jun 27, 2006
Permalink  


ddvmor wrote:
Cor.  A nicely formed formula is so damn sexy!
 
Er...


I am just waiting for Nate to come up something much simpler. Excel is not my forte.

__________________
ddvmor


I am the Jammie King!




Status: Offline
Posts: 12736
Date: Jun 27, 2006
Permalink  

Me neither.  But your cunning plan was a lot more cunning than mine, which involved notepad, a tub of peanut butter and a small lemon.

__________________
The King has spoken... But nobody listened.
Aodan


Teiam Member




Status: Offline
Posts: 2078
Date: Jun 27, 2006
Permalink  

You should have used a lime.

__________________
*~*Mouth Breathing DVD Extra Watcher*~*
NateO


Vice JDK
and Man of the People




Status: Offline
Posts: 5453
Date: Jun 27, 2006
Permalink  

How's this, mate:



=TEXT(A1,"mm/yyyy")




__________________
You can't polish a turd
bonniepirateanne


I ain't a Pirate and I ain't called Anne, but I sure am Bonnie!

(Mrs)




Status: Offline
Posts: 3266
Date: Jun 27, 2006
Permalink  


bonniepirateanne wrote:
where is nate when you need him?


Edit: where is brian when you need him?

__________________
current location: Antrim. I like it.
NateO


Vice JDK
and Man of the People




Status: Offline
Posts: 5453
Date: Jun 27, 2006
Permalink  

bonniepirateanne wrote:

Edit: where is brian when you need him?
It was good of you to step in, dude.

__________________
You can't polish a turd
BrianS


Still Number One

Status: Offline
Posts: 576
Date: Jun 27, 2006
Permalink  

Arrggg, I knew there had to be a better way. I could not find a good list of acceptiple formats for TEXT(), just hacked together the first one I could find.

Nice one Nate, you are the man!

__________________
bonniepirateanne


I ain't a Pirate and I ain't called Anne, but I sure am Bonnie!

(Mrs)




Status: Offline
Posts: 3266
Date: Jun 27, 2006
Permalink  

hoorah for everyone. well just nate and brian. no no everyone.. hoorah.

ok i'll hush

except to say if anyone needs help preparing a lovely spaghetti bolognese or something, you know, you can ask me

__________________
current location: Antrim. I like it.
ddvmor


I am the Jammie King!




Status: Offline
Posts: 12736
Date: Jun 27, 2006
Permalink  

Aye, nice work guys.  And not a tin of peaches in sight...

-- Edited by ddvmor at 00:09, 2006-06-28

__________________
The King has spoken... But nobody listened.
JonnyStead


"I'm Lois!"


Status: Offline
Posts: 4979
Date: Jun 28, 2006
Permalink  

Excellent excellent work everyone - thanks and well done to Brian and Nate!

__________________

I'll take arrogance and the inevitable hubris over self-doubt and lack of confidence.

"Everyone has a plan, until they get punched in the face" - Mike Tyson
NateO


Vice JDK
and Man of the People




Status: Offline
Posts: 5453
Date: Jun 28, 2006
Permalink  

Glad to be help!



bonniepirateanne wrote:

ok i'll hush except to say if anyone needs help preparing a lovely spaghetti bolognese or something, you know, you can ask me


Might have to take you up on that, Kate-mate. Mmmm, spag. bol...


Brian, I think the possible formats pretty much mirror that of VB[A]'s Format Function, or perhaps it's anything in Cell formatting possibilities... You can grab these when you select a format and look at the 'custom' bit, for interpretation, I think.



__________________
You can't polish a turd
Page 1 of 1  sorted by
 
Quick Reply

Please log in to post quick replies.
The Jammie Dodger Bored Board -> JDBB -> The Jammie Dodger Bored Board -> Quick Excel Question
Post to Digg Post to Del.icio.us


Create your own FREE Forum
Report Abuse 		Powered by ActiveBoard