+ Reply to Thread
Results 1 to 62 of 62

Unusual Date Format - need a fix

  1. #1
    Registered User
    Join Date
    12-11-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Unusual Date Format - need a fix

    Dec 18 2014 11:50AM

    Good morning, someone has sent me a spreadsheet with dates as above. I cannot re-format it as it appears to be an exported CSV file and all columns are set to General format. So I have the data but I cannot work with this format.

    I was thinking along the lines of setting up an Import Spec in Access to extract parts of this date, but i have 3 date columns like this and would be very fiddly, is there a way in Excel to easily extract the correct dd/mm/yy hh:mm format.
    Attached Files Attached Files
    Last edited by simon4amiee; 01-07-2015 at 05:07 AM.

  2. #2
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Unusual Date Format - need a fix

    hi simon4amiee

    Upload a sample work book with out any confidential data and make sure you have enough data to explain your requirement and also provided the expected results manually .

    Punnam

  3. #3
    Registered User
    Join Date
    12-11-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Unusual Date Format - need a fix

    I have attached a small sample and highlighted in red the problem columns and highlighted in Green what my required outcome would like to be.

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Unusual Date Format - need a fix

    =SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(E2,"AM"," AM"),"PM"," PM"))," ",", ",2)*1
    or
    =SUBSTITUTE(TRIM(LEFT(E2,LEN(E2)-2)&" "&RIGHT(E2,2))," ",", ",2)*1
    Try this in F2 and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Unusual Date Format - need a fix

    HI
    Copy the formula in
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in f2

    Punnam
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-11-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Unusual Date Format - need a fix

    Sorry matey this doesnt return the correct results, almost but the date on the spreadsheet you enclosed isnt the same date as the column you refer to.

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Unusual Date Format - need a fix

    Quote Originally Posted by simon4amiee View Post
    Sorry matey this doesnt return the correct results, almost but the date on the spreadsheet you enclosed isnt the same date as the column you refer to.
    =SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(E2,"AM"," AM"),"PM"," PM"))," ",", ",2)*1
    or
    =SUBSTITUTE(TRIM(LEFT(E2,LEN(E2)-2)&" "&RIGHT(E2,2))," ",", ",2)*1
    Try this in F2 and copy towards down
    These are working fairly
    try this if not working pls attach sheet with remarks where it is giving wrong result

  8. #8
    Registered User
    Join Date
    12-11-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Unusual Date Format - need a fix

    I think the issue is there is an extra space when only 1 digit in the first part of the date

  9. #9
    Registered User
    Join Date
    12-11-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Unusual Date Format - need a fix

    Quote Originally Posted by nflsales View Post
    =SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(E2,"AM"," AM"),"PM"," PM"))," ",", ",2)*1
    or
    =SUBSTITUTE(TRIM(LEFT(E2,LEN(E2)-2)&" "&RIGHT(E2,2))," ",", ",2)*1
    Try this in F2 and copy towards down
    These are working fairly
    try this if not working pls attach sheet with remarks where it is giving wrong result
    Here is the file with the above calculation in
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    12-11-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Unusual Date Format - need a fix

    Quote Originally Posted by Punnam View Post
    HI
    Copy the formula in
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in f2

    Punnam
    I think the issue is there is an extra space when only 1 digit in the first part of the date, which is returning an incorect date where there is two digits in the date, also the date format in the UK dd/mm/yyyy (eg 24/12/2014).

  11. #11
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Unusual Date Format - need a fix

    Hi try this in F2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Punnam

  12. #12
    Registered User
    Join Date
    12-11-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Unusual Date Format - need a fix

    Quote Originally Posted by Punnam View Post
    Hi try this in F2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Punnam
    Massive massive thanks matey, a true legend of the Excel world

  13. #13
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Unusual Date Format - need a fix

    HI simon4amiee ,

    welcome & tanks for the Reps

    Punnam

  14. #14
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Unusual Date Format - need a fix

    Quote Originally Posted by simon4amiee View Post
    Here is the file with the above calculation in
    See the attached format
    it is in number format
    change it in to Date format, I changed it for you
    Attached Files Attached Files

  15. #15
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Unusual Date Format - need a fix

    Otherwise try
    =TEXT(SUBSTITUTE(TRIM(LEFT(E2,LEN(E2)-2)&" "&RIGHT(E2,2))," ",", ",2)*1,"DD-MM-YYYY HH:MM")

  16. #16
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Unusual Date Format - need a fix

    Here's another formula that should work.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  17. #17
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Unusual Date Format - need a fix

    @ HI Norie ,

    Really it smart one ,
    can you explain me how "+0" is helpful ?

    Punnam

  18. #18
    Registered User
    Join Date
    12-11-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Unusual Date Format - need a fix

    Quote Originally Posted by Punnam View Post
    Hi try this in F2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Punnam
    Aww no matey, Ive just spotted something, its fantastic but its not displaying a 24 hour time thus 2:35PM is coming up as 02:35 and not 14:35 (is it easy to fix or add 12 on anywhere in the calculation if it states PM?)

  19. #19
    Registered User
    Join Date
    12-11-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Unusual Date Format - need a fix

    Quote Originally Posted by Norie View Post
    Here's another formula that should work.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This still shows the month as a name and not a number

  20. #20
    Registered User
    Join Date
    09-05-2013
    Location
    Private
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Unusual Date Format - need a fix

    Hi NFLsales,

    The attached file not solving.
    When I unprotect, enable editing, #value! error in all formula cells
    the "otherwise try same thing happens cells return #value! error

  21. #21
    Registered User
    Join Date
    09-05-2013
    Location
    Private
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Unusual Date Format - need a fix

    yes agree the AM/PM issue, I'm interested in if Punnam can add 12:00 If "*PM" in the same formula cell. You can add it via another cell.

  22. #22
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Unusual Date Format - need a fix

    May i know where it is giving wrong result

  23. #23
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Unusual Date Format - need a fix

    Quote Originally Posted by paulmacro View Post
    Hi NFLsales,

    The attached file not solving.
    When I unprotect, enable editing, #value! error in all formula cells
    the "otherwise try same thing happens cells return #value! error
    May i know where it is giving wrong result

  24. #24
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Unusual Date Format - need a fix

    it try this i am not sure but it will be helpful
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Punnam

  25. #25
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Unusual Date Format - need a fix

    using nflsales

    =TRIM(MID(E2,5,2) &" "&LEFT(E2,3)&" "&MID(E2,8,10)&" "&RIGHT(E2,2))+0
    that actually returns
    41991.49306
    dates/times are just a number string
    when formatted general
    all the info is there
    just custom format as
    mmm dd yyy hh:mm AM/PM
    Last edited by martindwilson; 01-07-2015 at 08:03 AM.
    "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

  26. #26
    Registered User
    Join Date
    12-11-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Unusual Date Format - need a fix

    Quote Originally Posted by Punnam View Post
    it try this i am not sure but it will be helpful
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Punnam
    Thanks again matey this may well be unsolvable, for it to be usable it has to be in dd/mm/yyyy hh:mm format using a 24h clock, again huge appriciation for the effort and far more than i could get.

  27. #27
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Unusual Date Format - need a fix

    Hi updated one
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Punnam

  28. #28
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Unusual Date Format - need a fix

    it doesnt matter
    if you want it usable in calculations it must be a number
    if you really want it as text
    =TEXT(TRIM(MID(E2,5,2) &" "&LEFT(E2,3)&" "&MID(E2,8,10)&" "&RIGHT(E2,2))+0,"dd/mm/yyyy hh:mm am/pm")
    or
    =TEXT(TRIM(MID(E2,5,2) &" "&LEFT(E3,3)&" "&MID(E2,8,10)&" "&RIGHT(E2,2))+0,"dd/mm/yyyy hh:mm")

  29. #29
    Registered User
    Join Date
    12-11-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Unusual Date Format - need a fix

    Quote Originally Posted by Punnam View Post
    Hi updated one
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Punnam
    Thanks again matey this may well be unsolvable, for it to be usable it has to be in dd/mm/yyyy hh:mm format using a 24h clock, again huge appriciation for the effort and far more than i could get. I dont need the AM/PM and the hours need to be 24 hour as its going to be exporting into Access and all our table have the date/time format

  30. #30
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Unusual Date Format - need a fix

    Hi check this one i think this the final one
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Punnam

  31. #31
    Registered User
    Join Date
    12-11-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Unusual Date Format - need a fix

    Quote Originally Posted by Punnam View Post
    Hi check this one i think this the final one
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Punnam
    Hero worship doesnt come close WOW WOW WOW

  32. #32
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Unusual Date Format - need a fix

    "doesnt come close" ?
    Punnam

  33. #33
    Registered User
    Join Date
    12-11-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Unusual Date Format - need a fix

    Quote Originally Posted by Punnam View Post
    Hi check this one i think this the final one
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Punnam
    Sorry again matey when the data is Dec 15 2014 12:05PM it is returning 15/12/2014 0:05:00 which isn't right

  34. #34
    Registered User
    Join Date
    12-11-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Unusual Date Format - need a fix

    Quote Originally Posted by Punnam View Post
    "doesnt come close" ?
    Punnam
    It means your better than the best of the best

  35. #35
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Unusual Date Format - need a fix

    date /times are serial numbers even if you just type
    07/01/2015 12:21 in a cell
    that is
    42011.51484 what you see depends on cell format. the underlying value will not change
    so do you mean you it as text or a valid date code

  36. #36
    Registered User
    Join Date
    12-11-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Unusual Date Format - need a fix

    Quote Originally Posted by martindwilson View Post
    date /times are serial numbers even if you just type
    07/01/2015 12:21 in a cell
    that is
    42011.51484 what you see depends on cell format. the underlying value will not change
    so do you mean you it as text or a valid date code
    It just needs to be recognised by Microsoft Access when Importing it so that we can use this field an all of our automated queries which ultimately calculate the difference between to date/times which is why the time is so important here.

  37. #37
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Unusual Date Format - need a fix

    Quote Originally Posted by simon4amiee View Post
    It means your better than the best of the best
    I am asking you from today morning i was not get proper response from you
    see the Column F & G of the attached file where it is getting wrong
    Attached Files Attached Files

  38. #38
    Registered User
    Join Date
    12-11-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Unusual Date Format - need a fix

    Quote Originally Posted by nflsales View Post
    I am asking you from today morning i was not get proper response from you
    see the Column F & G of the attached file where it is getting wrong
    It looks great buddy but the second i enter into the call and come out I get #Value again and I tried copying both forumlas into this and it kick out the exact same result #VALUE even in the worksheet you provided buddy

  39. #39
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Unusual Date Format - need a fix

    I think the problem with 2003 version of excel
    in my system it is showing correct result

  40. #40
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Unusual Date Format - need a fix

    here are your options and afik access date serials are the same as excel
    maybe regional settings are coming into play what are yours?
    mine will not recognise
    dec 11 2014 as a valid date
    but will recognise
    11 dec 2014
    also the date system in excel options is it 1900 or 1904?
    Attached Files Attached Files
    Last edited by martindwilson; 01-07-2015 at 09:07 AM.

  41. #41
    Registered User
    Join Date
    09-05-2013
    Location
    Private
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Unusual Date Format - need a fix

    Can solve with Punnam formula but requires a copy & paste and an extra column. Here's an example with final solution in column J, converted to number so you can change format easilyunusualdate2.xlsx

  42. #42
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Unusual Date Format - need a fix

    The formula, below, I posted will return 'real' dates.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you want to change how it's displayed change the formatting.

    For example if you want it to be displayed like this 18 Dec 2014 11:50 AM use this format string.

    d mmm yyyy hh:mm AM/PM
    Last edited by Norie; 01-07-2015 at 09:07 AM.

  43. #43
    Registered User
    Join Date
    12-11-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Unusual Date Format - need a fix

    Quote Originally Posted by martindwilson View Post
    here are your options and afik access date serials are the same as excel
    maybe regional settings are coming into play what are yours?
    mine will not recognise
    dec 11 2014 as a valid date
    but will recognise
    11 dec 2014
    also the date system in excel options is it 1900 or 1904?
    Thanks matey, was hoping to skip the manual formatting of the column though, but they do do the job, thank you very much

  44. #44
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Unusual Date Format - need a fix

    Hi one more update but surly i will say this is very lengthy process compared to "Martindwilson" function
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Punnam

  45. #45
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Unusual Date Format - need a fix

    simon4aimee

    If you are importing the data into Access there's no need to format, Access will recognise the dates as dates however they are formatted and treat them as such.

  46. #46
    Registered User
    Join Date
    09-05-2013
    Location
    Private
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Unusual Date Format - need a fix

    [QUOTE=Norie;3948356]The formula, below, I posted will return 'real' dates.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you want to change how it's displayed change the formatting.


    WELL DONE that's the best one yet. can you explain how that works or suggest one for this eg 2015-Dec-08 00:05
    Last edited by paulmacro; 01-07-2015 at 09:38 AM.

  47. #47
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Unusual Date Format - need a fix

    not mine nflsales has credit for that but it could be
    my most last post#40 has more to do with it

  48. #48
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Unusual Date Format - need a fix

    paulmacro

    In the that formula I've used +0 to coerce the 'text' date to a 'real' date, you could also use *1 as nflsales did in the formula(s) they posted.

  49. #49
    Registered User
    Join Date
    09-05-2013
    Location
    Private
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Unusual Date Format - need a fix

    Thanks Norie , I'll need to think about that & try to download again nflsales , in meantime how would I easily convert this format eg
    2015-Dec-08 00:00

    thanks again

    Paul

  50. #50
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Unusual Date Format - need a fix

    Paul

    That looks like a valid date format, are you having problems with data that contains the date formatted like that?

  51. #51
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Unusual Date Format - need a fix

    i uploaded a workbook with all your formatting choices at post #40,please don't just skip threads

  52. #52
    Registered User
    Join Date
    09-05-2013
    Location
    Private
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Unusual Date Format - need a fix

    unusualdate2.xlsx
    Norie, here is the spreadsheet with the various solutions from us on it and my problem date 2015-Dec-08 highlighted
    my date eg is imported via "text to columns" and appears to lose its date format so how can I get it back?
    Martin, downloaded your post'40 can this help? Nflsales, please put your solution on here or I'll try to download later.

    Thanks

    Paul
    Last edited by paulmacro; 01-07-2015 at 12:24 PM.

  53. #53
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Unusual Date Format - need a fix

    thats not a valid date entry where is the day? , so excel doesn't recognise it as such if you look it is aligned lef also if you put =istext(e18) it returns true
    Last edited by martindwilson; 01-07-2015 at 12:45 PM.

  54. #54
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Unusual Date Format - need a fix

    Paul

    I'm getting a bit confused.

    That's the workbook the OP originally uploaded, don't you have your own workbook?

    Anyway, for the 'date' in E18 try this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  55. #55
    Registered User
    Join Date
    09-05-2013
    Location
    Private
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Unusual Date Format - need a fix

    unusualdate3.xlsx

    Thanks all, here is OP workbook (converted to my 2010 xl version) with all the different solutions, Each author & result. Add more solutions if you wish or mark as solved?
    Nplsales still gives an error. Martins says true. Punnam or Norie can solve.

    Thanks Norie, I'll try that formula in my own workbooks, I tried to simplify by using the same workbook sorry if that was confusing, OP in this case, and see if any solution would fit my text to column import, unusual date.
    Cheers!
    Paul

    PS 11:50PM changed from 11:50AM at E2 ran ok as does 12:50PM
    PPS when I unprotect, enable editing, of Nplsales (Siva) documents the results of the formula change to give a #value! result (I don't know why that happens)
    Last edited by paulmacro; 01-08-2015 at 05:55 AM. Reason: PPS

  56. #56
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Unusual Date Format - need a fix

    see the attached file
    Attached Files Attached Files

  57. #57
    Registered User
    Join Date
    09-05-2013
    Location
    Private
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Unusual Date Format - need a fix

    Hi Siva,

    See my PPS on #55, your result changes to #value!. Can you add your solution to this document please. Where it states #value! unusualdate3.xlsx


    Thanks

    Paul

  58. #58
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Unusual Date Format - need a fix

    see the attached file with colored columns
    Attached Files Attached Files

  59. #59
    Registered User
    Join Date
    09-05-2013
    Location
    Private
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Unusual Date Format - need a fix

    Hey Siva nflsales,

    Excellent solution, most robust. Can you fix my Text to Column date eg 2015-Dec-08 00:04 here its is on OP workbook at E14 unusual-date4.xlsx

    Thanks very much

    Paul

  60. #60
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Unusual Date Format - need a fix

    Paul

    Could you start a new thread and upload your own workbook?

  61. #61
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Unusual Date Format - need a fix

    i didn't get what you are asking
    for which date, where it was and how is your expected result

  62. #62
    Registered User
    Join Date
    09-05-2013
    Location
    Private
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: Unusual Date Format - need a fix

    Hi again,

    Norie & Siva : I'll start a new thread in Excel General called "Recover Date from Text to Column and Concatenate" with Norie formula in my workbook

    Best Regards

    Paul

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Converting unusual time format
    By essee in forum Excel General
    Replies: 23
    Last Post: 09-24-2012, 08:36 AM
  2. Counting for an unusual time format.
    By percyth1 in forum Excel General
    Replies: 5
    Last Post: 01-31-2012, 11:08 PM
  3. Unusual Number Format With DDE
    By gpercy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-21-2008, 11:44 AM
  4. Text Import Unusual Format
    By rerhart in forum Excel General
    Replies: 2
    Last Post: 01-02-2007, 04:07 PM
  5. [SOLVED] Unusual data format
    By Salty in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-03-2005, 08:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1