none
format from dd-mm-yyyy to dd/mm/yyyy?

    Question

  • Every month I receive a whole bunch of invoices from a vendor and  noticed that most of the dates are formatted like dd/mm/yyyy (which is perfect). However now and then some dates are formatted like dd-mm-yyyy (not good). Is there a way (simple way) to reformat those dates dd-mm-yyyy to dd/mm/yyyy????

     

    Thanks for any help!


    Rik
    Wednesday, August 10, 2011 12:38 PM

Answers

  • The cells are probably interpreted as text.

    Try the following:

    Select a single column with wayward dates.

    Activate the Data tab of the ribbon.

    Click Text to Columns.

    Select Delimited, then click Finish.


    Regards, Hans Vogelaar
    Thursday, August 11, 2011 2:20 PM

All replies

  • Select the entire range with dates.

    Press Ctrl+1 to activate the Format Cells dialog.

    In the Number tab, select the Date category, then select the dd/mm/yyyy format and click OK.


    Regards, Hans Vogelaar
    Wednesday, August 10, 2011 2:11 PM
  • Hi Rik,

    You can use a simple VBA macro to accomplish this:

    Sub checkFormat()
    
    Application.ScreenUpdating = False
    Dim sheet As Worksheet
    'specify a worksheet to change date format
    Set sheet = ThisWorkbook.Worksheets(1)
    
    Dim sRange As Range
    Set sRange = sheet.UsedRange
    
    Dim cell As Range
    'check whether a used cell is in "dd-mm-yyyy" format
    For Each cell In sRange.Cells
    
    If cell.NumberFormat = "dd-mm-yyyy" Then
    cell.NumberFormat = "dd/mm/yyyy"
    
    End If
    Next cell
    Application.ScreenUpdating = True
    
    End Sub
    

    I hope this helps.


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 11, 2011 10:13 AM
  • If you get that data as a text file (like *.txt or *.csv), make sure you're
    importing the date fields correctly.

    Don't let excel guess. 

    If the data is ambiguous, like:  01/02/03
    then excel might use your windows short date setting order (mdy, dmy, ymd, etc)
    to determine what it thinks is the date.

    I try to make sure I rename the .csv files to .txt and use the text import
    wizard to specify each field to the correct order.  You could record a macro
    that you could re-run on demand, too.

    If you blindly let excel guess the real date based on that ambiguous string,
    then you may convert the data to something that looks like dates (and format
    them in the order you want), but the underlying value isn't what the sender
    intended.

    Be careful with importing date strings.

    Cire1507 wrote:


    Every month I receive a whole bunch of invoices from a vendor and  noticed that most of the dates are formatted like dd/mm/yyyy (which is perfect). However now and then some dates are formatted like dd-mm-yyyy (not good). Is there a way (simple way) to reformat those dates dd-mm-yyyy to dd/mm/yyyy????



    Thanks for any help!

    --
    Rik

    --

    Dave Peterson

    Thursday, August 11, 2011 11:27 AM
  • I have tried that but the dates still remaain formatted like dd-mm-yyyy.
    Rik
    Thursday, August 11, 2011 12:07 PM
  • Tried that but all dates formatted like dd-mm-yyyy do not change to format dd/mm/yyyy :-(
    Rik
    Thursday, August 11, 2011 12:08 PM
  • Dave I get the information in Excel format: file.xlsx

     


    Rik
    Thursday, August 11, 2011 12:11 PM
  • The cells are probably interpreted as text.

    Try the following:

    Select a single column with wayward dates.

    Activate the Data tab of the ribbon.

    Click Text to Columns.

    Select Delimited, then click Finish.


    Regards, Hans Vogelaar
    Thursday, August 11, 2011 2:20 PM