Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel year format 1

Status
Not open for further replies.
mars19301

mars19301

MIS
May 31, 2001
69
US
For a column, I can get 97 from 1997 using MID, but I need '97 (apostrophe prefix). Can somebody give me a pointer? Thank you in advance for your help.
 
Sort by date Sort by votes
If you're starting with text then [tt]=[/tt][blue][tt]"[/tt][/blue][red][tt]'[/tt][/red][blue][tt]"[/tt][/blue][tt]&MID([/tt]etc.
If you're starting with a date then you should probably be using a format ([blue]'yy[/blue] perhaps) and not a MID function.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association
 
Upvote 0 Downvote
Thank you for the suggestion. Actually the data reads: 1997 and I must turn it into '97 Complicated background story, but that's the requirement.
 
Upvote 0 Downvote
Got it. I did a MID into a column and then CONCATENATE on that column into a 2rd column and then copied and pasted "values" into the 3rd column which I used for the required output. m

 
Upvote 0 Downvote
Assume 1997 appears in cell A1. In another cell, type in the following formula:

="'"&RIGHT(A1,2)

which will give you '97.
 
Upvote 0 Downvote




If you have a REAL DATE, then you should NOT have to use STRING FUNCTIONS like LEFT, MID, RIGHT. A Date is NOT a STRING. It is a NUMBER that is FORMATTED to appear in any number of date formats.

So your requirement is to get '97 from a 1997 date?
Code: 
SomeString = "'" & Format(YourDateVariable, "yy")


Skip,

[glasses] [red][/red]
[tongue]
 
Upvote 0 Downvote
And yet you got a star! Nice one Tony.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
Force Data Entry Format in Excel
Replies
10
Views
293
combo
combo
techie131
  • Locked
  • Question
Excel Date format
2
Replies
20
Views
530
SkipVought
S
renigar
  • Question
Having trouble with Excel Lookup
Replies
5
Views
518
renigar
renigar
Bijugate
  • Locked
  • Question
Excel update question
Replies
0
Views
460
Bijugate
Bijugate
Pirouette
  • Locked
  • Question
Excel workbook
Replies
1
Views
407
combo
combo

Part and Inventory Search

Sponsor

Back
Top