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.
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
