Excel problem - date format confused

Joseph

Hi all

Having probably a stupid problem in Excel, excuse my ignorance, I've tried googling for it but not getting a solution to my problem.

I have alot of date data in the format: Mon(th)-day. All from 2012. I need to change it to dd/mm/yyyy, hope someone can help.

Here's a SS:



Cheers

PS: Should mention that after I get the data into the dd/mm/yyyy format I want to show a graph to illustrate over a month by month period how many purchases there are a day, if anyone could suggest the easiest way to do that I would be really grateful

efb

Click on the column you require, left click- format cells- custom type dd/mm/yyyyy

Joseph

I have been trying that but when I do that it changes everything to this (ie it think that the 2 digits after the month are the year, they are infact the day)

e.g.:

Jul-05 changes to 01/07/2005

ajc100

Joseph wrote: »

I have been trying that but when I do that it changes everything to this (ie it think that the 2 digits after the month are the year, they are infact the day)

e.g.:

Jul-05 changes to 01/07/2005

The formatting is seeing your in-put (Jul-05) as 1st July 2005. You need to put the day first then the month : 05-Jul
This will then format to 05/07/2012

As for the graph, my suggestion would be to add a table that totals your daily purchases by using a SUMIF function and build the graph around that:

Joseph

This is the solution incase anyone comes accross this: (thanks to Ace_XL at excelforum.com)

Use this in B1 and drag down. Format dd-mm-yyyy after that

=DATE(2012,MONTH(A1),RIGHT(YEAR(A1),2))