• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Difference in date formate

P

pragnesh_50

Member
Hi,


I have one shared book having different tabs from my team member.


in every Sheet i got Dates written in various forms like,,,,2/12/2011, 12/2/2011, 12-2-2011 etc......data column is always A in every sheet


I want to get uniform date format from all the tabs at once.
 
Hi Pragnesh ,


Don't you think that the absence of a format will make this impossible ?


A date such as 12/2/2011 can be interpreted as December 2 or February 12 , depending on the format. How can a formula or a macro determine which is correct ?


Narayan
 
Hi Narayan,


When i pull all the data from various sheets it is difficult to identify correct date.

because in every sheet different format are used (some are without any format just typing the date) like., 12/2/2011, 02/12/2011, 12-2-2011, Feb-12-2011 etc.,


So i am changing date format by going in to Control PanelClock, Language, and Region and change date format to DD/MM/YYYY.


But still i am facing problem with dates which are without any format.


What can i do to solve my problem?


I have tried following macro but still all the dates are not uniform.


Sub UpdateAllDateFormats()

Dim ws As Worksheet


For Each ws In Sheets

ws.Range("TheLocationOfYourDate").NumberFormat = "m/d/yyyy"

Next

End Sub
 
Hi Pragnesh ,


A solution can be found ; that is not the issue I am trying to highlight. When I used the word "format" , I meant that the input data should follow a certain format , otherwise data which is incorrect may still be accepted as valid.


The example I gave was to illustrate this ; if the input data is 2/12/2011 , should we interpret it as December 2 or February 12 ?


Narayan
 
Some of the dates could have been entered as text, that would prevent them from being reformatted.
 
HI,


what if i convert all date in column A in to text format by having in B cell =TEXT(A1,"DD-MM-YY") and than convert it into Date. =datevalue(B1)?
 
You must log in or register to reply here.
Back
Top