We help IT Professionals succeed at work.

Date Format

Cartillo
Cartillo asked
on
Hi Experts,

I would like to request Experts help create a macro to condition the date format that was entered at “Data” sheet (A6:A900) and at B2:H2 in Week 1 to Week5 sheets. The date needs to be in this format:  d/m/yyyy. Hope Experts could help me to add this feature.



Date.xls
Comment
Watch Question

Commented:
Why don't you just change those cell's formatting?

In Excel 2010, right-click those cells and select Format Cells.... On the Number tab, select Date, and choose a format. If you don't see one you like, create a custom one.

Lee

Author

Commented:
Hi,

I have tested that option but sometime the data that I have copied from the source in this workbook are always in different date format setting which is always create a problem. Hope you will consider my request.  
CERTIFIED EXPERT
Commented:
This should format the range you requested.

Flyster
Sub FormatDate()

    Range("A6:A900").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "d/m/yyyy"
    Sheets("Week1").Select
    Range("B2:H2").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "d/m/yyyy"
    Sheets("Week2").Select
    Range("B2:H2").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "d/m/yyyy"
    Sheets("Week3").Select
    Range("B2:H2").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "d/m/yyyy"
    Sheets("Week4").Select
    Range("B2:H2").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "d/m/yyyy"
    Sheets("Week5").Select
    Range("B2:H2").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "d/m/yyyy"
    
End Sub

Open in new window

Most Valuable Expert 2012
Top Expert 2012

Commented:
Not for points.

The code can be optimized to:

    Dim wks As Worksheet
   
    For Each wks In ActiveWorkbook.Worksheets
         If UCase(Left(wks.Name, 4)) = "WEEK" Then
            wks.Range("B2:H2").NumberFormat = "d/m/yyyy"
         End If
    Next wks

No need to select the sheets/ranges or reset the CutCopyMode property.

Cheers,

Dave
CERTIFIED EXPERT

Commented:
@ Dave. You should get points for that. It's sweet!

Paul
Most Valuable Expert 2012
Top Expert 2012
Commented:
Ok - I'm happy to share collaboration points, as  there's a bit more work to be done on the first sheet - need to identify which cells to change format to.  The app looks at the existing numberformat for m/d/yyyy and then changes it if it finds that format to d/m/yyyy

Here's the code:

 
Sub formatDates()
Dim wkb As Workbook
Dim wks As Worksheet
Dim fRange As Range

    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Data")
    
        
    For Each mycell In wks.Range("A1", wks.Range("A" & wks.Rows.Count).End(xlUp))
        If Trim(mycell.NumberFormat) = "m/d/yyyy" Then 'find old format
            mycell.NumberFormat = "d/m/yyyy"
        End If
    Next mycell
    
    For Each wks In wkb.Worksheets
         If UCase(Left(wks.Name, 4)) = "WEEK" Then
            wks.Range("B2:H2").NumberFormat = "m/d/yyyy"
         End If
    Next wks

End Sub

Open in new window


Dave
Date-r1.xls

Author

Commented:
Hi Dave,

I have tested with with few data and noticed the date was not converted into d/m/yyyy. Attached the workbook for your kind perusal.
Date.xls
Most Valuable Expert 2012
Top Expert 2012

Commented:
Specifically - what cells are not formatted correctly?

Dave

Author

Commented:
Hi Dave,

E.g. A6,A37, A356.
Most Valuable Expert 2012
Top Expert 2012

Commented:
Glad you're still on.  A6, A37, and A356 are already formatted d/m/yyyy.

To your eyes, it might look like m/d/yyyy, but the computer sees it as d/m/yyyy.  The macro doesn't know whether you typed it in one way or the other - neither would I.

If some of them look right, and some look wrong, that's because they were probably entered one way and then the other.

Am I making any sense?  What would you suggest might be done to rectify?

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
What I'm trying to say is that in the ORIGINAL dataset, someone entered the data, and Excel took it in and identified the day, month, and year.  All we're doing is changing the format, NOT interpreting the data.  I'm not sure how we'd do that if the person inputting the data mis-entered the data at times.

Let me look at it again - can you upload a dataset that has NOT had this macro run against it?  I need to see what it originally looked like.

By inspection, the original post I submitted correctly transformed everything from your original dataset in the original question.  So, I need a new starting point.

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
My last post on this till I get your feedback - just to be more clear, as I'm not sure I have been up to this point...

A6 - when formatted mmm-dd-yy is January 11, 2011
A37 is February 11, 2011
A357 is not a date, re: 13/11/11

If I were to assume that the data in the same format as A6 (ASSUME its November 1st because its 11/1/11) then what is A357?  What is 13/11/11?  13th month?

We need a consistent way to interpret the data - its either already in m/d/yyyy format and transform to d/m/yyyy; or what?

:)

Dave

Author

Commented:
Hi Dave,

You're right. The system not interpreting the data and the user need to be sensitive when updating/entering this type of data. Your code is just nice for me to identify and correct it manually if such entry exist.    
Most Valuable Expert 2012
Top Expert 2012

Commented:
You might format those cells as MMM-DD-YYYY, then set the macro up to convert to d/m/yyyy after?

That way the user would know???

Dave

Author

Commented:
Hi Dave,

That's good idea too. So that user totally aware whether the entered date is current or not immediately.

Author

Commented:
Hi,

Thanks a lot for the help

Explore More ContentExplore courses, solutions, and other research materials related to this topic.