LinkBack URL
About LinkBacks
I received a 10,000 row s/s and some dates are mm/ddd/yyyy and some are dd/mm/yyyy...any easy way to format to all the same w/o manually doing it??
I received a 10,000 row s/s and some dates are mm/ddd/yyyy and some are dd/mm/yyyy...any easy way to format to all the same w/o manually doing it??
are they text dates or real dates ? what was your source things depend on regional settings
eg in us 10/13/2014 will be seen as 13 oct but if you tried that in uk it would display as text 10/13/2014 as there is no such date
Last edited by martindwilson; 12-16-2014 at 02:28 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
They are real dates and I do believe it's because there were multiple people in other regions gathering the data.
The only reason I knew the date format was mixed is that there is a row with the week # and year.
well there is no real way of telling if 8/7/2014 is 8th july or 7th august
exactly what do they look like
The only reason I know is that the week/year is in the column next to it. I've attached for you to see.
hmm i changed my settings to US then used this
=DATEVALUE(IF(WEEKNUM(A2)=LEFT(B2,2)+0,TEXT(A2,"mm/dd/yyyy"),TEXT(A2,"dd/mm/yyyy")))
if that doesn't work try
=DATEVALUE(IF(WEEKNUM(A2)=LEFT(B2,2)+0,TEXT(A2,"dd/mm/yyyy"),TEXT(A2,"mm/dd/yyyy")))
Ok let me try that...thank you.
YOU ARE A GENIUS!! It worked, the first one!! THANK YOU!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks