convert M/D/YYYY to MM/DD/YYYY

miyo

New Member
Joined
Mar 2, 2010
Messages
15
help, I have a file converted from pdf to excel that has the date as 4/3/2010 and I want it to read 04/03/2010 so I can use the MATCH function - how do I change the date to the MM/DD/YYYY format?

thanks
Miyo
 

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,143
To change the format you would click on the cell, press Ctrl + 1 and pick a new format.

Not sure why you need to change the format because regardless of what the format looks like on the outside the underlying value is still an interger (40271).

Maybe you can tell us about the formula you are using?
 

miyo

New Member
Joined
Mar 2, 2010
Messages
15
I can't change the format to a number, different date or anything. This is a excel report was generated from Kronos payroll system. If there is a way to separate the date components by the "/" maybe I could do that and get it into an excel recognized format?
 

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,143
So if I understand, you are saying Excel is not recognizing the date as a date.

Say the date is in A1 -- in B1 enter =TEXT(A1,"mm\/dd\/yyyy")+0

This will force your text into a date and then format as you like

or

Highlight column with dates >> Text to Columns >> Delimited >> Next >> Next >> Date >> MDY >> Finish
 

StuartM1

Board Regular
Joined
Oct 6, 2010
Messages
95
Hi,
Here's an old relevant thread - that I still cant figure this out. Any info is appreciated.

A cell shows "09/03/2019" . It is formatted as text.
I need to be able to do a vlookup on this "09/03/2019", but 9/3/2019 does not work, even after using a custom format of mm/dd/yyyy.

The only way I've found to lookup 09/03/2019 is to type a ' and then 09/03/2019. Then type ' and the next date, etc etc etc. Dragging dates down is not available.

Any ideas on how to do a vlookup on a mm/dd/yyyy ?

Thanks very much!!!!!!!
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,839
Assuming that value was in A1, perhaps something like this:

=VLOOKUP(TEXT(A1,"mm/dd/yyyy"),A2:C100,2,0)
 

StuartM1

Board Regular
Joined
Oct 6, 2010
Messages
95
Thanks Scott but i tried things like that. This is really a pain... and the custom and date formatting dont deal with this at all. I've gone back and forth. Number format, text format. etc.

I have 09/03/2019 as text and zero ways to match look it up vs a list of dates.

Thank you though!!!!
 

MARK858

Well-known Member
Joined
Nov 12, 2010
Messages
11,329
Office Version
365, 2010
Platform
Windows, Mobile
Try the below on a copy of your worksheet.

Select the column, click "Data" tab - click "Text To Columns" - select "Delimited" and click "Next", make sure "Other" is not checked and click "Next", select General and click "Finish"

There is a date option at the last stage if your date needs further adjustment rather than just the General option (only specifying General to start with as you sometimes have to use the reverse m/d to what you think depending on your region).
 

StuartM1

Board Regular
Joined
Oct 6, 2010
Messages
95
Thanks very much Mark. That worked. !! Still odd how after doing Text to Columns, some dates remain mm/dd/yyyy, and some are m/d/yyyy and others are m/dd/yyyy.

But regardless thanks again for the fix!!!!!!!
 

MARK858

Well-known Member
Joined
Nov 12, 2010
Messages
11,329
Office Version
365, 2010
Platform
Windows, Mobile
Still odd how after doing Text to Columns, some dates remain mm/dd/yyyy, and some are m/d/yyyy and others are m/dd/yyyy.
If it has truly worked then they should all be "real" dates now and so you should be able to format the cells as you wish. If you can't then there is still an issue.
 

Forum statistics

Threads
1,078,242
Messages
5,339,042
Members
399,275
Latest member
amrita17170909

Some videos you may like

This Week's Hot Topics

Top