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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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!!!!!!!
 
Upvote 0
Assuming that value was in A1, perhaps something like this:

=VLOOKUP(TEXT(A1,"mm/dd/yyyy"),A2:C100,2,0)
 
Upvote 0
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!!!!
 
Upvote 0
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).
 
Upvote 0
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!!!!!!!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top