Any suggestions for more efficient time conversion code? 1

[PStrongs]

Hi,

I have written this procedure that converts decimal time (1.75) into analogue time (1hr 45 mins). It works ok, but seems a little verbose. Any suggestions for making it more efficient and shorter?

Regards,

Sub testString()

Dim Duration As String
Dim hrs As String, mins As String, totTime as String

'set Duration to decimal time
Duration = "1.75"

If Len(Duration) = 3 Then
	totTime = Duration * 60 & “mins”
		
	ElseIf Len(Duration) = 4 then
	Hrs=Left(Duration, Len(Duration) – 3)
	Mins = Right(Duration, Len(Duration) – 1) * 60

		If hrs = “1” Then
			totTime = hrs & “hr “ & mins & “mins”
		else
			totTime = hrs & “hrs “ & mins & “mins”
		end if

	ElseIf Len(Duration) = 5 Then
	Hrs = Left(Duration, Len(Duration) – 3)
	Mins = Right(Duration, Len(Duration) – 2) * 60
	totTime = hrs & “hrs “ & mins & “mins”

Else
'could be hundreds of hours
Hrs = Left(Duration, Len(Duration) – 3)
Mins = Right(Duration, Len(Duration) – 3) * 60
totTime = hrs & “hrs “ & mins & “mins”

End If

MsgBox totTime

End Sub

 

[strongm]

Something like

[blue]Public Function AnalogTime(Duration As Double) As String
    Dim days As Long
    Dim Result As Variant
    days = Duration \ 24
    Result = Split(Format(CDate(Duration / 24), "h:nn:ss"), ":")
    AnalogTime = (days * 24 + Result(0)) & "hrs " & Result(1) & "mins " & Result(2) & "secs"
End Function[/blue]

 

[macropod]

Hi strongm,

I'm sure you meant "h:mm:ss" rather than "h:nn:ss"

[MS MVP - Word]

 

[firefytr]

And why can you not just divide by 24?? Take your whole decimal, assuming it is in A2...

=A2/24

Format as time, h:mm:ss, or however you want to view the data.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[strongm]

>I'm sure you meant "h:mm:ss" rather than "h:nn:ss"

I didn't, actually. I meant exactly what I put.

>And why can you not just divide by 24

Indeed; that's just what we did in my solution, with a tiny bit of extra work to deal with the situations where you might be dealing with more than 24 hours (as the OP says 'could be hundreds of hours').

>... assuming it is in A2

Also, there is no hint that this is for Excel (a cursory look at pstrongs other posts strongly suggests that Word is the main app in which they are using VB), which suggests that using sheet functions isn't an option

 

[PHV]

macropod, why would you put month in time ? ;-)

 

[macropod]

Brain fade - I was thinking of 'mm' as in minutes.

[MS MVP - Word]

 

[firefytr]

strongm, point taken about the app. Indeed it was an assumption on my part this was Excel. As far as your format, did you have reasoning behind "h:nn:ss" as opposed to "h:mm:ss"? And for what application were you referring to? I know it is not Excel as that is not a valid format in such an app. As far as I can tell it is not a valid format whatsoever.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[strongm]

>I know it is not Excel as that is not a valid format in such an app.

Yes, it is, I'm afraid. For VBA, which is what we are talking about. It's been the correct, documented format since VBA was introduced. The sheet cell formatting functions in Excel differ, having little to do with VBA, and nn is not valid there.

> As far as I can tell it is not a valid format whatsoever

See my previous comment above. m and mm are allowed as an alternative to n and nn in VB and VBA, but there is always (as PHV hinted) the possibility of getting the month instead of the minute ... for example let's pretend we have decided to use the Format function to pull the minutes out of a Date/time

? format("1 Aug 2008 10:15", "mm")

will give you 08 rather than 15, whereas

? format("1 Aug 2008 10:15", "nn")

will give the correct result

 

[firefytr]

Very interesting. Thanks for the enlightenment!

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[xwb]

In your example, you should have picked something like format("1 Aug 2009 10:15", "mm"), something where all the fields end up with different results. My brain wasn't in gear when I first read it. Wondering how mm returned the last 2 digits of the year. Then the penny dropped: August is the 8th month.

 

[cresbydotcom]

Am I missing something? - but I always thought MM for minutes and mm for months and I do remember finding-out the hard way. Never though to try nn.

HH:MM and dd/mm/yy (UK version) work for me.

there is a tide in the affairs of man that you Canute ignore.................

 

[strongm]

>Am I missing something?
Yep

>MM for minutes and mm for months
Nope. VB and VBA* make no distinction between mm and MM. They both officially mean month but, as I have already said, are allowed as an alias for minutes when there is little or no ambiguity. Which means that

? Format("1 Sep 2008 10:15", "MM")

will give you the month, not the minutes as you have unfortunately misled yourself into believing it ought...


* VB.NET changes this, and MM and mm do indeed mean what you say

 

[AnotherHiggins]

to strongm for successfully running the gauntlet.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SBerthold]

>to strongm for successfully running the gauntlet.

yes, good job.
(just need to take a look-look at that code of his though ;-))

 

[SBerthold]

Ok. Took another look and with input hours like 36 or 470 I am not getting correct results on my system.

 

[strongm]

Both of those work fine here on Word 2007 and Word 2003

I get

36hrs 00mins 00secs
476hrs 00mins 00secs

What do you get?

 

[SBerthold]

Sorry. Wrong examples copied.

Try 47.5. I get 71hrs 30mins

and for 479.5 I get 503hrs 30mins


(Word 2002)

 

[strongm]

Yep, it's that irritating financial rounding catching me out.

Change

days = Duration \ 24

to

days = Int(Duration / 24)

 

[SBerthold]

I tried something like that yesterday and thought it wasn't always working, but I guess I was too fast.

Yesterday I came up with this one-liner and it seeeems to also work:

x = Int(Hours) & "hrs " & Format$(CDate(Hours / 24), "nn\m\i\n\s ss\s\e\c\s")