Share via

date format problem

Anonymous
2014-03-16T03:41:40+00:00

hi every one hope u all r doing nice there. i have some date format problems my erp softwear provide my delivery date like bellow my computer setting & all data sate date format like dd/MMM/yyyy now i want to to convert erp date data to dd/mmm/yyyy how to do this. this is how erp softwear date like bellow

3/13/2014 9:43:58 AM will be like 13-Mar-2014
3/15/2014 12:12:38 PM will be like 15-Mar-2014
03.03.14 10:11 will be like 03-Mar-2014
03.09.14 16:35 will be like 09-Mar-2014
03.05.14 19:21
03.05.14 19:22
03.11.14 9:26
2/22/2014 2:26:42 PM

 now how to do this please help

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

16 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-03-16T04:04:14+00:00

    This is one way to do the changes if the dates are text and not recognized as dates at all (as the next post assumes).  Try Sheeloo's method first and if it does not work, then try these formulas.

    Assuming one of the dates is in cell A2, try this formula and format the cell as you desire:

    =IF(ISERR(DATE(MID(A2,FIND(" ",A2)-4,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2))),DATE(MID(A2,7,2),LEFT(A2,2),MID(A2,3,2)),DATE(MID(A2,FIND(" ",A2)-4,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2)))

    Now - if for those dates in the format 03.05.14  show up as 1914 instead of 2014, then change the formula to this:

    =IF(ISERR(DATE(MID(A2,FIND(" ",A2)-4,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2))),DATE(MID(A2,7,2)+100,LEFT(A2,2),MID(A2,3,2)),DATE(MID(A2,FIND(" ",A2)-4,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2)))

    This formula assumes that dates of the format 3/3/2014 have a space after the 2014 followed by the time of day as you have shown.  It also assumes that the 03.05.14 type dates have 2-digit days and months in all cases.

    There should be a date format available to provide you with the dd-mmm-yyyy format, but if there is not one, then use a custom format like that.  That is the custom format entry would be:

    dd-mmm-yyyy

    After getting the dates converted you can select all of the cells with the formulas in them and use Edit Copy and then select all of the bad date entries and use Edit  Paste Special with the Values option selected.  That may cause the formulas to return errors or strange results, but it does not matter - after doing the Paste Special with Values, you may delete all of the formulas.

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2014-03-16T05:43:51+00:00

    When you bring the data into Excel, use the Text Import Tool and specify a MDY incoming format.

    Better yet, adjust the ERP export procedure to deliver the date int the same system format that you prefer to use.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-03-16T05:41:46+00:00

    sir i failed to apply step no. 4 * i.e. *

    i am adding some more sample

    01.03.13 16:18
    4/24/2013 2:28:12 PM
    01.11.13 11:30
    1/17/2013 5:21:57 PM
    6/17/2013 6:36:31 PM
    01.02.13 14:59
    01.03.13 19:42
    2/13/2013 4:27:50 PM
    1/17/2013 5:30:02 PM
    01.10.13 14:44
    1/30/2013 7:44:58 PM
    1/21/2013 9:23:15 AM
    01.02.13 19:28
    01.09.13 15:53
    01.05.13 15:44
    1/26/2013 5:13:44 PM
    1/17/2013 5:23:38 PM
    01.09.13 18:14
    01.03.13 19:28
    01.06.13 18:20
    01.02.13 18:27
    01.02.13 18:31
    1/16/2013 10:56:29 AM
    1/23/2013 3:04:17 PM
    02.01.13 14:52
    1/30/2013 6:44:15 PM
    02.09.13 12:26
    04.02.13 16:23
    01.09.13 16:05
    01.09.13 16:06
    01.09.13 15:49
    1/22/2013 6:22:48 PM
    1/22/2013 6:19:30 PM
    1/23/2013 8:01:16 PM
    1/19/2013 6:51:42 PM
    5/25/2013 11:13:16 AM
    01.10.13 12:11
    01.10.13 12:04
    01.02.13 19:39
    01.02.13 19:42
    01.02.13 19:40
    1/28/2013 11:40:18 AM
    6/29/2013 3:57:41 PM
    01.01.13 16:39
    3/24/2013 3:42:42 PM
    1/19/2013 11:41:53 AM
    01.05.13 12:15

    Was this answer helpful?

    0 comments
  4. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2014-03-16T04:16:30+00:00

    Hi,

    Try this

    1. Select the column of data and press Ctrl+H
    2. In the Find What box, type . and in the Replace With box, type /.  Click on Replace All
    3. Select the column of data and press Ctrl+H
    4. In the Find What box, type  * i.e. space and star
    5. Click on Replace All
    6. Select the column of data and sort the column in ascending order
    7. Select the column of data and apply a Custom Filter > *
    8. Select the resulting entries and go to Data > Text to columns > Delimited > Next > Next > Date > MDY
    9. Clear the Filter and format the column of data and Date > DMY

    Hope this helps.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2014-03-16T03:53:15+00:00

    Select the column with dates then click on the down arrow in Number group under Home tab..

    In the dialogbox choose the format

    dd/mm/yyyy

    confirm you choice 

    I am assuming your dates are like this in the column

    03.05.14 19:21
    03.05.14 19:22
    03.11.14 9:26

    Was this answer helpful?

    0 comments