Excel autoformat date - please help

[plfas]

I'm having serious issues with Excel autoformatting my dates. I am located in Canada, and I have the locale settings for windows set to the dd/mm/yyyy format. Every day I download an excel file containing responses to a survey. I run a macro that involves merging these downloaded workbooks into a summary workbook, and generating a summary report. The summary should only be for the last week, but unfortunately the file I download can only be all responses.

The issue I'm having is with the timestamp. Each response is timestamped and the format is dd/mm/yyyy HH:MM:SS (hours, minutes, seconds).

I figured that to sort properly by date I should split this column so I use

.Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove                   

.Range("A:A").TextToColumns Destination:=.Range("A:A"), DataType:=xlDelimited, _
                        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
                        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
                        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

to split it into two columns with the date in column A and the timestamp in column B.

But when this happens, excel likes to mess with my brain. For instance, 31/08/2014 08:00:00 separates properly, as Excel does not recognize 31/08/2014 as a valid date. But September 1st formats to 2/9/2014, which Excel then recognizes as January 9th. September 2 becomes February 9th and so on.

I need to figure out a way to preserve either the date in the raw format (which at least then I can use some complicated IF formula or something) or format all dates properly in the DD/MM/YYYY format, or any consistent format.

I am so frustrated with this ANY help would be much appreciated.

Thanks

 

[RickXL]

Hi,

The way to minimize trouble with dates and times is to make use of the way that Excel represents dates and times. For example, dates are stored as numbers from 1 Jan 1900. So, 2 Jan 1900 is represented by Excel as 2 etc. Times are in fractions of days so 0.5 means 12:00:00 PM.

When I pasted one of your timestamps into Excel it made all the correct assumptions. If I format the cell as General it displays 41882.3333333333 when 31/08/2014 08:00:00 is entered. That is what I would expect and those values will sort by date properly without any further processing.

If you format the cells that contain the timestamps as General what do you see? A number with a decimal point, a timestamp or something else?

What I am saying is that if everything is working as it should then there will be no need to split the date and time parts.

 

[plfas]

Hi Rick,

Thanks very much for your reply. When I run the first macro which merges the workbooks, the first column is all set to general without me changing anything. The date and timestamp still appear in the format dd/mm/yyyy HH:MM (I don't know why I thought seconds were included when I made the initial post. Could it have something to do with the macro that merges the workbooks?

Here is my code:

Sub MergeEnglish()

Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
 
Set dirObj = mergeObj.Getfolder("C:/JJ/English")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
 
Range("A6:S" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
 


Range("A65536").End(xlUp).Offset(0, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

 

[RickXL]

I can't see from your code but somewhere the timestamp is being treated as text by Excel. You can either look to see why that is happening or you should be able to use the workaround below.

If I convert an Excel timestamp into a text format then I can no longer multiply it by 1. I get a #VALUE error. (That was how I proved to myself that I was not looking at a proper Excel "timestamp" value.)

However, I can convert that text timestamp into an Excel value by using DATEVALUE() and TIMEVALUE(). So if I have 01/10/2014 12:34 in cell A1, I can put this in cell B1:

=DATEVALUE(A1)+TIMEVALUE(A1)

Then B1 displays: 41913.5236111111

I would now use a custom format to display that as DD/MM/YYYY HH:MM. It may seem a long way round but you can now sort by date, extract dates and times, and perform calculations on it.

I hope this helps.

 

[plfas]

Rick, thank you so much for your ongoing help! That will work fine for me.

 

[plfas]

ACtually Rick, I could use a little more help. I need to delete rows that contain a date outside of a certain date range (that I would like specify using an input box) I found the following on another post, but I can't get it to work (all my rows get deleted)

Any ideas?

 Sub GoGoGadgetDelete()    Dim stStart As String, stEnd As String
    Dim dbStart As Double, dbEnd As Double
     
    Application.ScreenUpdating = 0
     
    stStart = InputBox("Please supply a start date", "Date Input", Date)
    stEnd = InputBox("Please supply an end date", "Date Input", Date)
     
    If Not IsDate(stStart) Or Not IsDate(stEnd) Then
        MsgBox "Invalid Dates", vbExclamation, "Input Error"
        GoTo ExitSub
    End If
     
    dbStart = CDbl(CDate(stStart))
    dbEnd = CDbl(CDate(stEnd))
     
    With Sheets(1).Columns(2)
        .AutoFilter Field:=1, Criteria1:="<" & dbStart, Operator:=xlOr, Criteria2:=">" & dbEnd
        .Resize(Rows.Count - 1).Offset(1).SpecialCells(12).EntireRow.Delete
        If .Parent.AutoFilterMode = True Then .AutoFilter
    End With
    
      With Sheets(2).Columns(2)
        .AutoFilter Field:=1, Criteria1:="<" & dbStart, Operator:=xlOr, Criteria2:=">" & dbEnd
        .Resize(Rows.Count - 1).Offset(1).SpecialCells(12).EntireRow.Delete
        If .Parent.AutoFilterMode = True Then .AutoFilter
    End With
     
ExitSub:
    Application.ScreenUpdating = 1
End Sub