Excel - Date Formatting

Resolved · Urgent Priority · Version 2010

Tue 7 Apr 2015, 16:26 Delegate Adrian said...

Adrian has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel Intermediate course

Excel - Date Formatting

Hi

Could you please help regarding changing the Date format. I have tried going to Format Cells etc, but the format doesn't change.

I require either FRI 3-APR-15 or Apr 03,2015 to be in the following format: DD/MM/YYYY.

Could someone please urgently help as this is a new external report for daily trade checks to ensure accuracy

Tue 7 Apr 2015, 16:34 Trainer Andrew said...

RE: Excel - Date Formatting

Hi Adrian

Thanks for your question. It's possible the original dates are not stored in a format that Excel recognises as a date - which is why you can't change the formatting. A way to test this is to right click and choose format cells but change the format to general. Does the date change to a large number (e.g. 42101) or does it remain unchanged. If it doesn't change appearance then Excel most probably thinks the date is text rather than a number. Let me know what you find.

Thanks,
Andrew

Kind regards,
Andrew

Tue 7 Apr 2015, 16:51 Delegate Adrian said...

RE: Excel - Date Formatting

Hi

It doesn't change I am afraid, it stays as above

Thanks

Adrian

Fri 10 Apr 2015, 13:42 Trainer Jens said...

RE: Excel - Date Formatting

Hi Adrian,

If the dates are not changing to a number when you change the format to general, then it is because the dates are text and not dates.

Do you get the dates from an external data source? Very often when we import data to Excel we have problems with date formats.

If you type the dates yourself you must type them 10/04/2015 for Excel to understand it is a date.

Kind regards

Jens Bonde
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

Fri 17 Apr 2015, 10:22 Delegate Adrian said...

RE: Excel - Date Formatting

Hi Jens

Thank you for your help its much appreciated! I have now resolved the issue.

Kind regards

Adrian

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Select only cell that contain text to lock format

For selecting cells that only contain Text in Excel

By selecting cells that only contain text, you can delete, fill or protect cells of this type.

Use short cut to Go to box (F5) or Edit, Go to
In the dialog box, click special button & select Constants and only check text or any other desired type.

Click OK.

And text cells will all be highlighted for you to apply format. Please note only works on one sheet at a time.

View all Excel hints and tips