Issue with format of dates

  • Hello all


    I'm so sorry to be a pain but if someone could take a look at this problem for me I would be eternally grateful as it is driving me mad, slowly.


    Contained in Module 7 is a text to column action for cells N3 to N500 on the "DATES" sheet.


    It works fine for most of the data but the data in N16-N17, N34-N35 and N39-41 are formatted slighty differently and the command always puts the month first instead of the day (it is American).


    For example N38 is 2/18/2005 and after action is 2 18 2005
    However N39 is 03/01/2005 and after action is 1 3 2005 when it should be 3 1 2005.


    I am aware that the data is formatted slighty different to the rest and is thus giving me problems but for the life of me I am unable to understand why.


    Any help is very gratefully received.


    Many thanks
    Reg

  • Re: Text To Column Issue


    Is there any reason why those cells should be formatted differently?


    Because if not, you could always just change their formatting through 'Format | Cells | Number ' on the toolbar, and pick from the 'date' list the format you want. That would probably solve your problem, and you could apply it to your entire range very easily by highlighting.


    Or am I misunderstanding your question?


    EDIT: I tried it on your data, and to get the rogue cells to change, I had to format them as general first, and then apply the custom format. No idea why that is, but it did work. *shrug*


    Hope that helps,

  • Re: Text To Column Issue


    Hi Jaz


    Believe me - I've tried that.


    No matter what date format you choose, those cells in question always misbehave when I Text To Column for some reason.

  • Re: Text To Column Issue


    Sorry, edited as you posted.


    Is there anything else 'special' about those cells? I'll have a look through your code to see if I can trace the problem, but it's puzzling me at the minute. :confused:

  • Re: Text To Column Issue


    Sorry Jaz - no joy.


    The ridiculous thing is is that if you do the Text to Column process manually it works but not via the macro.


    Any ideas?

  • Re: Text To Column Issue


    It's something to do with the leading zero in the problem cells, I'm sure. So far, had no luck trying to get round it though.


    I have a suspicion that formatting as text for text-to-column, then converting back, may work, though it may involve messing round with copying twice to a separate column. I'll try, and report back.

  • Re: Text To Column Issue


    Hi Reg,


    I tried out my idea and no success; very sorry. I'm going to be out of the office this afternoon, so your best bet is probably to see if anyone else can field this. If any of the OzMVPs have a go, you'll probably be sorted!


    Otherwise, I'll have another look tomorrow and see if I can solve it (it's bugging me as well, now!).


    Kind regards, sorry I couldn't help much.

  • Re: Text To Column Issue


    Jaz - thanks very much or you assistance anyway.


    In a way I am a teeny weeny bit content that it is driving somebody else mad too!


    Thanks again
    Reg

  • Re: Trouble With Hiding #DIV/0!


    Batman,


    I apologize if it is not etiquette to bother OzMVPs with other matters but I posted a problem yesterday and Jaz replied, but could not fix it and since then nobody else has replied.


    If I am out of order pease say, but would you be able to take a look at it at all?


    Attached is a text to column action for cells N3 to N500.


    It works fine for most of the data but the data in N16-N17, N34-N35 and N39-41 are formatted slighty differently and the command always puts the month first instead of the day (it is American).


    For example N38 is 2/18/2005 and after action is 2 18 2005
    However N39 is 03/01/2005 and after action is 1 3 2005 when it should be 3 1 2005.


    I am aware that the data is formatted slighty different to the rest and is thus giving me problems but for the life of me I am unable to understand why. The ridiculous thing is is that if you do the Text to Column process manually it works but not via the macro.


    Jaz replied:"It's something to do with the leading zero in the problem cells, I'm sure. So far, had no luck trying to get round it though.


    I have a suspicion that formatting as text for text-to-column, then converting back, may work, though it may involve messing round with copying twice to a separate column."


    Hope that you can help
    Reg

  • Re: Trouble With Hiding #DIV/0!


    Reg,


    However much you try to get round me by promoting me to OzMVP, you shouldn't really add a new problem to an existing thread. You could bump the existing thread for this problem back to the top of the queue for more attention by adding another comment to it.


    That said, I will have a look at your problem.


    What I would like to know is how the data in columns A & N is populated into the worksheet. The reason that the values in N16 & N17 are in a different format is that the second part of the date (in this case the day) is less than 13 and Excel is managing to interpret it as a date; what I don't know is whether the date in those cells is displayed in dd/mm/yy or mm/dd/yy format. Is N16 4 August or 8 April?


    If you are working in non-US format dates, you will find that the internal processes of VBA will often automatically convert dates into US format unless you specifically code round it, which is why the Text To Columns is working as a spreadsheet function, but not in VBA.


    As to how to overcome the problem, I would first of all like to go back to columns A & N to try to get the data there in a consistent format, as currently it's a mixture of text and dates. If you could let me know how the data gets in there and whether it would be possible to change that process that would be a start. If that's not possible, I will look at ways to convert the data as it stands.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Trouble With Hiding #DIV/0!


    Thanks very much Batman for your offer and I'll be sure to follow the Ozgrid code in future.


    The data in columns A&N is populated from another spreadsheet and I have included the relevant information in a new attachment. It seems that all of the data is in general format except the rogue stuff which is custom. N16 is 8th April - the data comes from an American client and is thus mm/dd/yyyy.


    Thanks once again for your support
    Reg

  • Re: Trouble With Hiding #DIV/0!


    Reg,


    Your source data is obviously just opening up in the different formats, so we'll need to look at a way of converting from those different formats. By the way, when I tried Text To Columns manually on the first entry shown as a date, it converted to a 4 August, not 8 April.


    Can you let me know what you want to do with the data imported? Do you just want a single new date column, separate date and time columns, broken down into separate columns for day, month and year, or something else?


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Trouble With Hiding #DIV/0!


    Batman - I would love one column that contains the date in DD/MM/YYYYY. What I have been trying to do via text to column is to add zeroes to dates that are singles digits as I need the DD/MM/YYYY format e.g. if it is 9/3/2005 I try to make it 03/09/2005 (3rd September).


    Many thanks
    Reg

  • Re: Trouble With Hiding #DIV/0!


    Hi Reg,


    The code below seems to work for me. Let me know if you have any problems with it.



    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Text To Column Issue


    Hi Reg,


    because the different formatting it appears to be some values as string and other as number (the one with "." !!)


    try this out (maybe it is not the most elegant but it seems to work):



    HTH


    filo65


    P.S.
    Hopefully I understood right what u want to do!!

  • Re: Issue with format of dates


    How does the data get into man.xls?
    It is already wrong there. If the data is imported into man.xls, xl can be stopped from trying to interpret dates. At the moment, when it sees a valid English date it seems to flip month and day!
    If the file it is imported from is text, then Data|Import External Data|Import Data allows you to specify DMY or MDY


    I notice that the file is called 'man', is that because the data comes from the Man stocks and shares data feed supplier? If so, it looks like you've got future prices there - now that would be interesting!
    Pascal

  • Re: Trouble With Hiding #DIV/0!


    Hi Batman


    Thanks very much for your help. Unfortunately, I am having trouble with the code - can you tell me where to put the private function please?


    Many thanks
    Reg

Participate now!

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