Forum home » Delegate support and help forum » Microsoft Excel Training and help » Converting date format to text format
|
|
|
The UK's most regular 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. |
Converting date format to text format
Resolved · Medium Priority · Version 2007
Reply 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.
For upcoming training course dates see: Pricing & availability
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.
|