Jeremy has attended:
Excel Advanced course
UK Dates in Excel
Hi,
I'm having problems with dates in Excel. If I enter a UK style date dd/mm/yy, excel converts it into US format mm/dd/yy.
If I then change it and ask it to keep it UK format, it requires me to enter it in the mm/dd/yy format.
What I really want to do is put in a date and pull a portion of this date into a title cell.
I.e. I have the date in as 31 July 2012. Then I want to use the concatenate formula to pull this together with another cell like so:
=c4&d5
Therefore leaving a cell reading "For the year ended 31 July 2012". For some reason I can't input 31 July 2012. Whatever format I use, it normally converts it into a number such as 41083.
Please let me know the easiest way to put a date in a cell and just have it show as the UK format date.
Many thanks,
RE: UK Dates in Excel
Hi Jeremy
Thanks for getting in touch. There's a couple of issues here. The first sounds like a localization issue; your copy of Office seems to think it's running in a US version. Do you find Word wants to default to US spelling? The fix for this can vary depending on your IT setup and what you have access to at your PC. You can check what it defaults to in Excel by going to Format Cells > Date and look at the locale.
However, Excel 2010 takes it's date format from Windows. There's a good article on how you can do that here:
http://www.sevenforums.com/tutorials/3529-date-format-change.html
Regarding the second part of your formula, Excel is converting the date on the fly back into it's serial number. You have to force it back into a date format using the TEXT function. So in your above example you will amend it to:
=c4&TEXT(d5,"dd mmmm yyyy")
Consult Excel's help file on the TEXT function for more information.
I hope this helps.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
