Help with user function please
 

I want the following function to return either a string
(in the form DDMMMYYYY or HHMM), or just a blank if the
parameter INDATE hasn't been completed. The date/time
adding bit works OK, but if INDATE is blank the function
returns #VALUE!. It wouldn't matter except that the
#VALUE! response causes problems with a seperate importing
program. Is there any way I can fix it?

(INDATE and INTIME are strings in the form DDMMMYYYY and
HHMM respectively)

Many thanks,
Geoff.

Function TxtDateAdd(INDATE As Variant, INTIME As Variant,
HOURS As Double, DH As String) As Variant
TxtDateAdd = IIf(VarType(INDATE) = 8, UCase(Format
(CDate(Mid(INDATE, 1, 2) & "-" & Mid(INDATE, 3, 3) & "-" &
Mid(INDATE, 6, 4)) + CDate(Mid(INTIME, 1, 2) & ":" & Mid
(INTIME, 3, 2)) + (HOURS / 24), IIf(DH = "D", "ddmmmyyyy",
IIf(DH = "H", "hhmm", "mmhhyydd")))), "" )
End Function

Help with user function please
 

I re-wrote the function to make it clearer:-

Function TxtDateAdd(INDATE As String, _
INTIME As String, _
HOURS As Double, _
DH As String) As String
'Input:
'INDATE DDMMMYY
'INTIME HHMM
'HOURS - units
'DH D/H
Dim ThisDate As String, NewTime As Double

If INDATE < "" Then
ThisDate = Left(INDATE, 2) & "-" _
& Mid(INDATE, 3, 3) & "-" _
& Right(INDATE, 4) & " "
End If

ThisDate = ThisDate & Left(INTIME, 2) & ":" _
& Right(INTIME, 2)

NewTime = CDbl(TimeValue(ThisDate) _
+ DateValue(ThisDate)) _
+ HOURS / 24
If DH = "D" Then

TxtDateAdd = Format$(NewTime, "ddmmmyyyy hh:mm")

Else

TxtDateAdd = Format$(NewTime, "hh:mm")

End If
End Function

Patrick Molloy
Microsft Excel MVP


-----Original Message-----
I want the following function to return either a string
(in the form DDMMMYYYY or HHMM), or just a blank if the
parameter INDATE hasn't been completed. The date/time
adding bit works OK, but if INDATE is blank the function
returns #VALUE!. It wouldn't matter except that the
#VALUE! response causes problems with a seperate
importing
program. Is there any way I can fix it?

(INDATE and INTIME are strings in the form DDMMMYYYY and
HHMM respectively)

Many thanks,
Geoff.

Function TxtDateAdd(INDATE As Variant, INTIME As
Variant,
HOURS As Double, DH As String) As Variant
TxtDateAdd = IIf(VarType(INDATE) = 8, UCase(Format
(CDate(Mid(INDATE, 1, 2) & "-" & Mid(INDATE, 3, 3) & "-"
&
Mid(INDATE, 6, 4)) + CDate(Mid(INTIME, 1, 2) & ":" & Mid
(INTIME, 3, 2)) + (HOURS / 24), IIf(DH
= "D", "ddmmmyyyy",
IIf(DH = "H", "hhmm", "mmhhyydd")))), "" )
End Function

.

Help with user function please
 

Many thanks, this will improve my function writing a lot!

-----Original Message-----
I re-wrote the function to make it clearer:-

Function TxtDateAdd(INDATE As String, _
INTIME As String, _
HOURS As Double, _
DH As String) As String
'Input:
'INDATE DDMMMYY
'INTIME HHMM
'HOURS - units
'DH D/H
Dim ThisDate As String, NewTime As Double

If INDATE < "" Then
ThisDate = Left(INDATE, 2) & "-" _
& Mid(INDATE, 3, 3) & "-" _
& Right(INDATE, 4) & " "
End If

ThisDate = ThisDate & Left(INTIME, 2) & ":" _
& Right(INTIME, 2)

NewTime = CDbl(TimeValue(ThisDate) _
+ DateValue(ThisDate)) _
+ HOURS / 24
If DH = "D" Then

TxtDateAdd = Format$(NewTime, "ddmmmyyyy hh:mm")

Else

TxtDateAdd = Format$(NewTime, "hh:mm")

End If
End Function

Patrick Molloy
Microsft Excel MVP


-----Original Message-----
I want the following function to return either a string
(in the form DDMMMYYYY or HHMM), or just a blank if the
parameter INDATE hasn't been completed. The date/time
adding bit works OK, but if INDATE is blank the function
returns #VALUE!. It wouldn't matter except that the
#VALUE! response causes problems with a seperate
importing
program. Is there any way I can fix it?

(INDATE and INTIME are strings in the form DDMMMYYYY and
HHMM respectively)

Many thanks,
Geoff.

Function TxtDateAdd(INDATE As Variant, INTIME As
Variant,
HOURS As Double, DH As String) As Variant
TxtDateAdd = IIf(VarType(INDATE) = 8, UCase(Format
(CDate(Mid(INDATE, 1, 2) & "-" & Mid(INDATE, 3, 3) & "-"
&
Mid(INDATE, 6, 4)) + CDate(Mid(INTIME, 1, 2) & ":" & Mid
(INTIME, 3, 2)) + (HOURS / 24), IIf(DH
= "D", "ddmmmyyyy",
IIf(DH = "H", "hhmm", "mmhhyydd")))), "" )
End Function

.

.