Date Formats

[Jameo]

Hi all,

I have dates in the following form:

29/05/2011 07:00:00

I also have a macro to automatically fill a sheet with dates from a selected start date and end date:

Public Sub Dates2()
Dim StDate As Date
Dim EnDate As Date
Dim IntRow As Integer
Dim CuDate As Date
Dim LRow As Long
 
LRow = Sheets("BoundGraph").Range("A" & Rows.Count).End(xlUp).Row
StDate = Sheets("ControlPage").Cells(25, 10).Value
EnDate = Sheets("ControlPage").Cells(26, 10).Value
CuDate = StDate - 1
IntRow = 2
Sheets("BoundGraph").Select
Cells.Clear
Do
CuDate = CuDate + 1
IntRow = IntRow + 1
Sheets("BoundGraph").Range("A" & IntRow).Value = CuDate
Loop Until CuDate = EnDate
 
End Sub

I then have a vlookup that looks up the relevent data in the "BoundGraph" sheet.

However I keep getting an NA error as the date formats are different.

I have tried changing the date format in the sheet that is being looked up, but even though it doesn't display the 07:00 bit, it is still in the cell value.

Does anyone have any solutions to this issue?

Thanks in advance

 

[Richard Schollar]

What VLOOKUP form are you using? Presumably you should be using the one with 4th argument set to TRUE (or omitted altogether) so that an exact match isn't required (your dates in the lookup sheet are all ascending so you can use this form).

 

[Jameo]

I thought this myself Richard, and initially I had the forth argument set to a specific range, however, I have since changed that to TRUE (and left it blank), but it is still not functioning. There is no issue with the actual formula as upon changing the date and removing the 07:00 it functions correctly.

Unfortunately the data is imported in the dd/mm/yyyy hh:mm format.

An example of my formula is:
=VLOOKUP(A4,CompiledAgreed!A4:AA35,2,TRUE)

 

[Jameo]

Does anyone have any further input on this matter?

Thanks

 

[Jameo]

I've made a really ugly fix to this issue by adding a time value to my date populating macro:

Public Sub Dates2()
Dim StDate As Date
Dim EnDate As Date
Dim IntRow As Integer
Dim CuDate As Date
Dim LRow As Long
Dim TValue as Date
 
LRow = Sheets("BoundGraph").Range("A" & Rows.Count).End(xlUp).Row
StDate = Sheets("ControlPage").Cells(25, 10).Value
EnDate = Sheets("ControlPage").Cells(26, 10).Value
CuDate = StDate - 1
IntRow = 2
Sheets("BoundGraph").Select
Cells.Clear
 
TValue = TimeValue("07:00:00")
 
CuDate = CuDate + TValue
EnDate = EnDate + TValue

Do
CuDate = CuDate + 1
IntRow = IntRow + 1
Sheets("BoundGraph").Range("A" & IntRow).Value = CuDate
Loop Until CuDate = EnDate
 
End Sub

But if anyone has a more elegent method of fixing this issue, please let me know.

Thanks

 

[TiaXL]

Does anyone have any further input on this matter?

Thanks

try using the =left() function to extract the date part of your data into a seperate column and perform the vlookup on this data? you can probably build this into the vlookup although I'm not sure of the syntax

 

[Richard Schollar]

Do you get #N/A returns with all dates or just some of them? You should only get a #N/A if the date you are looking up is earlier than the earliest date in the lookup range, otherwise there will alswys be a date within the lookup range less than than the lookup value so some value should be returned.