Solved

Normalize dates in excel

Posted on 2014-10-08
3
525 Views
Last Modified: 2014-10-08
I have a long column of dates that look like this:

7/1/2008
10/13/2003
11/17/2003
02/25/2002
01/01/1999
5/11/1992
9/30/2013
9/1/2011
10/1/2011

I need leading zeros in front of all the days and months in each cells. Or at least a leading zero on the days under ten. OR is there anyway to convert all these dates into this format YYYYMMDD?
0
Comment
Question by:roosterup
  • 2
3 Comments
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
Comment Utility
put this in column B

=TEXT(A1,"YYYYMMDD")
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
also you could do this another way

=TEXT(A1,"mm/dd/yyyy")
0
 
LVL 15

Expert Comment

by:David L. Hansen
Comment Utility
ProfessorJimJam's solution but refined:
=TEXT(A1, "MM-DD-YYYY")
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

728 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

14 Experts available now in Live!

Get 1:1 Help Now