Solved

How to maintain leading zero in date field.

Posted on 2014-03-27
8
280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

635 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