Excel 2003: Need help converting from text to date/time format please

[IncomePlanet]

Every month I receive data files which have dates and times but they ar
formatted as "general". I need to format these into a specific dat
format which is YYYY/MM/DD HH:MM so I can upload them to a website.

The current fields are in different formats which are:
7/19/2004 15:25
Jul 9 2004 5:46AM
I'm hoping there is an easy way to do this as my excel knowledge i
limited and I need to do this every month.

I also have some files from last year which have different formats
These aren't as important but it would be great if I could change thes
too.
Tue Dec 30 13:36:30
Thu Dec 25 01:07:38 2003
10/16/03 1:43:00 AM

Thanks in advance

 

[tanner]

hi

you can format most of the date formats that you submitted using th
following function:

- format(Date_Value,"YYYY/MM/DD HH:MM")

where Date_value can be a cell reference or string value

eg

- format("10/16/03 1:43:00 AM","YYYY/MM/DD HH:MM")
- format(cells(1,1),"YYYY/MM/DD HH:MM")
(where cells(1,1) hold the text value - 10/16/03 1:43:00 AM)

would give you:
2003/10/16 01:43

hope this help

 

[IncomePlanet]

Thanks tanner but I can't get this function to even work. I haven'
spent a lot of time using excel so there are some things I don't kno
about yet. Where am I suppose to enter this?
I tried putting it into a cell and changing Date_Value to the cel
containing the date I want formatted but it just comes up #NAME?
I even searched through help for "format function" and couldn't fin
anything.


I'm also having another problem now too. I have a couple of files whic
I could just change the cell formatting to what I want but when I try t
save it as a .csv file it loses the formatting again. How can I keep th
date formatted the way I want to and save the file as a .csv file

 

[hgrove]

tanner wrote...

you can format most of the date formats that you submitted
using the following function:

format(Date_Value,"YYYY/MM/DD HH:MM")

...

VBA has a Format function, but Excel has a similar function named TEXT
I'd bet to OP would be more interested in the TEXT function

 

[IncomePlanet]

I have tried the TEXT function but that doesn't work either.