average date / new function
 

Hello
A customer gives me the following data :
2003/01-12
2002/01-03
2002/10-12
....
....
....
I would like to insert a new column so the above data would be calculated as
followed :

2003/01-12 would become 01/07/2003
2002/01-03 would become 15/02/2002
2002/10-12 would become 15/11/2002

......
.....
.....
Can anybody give me a solution (macro, inserting a new function, etc..)?
Thank you.

average date / new function
 

How does 2002/10-12 become 15/11/2002 ??


--
Gary''s Student


"dirk" wrote:

Hello
A customer gives me the following data :
2003/01-12
2002/01-03
2002/10-12
....
....
....
I would like to insert a new column so the above data would be calculated as
followed :

2003/01-12 would become 01/07/2003
2002/01-03 would become 15/02/2002
2002/10-12 would become 15/11/2002

......
.....
.....
Can anybody give me a solution (macro, inserting a new function, etc..)?
Thank you.

average date / new function
 

Public Function DateFromRange(DateRange As String) As Date
' This returns a date variable; if you need text you can convert it and
format it
' NOTE: I do no error checking of the validity of the input string!

Dim TempStr As String, Month1 As Integer, Month2 As Integer
Dim ResultMonth As Integer, ResultDay As Integer

' Parse out the months
TempStr = Right(DateRange, Len(DateRange) - InStrRev(DateRange, "/"))
Month1 = Val(Left(TempStr, InStr(TempStr, "-")))
Month2 = Val(Right(TempStr, Len(TempStr) - InStrRev(TempStr, "-")))

' Determine if an even or odd number of months
' and figure the "midrange" value correspondingly
ResultMonth = Int((Month2 - Month1) / 2)
If (Month2 - Month1) / 2 = Int((Month2 - Month1) / 2) Then
ResultDay = 15
Else
ResultDay = 1
ResultMonth = ResultMonth + 1
End If
ResultMonth = Month1 + ResultMonth

DateFromRange = DateSerial(Left(DateRange, 4), ResultMonth, ResultDay)

End Function

--
- K Dales


"dirk" wrote:

Hello
A customer gives me the following data :
2003/01-12
2002/01-03
2002/10-12
....
....
....
I would like to insert a new column so the above data would be calculated as
followed :

2003/01-12 would become 01/07/2003
2002/01-03 would become 15/02/2002
2002/10-12 would become 15/11/2002

......
.....
.....
Can anybody give me a solution (macro, inserting a new function, etc..)?
Thank you.