Solved

How to maintain leading zero in date field.

Posted on 2014-03-27
8
269 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 34

Expert Comment

by:Dan Craciun
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Dan I will try it!
0
 
LVL 25

Expert Comment

by:Tony Giangreco
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:mscalafasd
Comment Utility
How do I apply that formula to that column Dan?
0
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
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
Comment Utility
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
Comment Utility
Didn't need assistance
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now