98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Converting date format to text format
Converting date format to text format
Resolved · Medium Priority · Version 2007
Fri 11 Nov 2011, 16:24Delegate Roger said...
Roger has attended:
Excel Advanced course
Converting date format to text format
I need to merge 3 cells into 1. The cells are set up with the following formatts - number/date/text. I use the following formula:
=E12&" - "&F12&" - "&G12
When they merge the date turns into a number so i have to put an aprostrophie in front of the date to keep it in date format.
Could you tell me if there is a better way to merge the cells or to keep the date format without having to insert the aprostrophie?
I was on an Excel advanced course today.
Thanks in advance.
Wed 16 Nov 2011, 12:40Trainer Jim said...
RE: converting date format to text format
Hi Roger
There is an alternative, though I'm not sure it is better.
You could use the DAY function, the month function and the YEAR function, along with the use of the &. You can also add a space between day month and year, or alternatively a "/".
Assuming your date is in cell G12, from the example you quoted, try this:
=E12&" - "&F12&" - "&DAY(G12)&"/"&MONTH(G12)&"/"&YEAR(G12)
If you only want the last two digits of the year, you could incorporate the RIGHT function with the YEAR function:
=E12&" - "&F12&" - "&DAY(G12)&"/"&MONTH(G12)&"/"&RIGHT(YEAR(G12),2)
I hope that helps - at least it offers the opportunity to modify the format of your date. Good luck with your Excel.
Regards
Jim Hewitt
Wed 23 Nov 2011: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Conditional formatting for cells that return text , not picked up by Go to commandIf you have tried to format all cells containing text even those that display text as a result of a formula you may have had difficulty. As Go to command with constant selected does not pick up formulas that result in text. |
