Date Values changing

Question

I'm trying to create a database based on lots of spreadsheets. I aim to go through a lot of spreadsheets pulling out the information needed into a nicely formatted table. I have just started so I'm writing some functions to pull out what I need. I'll store them in variables and then put them into a table.

The function I have written searches a range for a string and pulls out the information in the cell next to it. It seems to work fine for the strings.

Can anyone explain what is happening with the dates? I want it to output dates in UK format dd/mm/yyyy. Currently the date next to expiry 01/06/2018 is in UK format. If I debug it and put in a print it comes out in UK format. If I check the format of the cell it's stored in and the cell it is being returned to both are UK format. Yet it comes out as 06/01/2018.

If possible could an explanation of what is happening be included in the answer rather than a "Just change the code to this" solution so I can learn how it is actually interpreting it?

Any help would be appreciated.

Here's the code

Private Sub Main()

'Turn off screen updating
Application.ScreenUpdating = False

'Define the variables
Dim Coverholder As String
Dim SearchRange As Range

'set the range to search
Set SearchRange = Sheets("2017 Property").Range("A1:AZ100")

'testing
Range("A1").Value = GetNextTo(SearchRange, "*Expiry*")

Debug.Print GetNextTo(SearchRange, "*Expiry*")

'Reset the position of the selection
Range("A1").Select

'Turn on the screen updating
Application.ScreenUpdating = True

End Sub

'Function to return the value one cell to the right of a search value
Private Function GetNextTo(SearchRange As Range, FindString As String) As String

    'Define variables
    Dim Rng As Range

    'Clean the string
    If Trim(FindString) <> "" Then
        'use the search range
        With SearchRange
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            'If value found
            If Not Rng Is Nothing Then

                Application.Goto Rng.Offset(0, 1)

            'If value not found
            Else

                MsgBox "Nothing found"

            End If
        End With
    End If

    GetNextTo = ActiveCell.Value

End Function

I really would like to explain it to you, but I have searched so long and also asked on stackoverlow but never found a good explanation. If you just want it right: Format(Date, "dd/mm/yyyy") — Doomenik, Jul 04 '17 at 11:33
Offtopic: `Application.ScreenUpdating = True` does not turn off the screen updating — , Jul 04 '17 at 11:34
https://stackoverflow.com/questions/37100821/change-date-format-using-substitute-or-replace/37101358#37101358 here and here https://stackoverflow.com/questions/38000194/difference-between-date-and-time-w-out-work-week-excel/38001028#38001028 Are some stackoverflow explanations of it — Doomenik, Jul 04 '17 at 11:36

score 0 · Accepted Answer · answered Jul 04 '17 at 11:44

This is what happens (as far as I know):

Dates in excel are displayed as (e.g.) dd.mm.jjjj, but not stored that way. You can test this by printing a date within a cell like this:

Create a new wb, enter 12.11.1985 into the first cell, then run:

Sub test()
 'returns value of cell properly formated (but not always as displayed in the cell)
 Debug.Print Cells(1, 1).Value
 'returns the actual value of the cell
 Debug.Print Cells(1, 1).Value2
End Sub

.Value2 will return 31363, which is the actual value within the cell. This value will be interpreted in different ways, depending on various settings and the functions you extract it with.

As for a solution, see @Doomeniks comment.

Edit: Doomenik linked some excellent answers, far more in-depth than this one.

`.Value2` will give you the "naked" value within the cell without any formatting, and is much more predictable than `.Value` and `.Text` — , Jul 04 '17 at 11:50

Date Values changing

1 Answers1