Trouble with formatting text in a Function

[DeDBlanK]

Hello,
First off, thanks to those who help!
I am trying to write a UDF (for the first time) to combined these
formulas, at the bottom, that I have used in my worksheet to clean it
up and simplify it a bit. I am having troble in the code to get the
value to format to do what I want. If there is an easier way, please
let me know. Again, Thanks!

**************************************CODE
START***************************
Option Explicit
Public Function hmsDiff(sTime As Integer, fTime As Integer)

Dim BT As Long
Dim ET As Long

If sTime <> "" Then
If Len(sTime) > 5 Then
BT = TimeValue(Format(Left(sTime, 2) & Left(Right(sTime,
4), 2) & Right(sTime, 2), "0\:00\:00"))
Else
BT = TimeValue(Format(Left(sTime, 1) & Left(Right(sTime,
4), 2) & Right(sTime, 2), "00\:00\:00"))
End If
End If

If fTime <> "" Then
If Len(fTime) > 5 Then
ET = TimeValue(Format(Left(fTime, 2) & Left(Right(fTime,
4), 2) & Right(fTime, 2), "0\:00\:00"))
Else
ET = TimeValue(Format(Left(fTime, 1) & Left(Right(fTime,
4), 2) & Right(fTime, 2), "00\:00\:00"))
End If
End If

If IsNull(sTime) Then
If IsNull(fTime) Then
hmsDiff = ""
Else
If sTime > fTime Then
hmsDiff = 1440 * (ET Mod 1) - 1400 * (BT Mod 1) + 720
Else
hmsDiff = 1440 * (ET Mod 1) - 1400 * (BT Mod 1)
End If
End If
End If

End Function
**************************************CODE
END***************************

WORKSHEET FORMULAS
CELL I6
63000
CELL J6
74500
CELL CJ5
=IF(I6="","",IF(LEN(I6)>5,TIMEVALUE(TEXT(LEFT(I6,2)&LEFT(RIGHT(I6,4),2)
&RIGHT(I6,2),"0\:00\:00")),TIMEVALUE(TEXT(LEFT(I6,1)&LEFT(RIGHT(I6,4),
2)&RIGHT(I6,2),"00\:00\:00"))))
CELL CK5
=IF(J6="","",IF(LEN(J6)>5,TIMEVALUE(TEXT(LEFT(J6,2)&LEFT(RIGHT(J6,4),2)
&RIGHT(J6,2),"0\:00\:00")),TIMEVALUE(TEXT(LEFT(J6,1)&LEFT(RIGHT(J6,4),
2)&RIGHT(J6,2),"00\:00\:00"))))
CELL CQ5
=IF(I6="",0,IF(I6>J6,1440*MOD(CK5,1)-1440*MOD(CJ5,1)+720,1440*MOD
(CK5,1)-1440*MOD(CJ5,1)))

 

[Patrick Molloy]

use STRING or DATE but NOT Long for the declaration of BT and ET
and you probably need STRING and not Integer as the parameters

so

Option Explicit
Public Function hmsDiff(sTime As String, fTime As String)

Dim BT As string
Dim ET As string

 

[DeDBlanK]

use STRING or DATE but NOT Long for the declaration of BT and ET
and you probably need STRING and not Integer as the parameters

so

 Option Explicit
 Public Function hmsDiff(sTime As String, fTime As String)

    Dim BT As string
    Dim ET As string













- Show quoted text -

That didn't work. I even tried 'as Date'

 

[Patrick Molloy]

what do the values in CELL I6 (63000) and CELL J6 (74500 ) represent? what
are you looking to achieve?

 

[DeDBlanK]

what do the values in  CELL I6 (63000) and CELL J6 (74500 ) represent? what
are you looking to achieve?







- Show quoted text -

The formuals at the bottom of the first post work if you place them in
a worksheet. That is what I am trying to accomplish.
Just a note though, to see the formatting, Cells I6 and J6 should have
a custom format of #":"##":"##
Cell that the function or result should be [h]:mm:ss
The basics of it is, time start (as number) - time finish (as number)
= difference
however there is a lot of things that don't make sense because of the
way the want the times entered (as numbers, but really they are
times) just making it easy on the inputter.
thanks for the help patrick!

 

[DeDBlanK]

what do the values in  CELL I6 (63000) and CELL J6 (74500 ) represent? what
are you looking to achieve?

- Show quoted text -

The formuals at the bottom of the first post work if you place them in
a worksheet.  That is what I am trying to accomplish.
Just a note though, to see the formatting, Cells I6 and J6 should have
a custom format of  #":"##":"##
Cell that the function or result should be [h]:mm:ss
The basics of it is, time start (as number) - time finish (as number)
= difference
however there is a lot of things that don't make sense because of the
way the want the times entered (as numbers, but really they are
times)  just making it easy on the inputter.
thanks for the help patrick!- Hide quoted text -

- Show quoted text -

Patrick,
This is kinda what I was talking about in
http://groups.google.com/group/micr...d724d11eb33/32532ff36f22effc#32532ff36f22effc
however, this it's a different approach.
Sorry, I guess I didn't exactly answer your last post.
what do the values in CELL I6 (63000) and CELL J6 (74500 ) represent?
they are time values as numbers with formatting applied to the cells
to make them represent times
63000 is 6:30:00
74500 is 7:45:00
again, some of the formulas are in place adjusting the numbers for the
12hr (720minutes) variance, for example
if 120000 - 10000 is 00:01:00 and not 11:00:00
hope that helps, cause I know this can be confusing

 

[DeDBlanK]

what do the values in  CELL I6 (63000) and CELL J6 (74500 ) represent? what
are you looking to achieve?







- Show quoted text -

I think I got it! Here is what I ended up with:


Option Explicit
Public Function hmsDiff(sTime As String, fTime As String)

Dim BT As Date
Dim ET As Date

If sTime <> "" Then
If Len(sTime) > 5 Then
BT = TimeValue(Format(Left(sTime, 2) & Left(Right(sTime,
4), 2) & Right(sTime, 2), "0\:00\:00"))
Else
BT = TimeValue(Format(Left(sTime, 1) & Left(Right(sTime,
4), 2) & Right(sTime, 2), "00\:00\:00"))
End If
End If

If fTime <> "" Then
If Len(fTime) > 5 Then
ET = TimeValue(Format(Left(fTime, 2) & Left(Right(fTime,
4), 2) & Right(fTime, 2), "0\:00\:00"))
Else
ET = TimeValue(Format(Left(fTime, 1) & Left(Right(fTime,
4), 2) & Right(fTime, 2), "00\:00\:00"))
End If
End If

'hmsDiff = (ET * 24 * 60) - (BT * 24 * 60) + 720

If sTime <> "" Then
If fTime <> "" Then
If sTime > fTime Then
hmsDiff = (ET * 24 * 60) - (BT * 24 * 60) + 720
Else
hmsDiff = (ET * 24 * 60) - (BT * 24 * 60)
End If
Else
hmsDiff = ""
End If
End If

End Function

So far everything that I tested works.

 

[Patrick Molloy]

well done! it helps to talk

I think I got it! Here is what I ended up with:


Option Explicit
Public Function hmsDiff(sTime As String, fTime As String)

Dim BT As Date
Dim ET As Date

If sTime <> "" Then
If Len(sTime) > 5 Then
BT = TimeValue(Format(Left(sTime, 2) & Left(Right(sTime,
4), 2) & Right(sTime, 2), "0\:00\:00"))
Else
BT = TimeValue(Format(Left(sTime, 1) & Left(Right(sTime,
4), 2) & Right(sTime, 2), "00\:00\:00"))
End If
End If

If fTime <> "" Then
If Len(fTime) > 5 Then
ET = TimeValue(Format(Left(fTime, 2) & Left(Right(fTime,
4), 2) & Right(fTime, 2), "0\:00\:00"))
Else
ET = TimeValue(Format(Left(fTime, 1) & Left(Right(fTime,
4), 2) & Right(fTime, 2), "00\:00\:00"))
End If
End If

'hmsDiff = (ET * 24 * 60) - (BT * 24 * 60) + 720

If sTime <> "" Then
If fTime <> "" Then
If sTime > fTime Then
hmsDiff = (ET * 24 * 60) - (BT * 24 * 60) + 720
Else
hmsDiff = (ET * 24 * 60) - (BT * 24 * 60)
End If
Else
hmsDiff = ""
End If
End If

End Function

So far everything that I tested works.

 

[DeDBlanK]

well done!  it helps to talk















- Show quoted text -

Again thanks for your help Patrick. It does help to talk. Especially
since I am the only one @ my employer that does what I do.
I did make one more change to correct the 12hr variance.

Option Explicit
Public Function hmsDiff(sTime As String, fTime As String)

Dim BT As Date
Dim ET As Date

If sTime <> "" Then
If Len(sTime) > 5 Then
BT = TimeValue(Format(Left(sTime, 2) & Left(Right(sTime,
4), 2) & Right(sTime, 2), "0\:00\:00"))
Else
BT = TimeValue(Format(Left(sTime, 1) & Left(Right(sTime,
4), 2) & Right(sTime, 2), "00\:00\:00"))
End If
End If

If fTime <> "" Then
If Len(fTime) > 5 Then
ET = TimeValue(Format(Left(fTime, 2) & Left(Right(fTime,
4), 2) & Right(fTime, 2), "0\:00\:00"))
Else
ET = TimeValue(Format(Left(fTime, 1) & Left(Right(fTime,
4), 2) & Right(fTime, 2), "00\:00\:00"))
End If
End If

'hmsDiff = (ET * 24 * 60) - (BT * 24 * 60) + 720

If sTime <> "" Then
If fTime <> "" Then
If (BT * 24 * 60) > (ET * 24 * 60) Then '<~~Change here
hmsDiff = (ET * 24 * 60) - (BT * 24 * 60) + 720
Else
hmsDiff = (ET * 24 * 60) - (BT * 24 * 60)
End If
Else
hmsDiff = ""
End If
End If

End Function