Rectangle 27 2

As Assylias mentioned, some dates can be ambiguous. To Excel a date is nothing but a formatted number which represents the number of days since 01/01/1900, today (March 3, 2016) to excel is nothing more than 42447. When using the number, there can be no ambiguity about the date format.

ws.Cells(iRow, x + 1) = lineArr(x)
With ws.Cells(iRow, x + 1)
    If x = 0 Then
        .Value = CLng(CDate(lineArr(x)))
        .NumberFormat = "mm/dd/yyyy;@"
        .Value = lineArr(x)
    End If
End With

had the same idea before seeing your post - this worked: CDate(lineArr(0))

I understand CDate to first attempt a conversion in the EN-US MDY format; then in the DMY format if it cannot reconcile the >12 month. I see this as producing a mashup of dates, not a true conversion.

@Jeeped, It depends on what the country and region settings of the pc and the locale settings of Office are, if the system default is MDY but the source is DMY this will most likely cause errors, very true.

I'm going to have to flip another machine over to DMY to test this. I did it a while ago and the VBA tendency toward EN-US locale overrode any system regional settings. Perhaps I erred last time or the system has been improved.

Excel VBA importing .txt file results in wrong date format - Stack Ove...

excel vba excel-vba date datetime
Rectangle 27 1

Excel first tries to convert a date string to the format of the local setting. If it fails, like when the month is superior to 12, it will then inverse the month and the days. Since you are dealing with the two types of formats, you best option is probably to parse the dates yourself:

Sub Macro1()
  Dim arr() As String
  Dim mydate As Date

  ' US format 6 may 2015
  arr = Split("05/06/2015", "/")
  mydate = DateSerial(Year:=arr(2), Month:=arr(0), Day:=arr(1))

  ' UK format 6 may 2015
  arr = Split("06/05/2015", "/")
  mydate = DateSerial(Year:=arr(2), Month:=arr(1), Day:=arr(0))

End Sub

Excel VBA importing .txt file results in wrong date format - Stack Ove...

excel vba excel-vba date datetime
Rectangle 27 1

You don't need VBA/RegEx. Select the cells/columns where you input dates and create a Custom number format: dd/mm/yyyy. Now no matter how you type in a valid date (05-05-2000, 3-1-2010, 14/6-1990, etc.), it should be formatted as dd/mm/yyyy.

And, as Olle points out, you should use the Date object rather than Variant if you are going to be manipulating dates in VBA. This way you're working with the serial number and not a string with potential formatting issues.

vba - convert date in excel from dd-mm-yyyy to dd/mm/yyyy format and t...

excel vba excel-vba
Rectangle 27 0

Assuming your happy with the formatting that is in the copy fields then you can just do a simple paste instead of paste special. Right now your code is only pasting values because of this line:


You can also just change it to this instead:


vba - Microsoft Excel Copy and Paste with Format of Date - Stack Overf...

excel vba date copy paste
Rectangle 27 0

Sub sortFuel()
    Dim D1 As Long, D2 As Long

    With UserForm1
        With .dp_StartDate
            startDate = Format(.Value, "dd/mm/yyyy")
        End With
        With .dp_EndDate
            endDate = Format(.Value, "dd/mm/yyyy")
        End With
    End With

    D1 = Val(Format(startDate, "0"))
    D2 = Val(Format(endDate, "0"))

    With ws
        .ListObjects("Table_DFDBMain_FuelTrans4").Range.AutoFilter Field:= _
        3, Criteria1:=">=" & D1, Operator:=xlAnd, Criteria2:="<=" & D2
    End With
End Sub

Date from vba userform datepicker passed used to sort excel table wron...

excel excel-vba excel-2007 userform
Rectangle 27 0

I have this issue with my forms too and the best method for me is to format the textbox like this:

sDate = format(SourceSheet.Range("AB" & CurRow.Row).Value, "mm/dd/yyyy")

Even though the date format is wrong in VBA, it seems to work the right way round in Excel. It's weird, I can't explain why it happens, but this fixes it for me. Whenever I go from VBA to Excel, I almost always find this issue if the value is stored as a date.

It works !! I really can't understand the logic behind, as you said it's weird. Thanks.

excel - VBA read wrong date format - Stack Overflow

excel excel-vba
Rectangle 27 0

well, unfortunately that is not something that I require, I need the format to be "yyyy-mm-dd hh:mm:ss" due to the fact, that datetime stamps are stored in my database that way and this cell is going to be used as part of a query.

Can't format the date properly in VBA, Excel 2012 - Stack Overflow

excel vba date excel-2010
Rectangle 27 0

You will have to trace back to your source data. There is no way Excel itself knows whether 1/2/2014 for example should be the first of February or the second of January, only that it is either 41671 or 41641.

Edit In your second example, clearly 28/9/2013 17:59 is September 28. If 10/01/13 12:11:00 PM had the same formatting (perhaps came from the same file) then it is January 10. But if the formatting was different then it could be October 1. If you are seeing AMs and PMs with formatting as dd/mm/yy hh:mm;@ then some of your data is text and there is no reliable 'automatic' way to switch this to a date/time serial number without knowing the text convention (ie whether DMY or MDY), hence the need to revert to source.

Obviously 'day' values greater than 12 are actually months but that does not help much when for less than 13 it depends upon the formatting.

In addition, given your various sources, there is a risk that both the 1900 and the 1904 conventions might have been used and even possibly others also (your data logger might be on UNIX time, which starts in 1970).

+ 1 Technically you are right. Excel can not know. But if the user forcibly wants to convert to a specific format by splitting then that is possible :)

+1 @pnuts you have a point. That was my gut reaction, too, when I read the question. "Does the OP REALLY know which date is supposed to be DD-MM-YYYY versus MM-DD-YYYY if they only see the date and the first two items are both less than 13. It would not surprise me if the OP comes back to redact. But +1 to Sid, too for his approach.

@pnuts, thing is, once the data is inside Excel, Excel will have done its thing on interpreting it and has made up its mind about DMY or MDY. Much easier would be to import the data with the wizard and TELL Excel if the dates are DMY or MYD.

@SiddharthRout, how can I NOT luv ya after you are passing that quote back to me? And, of course, I totally agree that users don't necessarily enter data into Excel to promote easy processing. I so wish users would all learn how to operate the tools they are using every day and make their lives easier. Spend five minutes on learning a few basics every day and remove hours and days of time wasted on troubleshooting. - I think this conversation should move to Meta.

@pnuts, I asked this question years ago in another forum: -- most folks involved in the discussion are now MS MVPs, but were not at the time.

How to Solve Mixed Date formats in Excel with VBA - Stack Overflow

excel vba date excel-vba
Rectangle 27 0

I wouldn't want to say for certain that there is a VBA solution as it could simply be a localization issue with the software... but perhaps you can fix the issue by explicitly stripping the unwanted portion of the date/time from each cell before you format it. Something like

myWS.Range("A" & i).value = Format(myWS.Range("A" & i).value,"DD-MMM-YY")
myWS.Range("A" & i).numberformat = "DD-MMM-YY"
myWS.Range("B" & i).value = Format(myWS.Range("B" & i).value,"HH:MM")
myWSRange("B" & i).numberformat = "HH:MM"

This might not actually fix the formatting and may, in fact, cause the fields to report bad information when in the wrong format (for example '12/5/2013 00:00:00' in A1 and '12/30/1899 05:00:45' in B1), but I would give it a try and see what happens. You might want to ignore formatting this as a number all together and leaving it as a formatted string. This of course hinges upon the understand that no other code demands that the time is preserved in column A and the date is preserved in column B. If there is, then this is not a proper solution.

Thanks, but unfortunately I need the cell to retain the entire date and time since it is part of a schedule that relies on total time accuracy. I have to figure out how to change just the format of displayed data but not the stored value. I am going to investigate Styles.

Excel VBA: Dates displaying as text even though date format is set - S...

Rectangle 27 0

Store your date value in a string and pass it to the other procedure:

Dim dateValue as String

dateValue = Cells(Application.ActiveCell.Row, 14).Value

wdFind.Text = "DATE"

Call NoFormatPaste(dateValue)

In the other procedure, don't use the .Paste method, instead you can just insert the string.

Sub NoFormatDatePaste(dateValue as String)

wdFind.Replacement.Text = ""
wdFind.Forward = True
wdFind.Wrap = wdFindContinue
appWd.Selection.InsertAfter Format(dateValue, "dddd d mmmm yyyy")

End Sub

Thank you for this, however is there a function instead of InsertAfter so the new value overrides the selection, instead of inserting it after it?

Its ok I added appWd.Selection.Delete before the last line so it removes the found text before inserting the date.


Tried .Replace and it didnt like it, deleting the text and inserting the date is working just fine for now, thank you for your help.

Cool, if it's working for you then that's all that matters :) But I know there's another method that would do it directly without the deletion. (Word is not my specialty so I don't know it off the top of my head, though)

vba - Copy date from Excel paste to Word in different format - Stack O...

excel vba excel-vba ms-word word-vba
Rectangle 27 0

First, I suggest you check the regional settings for dates on your computer. If you set it to use the "dd/mm/yyyy" format it will be used by Excel as well and hopefully remove the need for any RegEx VBA-code.

Second, if you do need to use VBA to reformat dates, I strongly suggest you use the Date data type instead of Variants. I also advise you to use Option Explicit at the top of your code and explicitly declare any variables in order to minimize typos and produce better quality code.

1. Because it is never declared, tempdate is a Variant 2. You assign tempdate to be a boolean, from the result of RegExp.Test(pdate) 3. So when you check IsDate(tempdate) it will always be false, since a boolean can never be a Date.

Again, if you use the Date data type, you can skip the RegEx... :)

i have updated my code very slightly, and instead of using tempdate i'm now using pdate in the IsDate method as IsDate(pdate), is this correct now ?

Yes, that should do it. I still recommend Option Explicit and Date, though. ;)

vba - convert date in excel from dd-mm-yyyy to dd/mm/yyyy format and t...

excel vba excel-vba
Rectangle 27 0

I had the same problem when saving to .csr file to import into Access. The answer is to add Local:=True to the SaveAs statement. This keep the dates as UK format.

' save data to csv file
ChDir "C:\Documents and Settings\Doug\My Documents\Fitness\LogCard Data"
ActiveWorkbook.SaveAs Filename:= _
 "C:\Documents and Settings\Doug\My Documents\Fitness\LogCard Data\LogCard_access.csv" _
    , FileFormat:=xlCSV, CreateBackup:=False, Local:=True

excel - Keeping a date format when converting to text in VBA - Stack O...

excel vba date-format
Rectangle 27 0

Since the question has already been answered by the OP in the comments but not posted as an official answer I'll put it here in case someone else misses it like I did.

workbook = workbooks.Open(filename, Local:= true)

By setting Local = true uses the local machines date format rather than assuming mdy so in locales such as Australia (dmy) it'll change the behavior of Open()

Excel VBA date formats/values change when file is opened programatical...

excel vba date csv
Rectangle 27 0

First, I think the answer from Siddharth Rout is probably what you're looking for, but maybe this might help too?

The dates you're looking at are really zeros, right? You could test and handle those cells in a formula in a separate column (that is if you weren't looking to automate this solution).

For example, if your dates are in column A, and this is where you're seeing the "00/01/1900" as well, just as add a formula in column B. Something like:

=IF(A1=0, "N/A", A1)

You could then copy the results in column B and paste special their values onto column A -- effectively turning your 0's into N/A's.

vba - Excel date format issue (00/01/1990) - Stack Overflow

excel vba date excel-vba
Rectangle 27 0

I guess your default date format is "mm/dd/yyyy" and hence you are getting that problem.

Before you run the macro Right Click on Sheets("Sheet1").Cells(17, 3) and click on format. and then check what is the format of that cell.

If it is mm/dd/yyyy then change the value of the cell to 10/01/2011 and then try the macro again.

Excel VBA - the date format changes automatically - Stack Overflow

date excel-vba
Rectangle 27 0

Use this code (TRIED AND TESTED). I am assuming that the dates (as string) are stored in Col A from A1 onwards in say Sheet1

Sub Test()
    Dim temp As String
    Dim D As String, M As String, Y As String
    Dim ws As Worksheet
    Dim lRow As Long, i As Long

    '~~> Change this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet2")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 1 To lRow
            temp = CStr(.Range("A" & i).Value)

            Y = Left(temp, 4)
            M = Mid(temp, 5, 2)
            D = Right(temp, 2)

            .Range("A" & i).NumberFormat = "mm/dd/yyyy"

            '~~> This will give you 01/29/2014
            .Range("A" & i).Formula = Format(DateSerial(Val(Y), Val(M), Val(D)), "mm/dd/yyyy")
        Next i

    End With
End Sub

Downvote wasn't from me but this looks very close to what I'm trying to do. But I need to use it for hundreds of dates that all differ but are in that format yyyymmdd.

@novak: Use a loop :)

excel - Can I change my date format from 20140129 to 01/29/2014 using ...

excel vba date excel-vba date-format
Rectangle 27 0

Just in case this helps, i had an issue where excel was auto formating my info pulled from a webpage into DATE format, to fix, in the import window, click options and UNCHECK the auto date formating option, after that, my data imported perfect!

that's what my answer does. The user was asking within the context of vba though... so basically automating that popup box really quickly. Welcome to SO!

vba - Why do my Excel spreadsheets format my numbers as dates? - Stack...

excel vba excel-vba
Rectangle 27 0

The problem arises because 2A is interpreted as 2 AM:



Dealing with this depends on what types of values are in the column alongside the dates, you could check their length or check for the default date that's used when a DateTime contains no date part;

if format$(cdate("2A"), "YYYY-MM-DD") = "1899-12-30" then ..dodgy..

or look at its pattern; if isdate(x) and x like "####-##-##" or you could convert the value to a date and check that it falls within an appropriate range.

Selection.NumberFormat = "yyyy-mm-dd"

vba - excel cell date format - Stack Overflow

excel vba csv
Rectangle 27 0

A much simpler approach would be to use the text value in the cell e.g.

For r = 1 To Selection.Rows.Count
s = """"
c = 1
For c = 1 To Selection.Columns.Count
s = s & Selection.Cells(r, c).Text

        If c = Selection.Columns.Count Then
            s = s & """" 'end of row
            s = s & """,""" 'mid row
        End If
Next c
a.writeline s
Next r

Using .Text avoids all the issues you have encountered with formats

(I would also recommend reading this help on writing loops with Excel ranges. It means you can define rows, columns in relation to your table and not the absolute addresses or a selection)

vba - excel cell date format - Stack Overflow

excel vba csv
Rectangle 27 0

Range("U2:U" & newrow).NumberFormat = "mm/dd/yy"
Range("V2:V" & newrow).NumberFormat = "mm/dd/yy"

U and V are column and newrow is a variable

vba - excel cell date format - Stack Overflow

excel vba csv