Format to date

Microsoft Windows xp inside out, second...
December 9, 2009 at 02:43:05
Specs: Windows XP, pentium 4 3.00Ghz. 1gb ram
How i can format the following to date in col D of my worksheet?


that is, if month number is greater than 12; convert it to date and date number into month. However if both the month and date number is greater than 12, give error "wrong date format"

See More: Format to date

Report •

December 9, 2009 at 04:36:17
Are users entering these dates?

If so, you might be able to use Data Validation - Allow: Date with an error message that shows the correct format.

Excel will try to format the date with whatever format is set for the cell and if it can't it will pop up the error message.

If the cell is formatted to 12-25-2009 and the user enters 25-12-2009 the error message will pop up showing the user the correct format, e.g.

"Please enter your dates as MM-DD-YYYY. Example: December 25, 2009 should be entered as 12-25-2009."

Report •

December 9, 2009 at 06:03:37

As DerbyDad03 says, use cell data validation to control the date entry into the cells.

You will still have problems when someone enters a valid date but has swapped day and month, e.g., 10/9/2009 rather than 9/10/2009. No formula will detect that.

I suggest that as well as data validation you format the date entry cells with dd/mmm/yyyy or similar to show the month in text 09/Oct/2009. This gives the user entering the data a better chance of getting it right.

If you know that date entry should always be in a specific range, put formulas in the two cells used in the date validation when you use 'allow date between'.

A formula such as =TODAY()+30 will limit dates to not later than 30 days from today. =EOMONTH(TODAY(),1) will allow dates up to the last day of next month.
For earliest day you might use TODAY() or TODAY()-1.

If you want to really guarantee that the date is OK, you can add Microsoft's calendar. Look at this from Martin Green's site.


Report •

December 9, 2009 at 06:24:34
Other options include:

1 - Drop Down lists to force the user to select a month, day and year.

2 - A SelectionChange macro to present a message telling the user what format to use.

3 - A SelectionChange macro to gather the user's input, ask if the month/day order is correct, etc.

I think the bottom line is that dealing with months greater than 12 is doable within Excel, but verifying that a user entry of 4/3/2009 should really be 4/3/2009 and not 3/4/2009 isn't going to be possible without user intervention.

Unfortunately, we all know what happens when we get users involved.

Report •

Related Solutions

December 9, 2009 at 16:45:44
Thanks DerbyDad03 and Humar for your suggestion. I am thinking if a macro could identify date format mm/dd/yyyy then convert it to dd/mm/yyyy in the selected range. The fact is i used to get worksheets from different people having date format differently; some of them use date format mm/dd/yyyy while majority of them use this date format dd/mm/yyyy. Then i have to compile them together by copy paste in a single sheet in my computer.

Now i want all dates to be in this format: dd/mm/yyyy. As DerbyDad03 pointed out about 4/3/2009, let it be so as long as 3(month in my case) is not greater than 12.

The last word is: Let macro fix the date format to dd/mm/yyyy and then if macro finds that mid i.e. month number to be greater than 12 then interchange date and month.

Report •

December 9, 2009 at 20:03:09

Assuming that every workbook was completed with dates that appeared correct to the user, either dd/mm or mm/dd, and if you import the workbooks as Excel workbooks (not text or csv files) then the cells in each workbook should be formatted either dd/mm/yy or mm/dd./yy.

So take the cells with dates in each imported workbook and format the date cells as dd/mmm/yyyy.

All cells containing dates should now show the dates in the same formats.

Without seeing your source data, this is difficult, but the main issue is that Excel converts all entered data that it recognizes as dates into numeric values. What you see is the formated data. So whatever you see is just based on formatting.

Take each Workbook that you receive, and before copying/pasting select all cells with a date and format them dd/mmm/yy.

Now copy and paste.

All pasted date cells will have the correct date.


Report •

December 10, 2009 at 01:35:24
Humar, you can type the following in excel 2003 and then tried to format it like 14-Mar-2009 date format.


I think one of them will turn into 15-Mar-2009 while other remain as it is. I want both of them to be 15-Mar-2009. But How? Please take note of my response No.4 2nd &3rd para.

Report •

December 10, 2009 at 08:34:40

The problem you have, using your example is that in your example:

the first of these dates is accepted by Excel as a date and the second is not accepted as a date and is left as text - which is why it doesn't change when formatted.

A macro can be created which tests if any of the 'dates' in a worksheet or copied column of dates are text rather than real Excel dates, and then convert all of them, but if all of the 'dates' are Excel dates, then leave them as they are.

This would miss a situation where all the dates were entered in mm/dd/ order but all were valid dates.

Here is a macro that will undertake a conversion:

Option Explicit
Public Sub DateConv()
Dim rngArry() As Range
Dim intTest As Integer
Dim rngCell As Range
Dim strDy As String
Dim strMo As String
Dim strYr As String
Dim blnAllDates As Boolean
Dim int1 As Integer
Dim int2 As Integer
Dim n As Integer

On Error GoTo ErrHnd

'change for number of ranges to test
intTest = 2

'resize array to match
ReDim rngArry(intTest - 1)

'setup ranges to test
Set rngArry(0) = Range("B2:B5")
Set rngArry(1) = Range("D2:D5")

For n = 0 To intTest - 1
    'test if all cells are dates
    'use Value2 which is a number if this is a date
    'else its text
    blnAllDates = True
    For Each rngCell In rngArry(n).Cells
        If Not IsNumeric(rngCell.Value2) Then
            'not a date so change flag to false
            blnAllDates = False
            'we only need one cell to not be a date
            'so no need to test any more
            Exit For
        End If
    Next rngCell
    'change cells if one of them was not a date
    If blnAllDates = False Then
        For Each rngCell In rngArry(n).Cells
            'find the position of the two "/"s in the text
            int1 = InStr(1, rngCell.Text, "/")
            int2 = InStr(int1 + 1, rngCell.Text, "/")
            'get date components
            strDy = Mid(rngCell.Text, int1 + 1, int2 - int1 - 1)
            strMo = Left(rngCell.Text, int1 - 1)
            strYr = Right(rngCell.Text, Len(rngCell.Text) - int2)
            'if month not numeric, convert it
            If Not IsNumeric(strMo) Then
                Select Case strMo
                    Case "Jan", "January"
                    strMo = "01"
                    Case "Feb", "February"
                    strMo = "02"
                    Case "Mar", "March"
                    strMo = "03"
                    Case "Apr", "April"
                    strMo = "04"
                    Case "May", "May"
                    strMo = "05"
                    Case "Jun", "June"
                    strMo = "06"
                    Case "Jly", "July"
                    strMo = "07"
                    Case "Aug", "August"
                    strMo = "08"
                    Case "Sep", "September"
                    strMo = "09"
                    Case "Oct", "October"
                    strMo = "10"
                    Case "Nov", "November"
                    strMo = "11"
                    Case "Dec", "December"
                    strMo = "12"
                    Case Else
                    GoTo ErrHnd
                End Select
            End If
            'put new date into next column & format it
            rngCell.Offset(0, 1).Value = DateSerial(strYr, strMo, strDy)
            rngCell.Offset(0, 1).NumberFormat = "dd/mmm/yy"
            Next rngCell
        'just copy and format
        For Each rngCell In rngArry(n).Cells
            rngCell.Offset(0, 1).Value = rngCell.Value
            rngCell.Offset(0, 1).NumberFormat = "dd/mmm/yy"
        Next rngCell
    End If
Exit Sub

'error handler
End Sub

In this example it runs on two ranges B2 to B5 and D2 to D5.

You can change the ranges as required and change the number of ranges it runs on.

Here is the before and after

Mixed		PostMacro	Excel dates	Post Macro
2/16/2009			02/10/09	
3/15/2009			28/03/09	
02/10/09			02/10/09	
11/11/09			11/11/09	
Mixed		PostMacro	Excel dates	Post Macro
2/16/2009	16/Feb/09	02/10/09	02/Oct/09
3/15/2009	15/Mar/09	28/03/09	28/Mar/09
02/10/09	10/Feb/09	02/10/09	02/Oct/09
11/11/09	11/Nov/09	11/11/09	11/Nov/09

NOTE: There is NO undo with a macro.
Test this on copies of workbooks, and always make a backup before running it on real data.

This macro may not work in all circumstances. Some date formats move the month and day depending on regional settings in Windows, also some incoming dates may have been entered with separators other than "/"

Hope this gives you something to work on, to solve your problem.


Report •

December 10, 2009 at 23:48:09
Thanks for your hard work for me. Excel have a format option that works similar to your code.I have dd/mm/yyyy date format In regional settings in Windows. Thus:
15/3/2009  - excel identify this as date and your code convert this to 15-Mar-2009
But 3/15/2009 connot be identify as date and your code also 
doing noting on this whereas this is my main problem.

Can you provide a code or function that convert 3/15/2009 and 15/3/2009 to 15-Mar-2009 date format?

Please type this date in your excel
25/6/2009 then
If the code convert both of this date to 15-Mar-2009 date format, consider I got what i need.

Report •

December 11, 2009 at 04:42:42

If you look at the before and after I provided - in the first column 'Mixed' the first two dates were not recognized as dates by Excel. The code I provided recognizes this and converts both of them - 2/16/2009 to 16/Feb/2009 and 3/15/2009 to 15/Mar/2009.

The formatting part of the code I gave you was only a minor part of the macro. The code actually converts these unrecognized dates into proper Excel dates.

Note also that 02/10/2009 is handled differently. In the Mixed column, because some dates are not recognized by Excel, the whole column is treated as mm/dd/ and 02/10/2009 becomes 10/Feb/2009.
In the Excel column, all dates were recognized by Excel as dates and treated as dd/mm and 02/10/2009 was treated as 02/Oct/2009.

I tested your example:

PreMacro	PostMacro
15/03/09	15/Mar/09
25/06/09	25/Jun/09

PreMacro	PostMacro
3/15/2009	15/Mar/09
6/25/2009	25/Jun/09


Report •

Ask Question