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-06-11T08:35:53+00:00

    Hi Ashish,

    I have a worksheet that has a collection of dates and times in one column that were consolidated from multiple worksheets. They appear to be in three distinct formats: one format is DD/MM/YYYY HH:MM (e.g. 14/09/2013 22:30); one format is MM/DD/YY HH:MM AM/PM (e.g. 09/14/13 10:30 PM); and, the last format is MM/DD/YYYY HH:MM (e.g. 09/17/13 15:30). How can I change that so that they all have the DD/MM/YYYY HH:MM format?

    These values are pre-formatted as Text values and are not responding to re-formatting to fix the problem.

    Regards,

    Lindsay_80

    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
  2. Ashish Mathur 102K Reputation points Volunteer Moderator
    2014-03-16T11:20:46+00:00

    Hi,

    Try it yourself and post back with problems, if any.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-03-16T09:20:21+00:00

    https://onedrive.live.com/redir?resid=194A285C8E4498E1%21109

    here is ling of my file can u check ur soluation work on it.

    sorry to disturb u again

    Was this answer helpful?

    0 comments
  4. Ashish Mathur 102K Reputation points Volunteer Moderator
    2014-03-16T06:40:05+00:00

    You are welcome.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2014-03-16T06:32:19+00:00

    thank u very much i have done it

    Was this answer helpful?

    0 comments