Number of Days Query Help

[darreno]

I imported an large Excel file with a date column consisting of 3 different date formats and need help on a query to extract the number of days. Examples of the date on that column:

7/6/2006
7/1/06-7/31/06
7/1-7/5

Appreciate any advise.

 

[Mile-O]

First things first. Are you in the UK or US? That will help determine if 7/6/2006 is the 7th June, 2006 or the 6th July, 2006.

Next thing: 7/1-7/5 - what the hell is that?

 

[Brianwarnock]

The following functions are for the UK date format, but the change to US is simple enough, and they are for illustration as to how I would tackle this.There are no error checks or checks for a single date as you displayed, nor are all of the possible Cases covered, but it gives an approach.

Brian

Public Function numdays(fldin As String) As Integer
Dim lendate As Integer
Dim date1 As Date
Dim date2 As Date
Dim data1 As String
Dim lendata1 As Integer

myarray = Split(fldin, "-")
data1 = myarray(0)
lendata1 = Len(data1)
date1 = createdate(data1, lendata1)

data1 = myarray(1)
lendata1 = Len(data1)
date2 = createdate(data1, lendata1)

numdays = DateDiff("d", date1, date2)
 

End Function

Public Function createdate(data1 As String, lendata1 As Integer) As Date
Select Case lendata1
Case 10
createdate = DateSerial(Right(data1, 4), Mid(data1, 4, 2), Left(data1, 2))
Case 3
createdate = DateSerial(Year(Date), Right(data1, 1), Left(data1, 1))

End Select
End Function

 

[darreno]

Mile-O:
I'm in the US. 7/1-7/5 is July 1 to July 5.

Brian:
Thanks for the code. I will try that out first thing tomorrow.

Thank you guys!

 

[Brianwarnock]

Darren I realised later that my method was not only heavy handed but had a fatal flaw it would not diffentiate, which it must, between d/mm and dd/m or in US terms mm/d and m/dd, and thus would give an error. The code below works to convert to date and should handle US dates without change.
You need to add the conversion of Array(1) and do the datediff and you no doubt realise that there are still outstanding issues as mentioned earlier.

Brian

Edit I was wondering if it might be better to have two date extractions one for each date into separate fields and then do the Diff into a third as it might make it easier to track "dirty" data which it strikes me you may well have.

Public Function fgetdate(fldin As String) As Date

Dim date1 As Date
Dim data1 As String

myarray = Split(fldin, "-")
data1 = myarray(0)

fgetdate = datevalue(data1)    ' whilst testing I just output a converted date the code below is also required to complete the number of days

data1 = myarray(1)
date2=datevalue(data1)
Datediff etc

End Function

 

[raskew]

Brian - Mile-O

Given "7/31/06", there's no confusion. It's obviously US short-date format.

Bob

 

[Brianwarnock]

Brian - Mile-O

Given "7/31/06", there's no confusion. It's obviously US short-date format.

Bob

I was never confused, but I cannot test US so wrote it in UK and told Darren he would need to convert the first attempt, but not the second.

Brian

 

[Brianwarnock]

Sometimes you know you haven't done the best of jobs especially when using functions that are new to you so if you really trust your data and just want the number of days this works

Public Function fgetnumdays(fldin As String) As Integer
myarray = Split(fldin, "-")
fgetnumdays = DateDiff("d", DateValue(myarray(0)), DateValue(myarray(1)))
End Function

tested on (uk date format)

19/07/2006-21/07/2006
1/7/08-12/07/08
30/6-5/7

Brian