Dates: US vs. Europe

  • Hi,

    I am located in NY and recently received two files from London.

    One has the dates transformed properly, although in dd-mmm-yy format.

    The other one has the day and month reversed, so what should be Dec-1-03 is now 12-Jan-03. The ones with the day > 12 get formatted as text.

    Does anyone have an idea why this is happening on one file and not on the other?

    Thank you,


  • Re: Dates: US vs. Europe

    Hey Greg,

    Not sure if this is it, but, check to see how the original file/cells were formatted. Most users don't go to the trouble to "hard" format date cells. In stead they rely on the "Auto" format that may or may not be right.

    Just a thought or starting place.


  • Re: Dates: US vs. Europe

    Hi Greg,

    Some ideas

    Change the format of the cells that have the dates in question to "General" and observe if the cells have correct date serial numbers. Or use the "IsText" function to determine if the dates are actually texted.

    = IF(ISTEXT(A1),"Text","Not Text")

    Also text is usually left hand justified, while dates (being date serial numbers, are usually right hand justified.

    If the dates are in European text format convert to date serial by

    = IF(ISTEXT(A10),DATEVALUE(MID(A10,FIND("/",A10)+1,FIND("/",A10,4)-FIND("/",A10)-1) & "/" & LEFT(A10,FIND("/",A10)-1) & "/" & RIGHT(A10,LEN(A10)-FIND("/",A10,4))),"")


    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!