Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to maintain leading zero in date field.

Posted on 2014-03-27
8
Medium Priority
?
282 Views
Last Modified: 2014-04-01
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
Comment
Question by:mscalafasd
  • 5
  • 2
8 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39959436
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
 

Author Comment

by:mscalafasd
ID: 39959438
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
 

Author Comment

by:mscalafasd
ID: 39959439
Thanks Dan I will try it!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:Tony Giangreco
ID: 39959443
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
 

Author Comment

by:mscalafasd
ID: 39959444
How do I apply that formula to that column Dan?
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39959454
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
 

Accepted Solution

by:
mscalafasd earned 0 total points
ID: 39959478
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
 

Author Closing Comment

by:mscalafasd
ID: 39968816
Didn't need assistance
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question