Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

How to maintain leading zero in date field.

I have to export an Excel worksheet to a CSV file with one of the columns containing date information in the format of MM/DD/YYYY.

I have the correct format and then I change the column to text, but it still messes up any dates that being with 0 for the month, for example 02 for February.

How can I maintain the 0's?  The upload simply fails as of now, or at least 850 entries do.  I'm using Excel 2007, but I do have 2010 available if I need to use that instead.

I'm uploading student information into a state system.

Thanks for any help,

Matt
0
mscalafasd
Asked:
mscalafasd
  • 5
  • 2
1 Solution
 
Dan CraciunIT ConsultantCommented:
Just tried to save as CSV a file with a field with the following formula:

= TEXT(A1, "mm/dd/yyyy")

where A1 was a date cell, and it kept the 0s.

HTH,
Dan
0
 
mscalafasdAuthor Commented:
In addition, I can get it to display correctly, it's just when I export to a .CSV it does not maintain it...

I do the export and then I try the upload and it fails...

Thanks.
0
 
mscalafasdAuthor Commented:
Thanks Dan I will try it!
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Tony GiangrecoCommented:
Reformat the date format to 03/27/2014 without changing ti to text. I just tested it and it kept the leading zero using Excel 2010.
0
 
mscalafasdAuthor Commented:
How do I apply that formula to that column Dan?
0
 
Dan CraciunIT ConsultantCommented:
You cannot apply the formula to the same column (circular reference). You need to put it into another column.
For ex if your dates are in the column A, put that formula that will convert your dates to text into column B.
0
 
mscalafasdAuthor Commented:
Nevermind... in all my fiddling somehow the dates were 24/03/2014, they got flipped around.  I used United States (Carribbean) Date format and just saved it and it's fine now...

Thanks,

Matt
0
 
mscalafasdAuthor Commented:
Didn't need assistance
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now