date/time formatting

  • Thread starter Thread starter Cindy Conover
  • Start date Start date
C

Cindy Conover

I download a csv report from a web based program. Everything works great
but the date comes through as "Jul 21 2009 1:51pm". I do not need the time
in my report. I have tried reformatting the cells, tried to copy to a new
file with the cells already formatted to "7/21/09", opening the cell format
using the F2 key and deleting the time (time will still not be formatted
correctly). The only way I get get just the time is to retype every cell.
What am I doing wrong?

I am using Excel 2002 SP3

TIA,

Cindy
 
When data is downloaded from the web, a lot of "other" characters can "come
along for the ride", corrupting the data as XL recognizable.

You never know exactly what you're getting.

Try something like this, with date in A1:

=Text(A1,"mm/dd/yy")

OR

=Text(Date(year(A1),month(A1),Day(A1)),"mm/dd/yy")

Post back if these don't work.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I download a csv report from a web based program. Everything works great
but the date comes through as "Jul 21 2009 1:51pm". I do not need the time
in my report. I have tried reformatting the cells, tried to copy to a new
file with the cells already formatted to "7/21/09", opening the cell format
using the F2 key and deleting the time (time will still not be formatted
correctly). The only way I get get just the time is to retype every cell.
What am I doing wrong?

I am using Excel 2002 SP3

TIA,

Cindy
 
RD,

This first formula still has the time in the cell
The second one give me an #value error

Cindy
 
Cindy Conover said:
the date comes through as "Jul 21 2009 1:51pm".
I do not need the time in my report.
Click to expand...

Try:

=--(LEFT(A1,6)&","&MID(A1,7,5))

formatted as Date.

I think you are saying that the cell has the text "Jul 21 2009 1:51pm".
Excel does not recognize that as date/time because of the missing comma
after 21 and the missing space before "pm". So TEXT(A1,"mm/dd/yyyy") simply
returns the same string; and --TEXT(...) and DATE(YEAR(A1),...) return a
#VALUE error for the same reason.

The formula above not only returns a recognizable date string, but it also
converts it to a "serial number", the internal form of dates. If you prefer
just the text, omit the "--" and redundant parentheses. For that matter,
perhaps you want simply LEFT(A1,11).

Caveat emptor: The formulas above assume that days of the month less than
10 are represented as, for example, "Jul 1". Note the extra space before
the digit. If that is not true for your situation, post back for a more
complicated, but viable solution.


----- original message -----
 
Back
Top