Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
user defined function | Excel Worksheet Functions | |||
sumproduct function / VB user defined function | Excel Discussion (Misc queries) | |||
User Defined FUNCTION | Excel Discussion (Misc queries) | |||
User-defined function | Excel Worksheet Functions | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |