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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
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,145
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,935
Office Version
365, 2010
Platform
Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,053
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,053
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,089,559
Messages
5,408,971
Members
403,246
Latest member
NateD1

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top