Converting hours/minutes string to minutes

[Andy]

Hi,

I have a field which displays total hours and minutes as
a text string (for other calculation reasons) e.g 37:20.

What I am trying to achieve, is convert the string into
total minutes. BUT it needs to be able to cope with
negative times. I currently have some code that converts
this and works fine until it tries to convert -37:20.

I also have some code for dealing with negative minutes
when converting back into the text string e.g -260 back
into "-04:20" but cannot get it to work the other way
round.

Any help would be greatly appreciated.

TIA

Andy

 

[Rick B]

Not sure of the exact syntax, but you need to pull all of the string to the
left of the colon. Turn that into a value. find the absolute value of it
and multiply it by 60. Add that to the value of the string to the right of
the colon. If the first character of the string is a minus sign, multiply
the result by -1.

I would build a custom function to accomplish this and then call that
function anywhere I needed to do the conversion. Use the help filed to
figure out the proper syntax of the InString, Mid, Left, Right, Abs, and Val
functions. These should be all that you need to perform the translation.

Rick B




Hi,

I have a field which displays total hours and minutes as
a text string (for other calculation reasons) e.g 37:20.

What I am trying to achieve, is convert the string into
total minutes. BUT it needs to be able to cope with
negative times. I currently have some code that converts
this and works fine until it tries to convert -37:20.

I also have some code for dealing with negative minutes
when converting back into the text string e.g -260 back
into "-04:20" but cannot get it to work the other way
round.

Any help would be greatly appreciated.

TIA

Andy

 

[Andy]

Thanks Rick,

I have been playing around with these for weeks, and
couldn't get it right so I went back and split
everything, and it now works. I guess I was almost
there, but my syntax wasn't quite right when I was trying
to perform the calculation in 1 statement...

Anyway, like i say, it is probably longer winded than
needed but if it helps anyone else, this is the code I
used...

****************************************************
toleft = Len(startflexi) - InStr(1, startflexi, ":")
toleft = toleft + 1

sfhours = Left(startflexi, toleft)
sfhours = sfhours * 60

sfminutes = Right(startflexi, 2)

If Left(startflexi, 1) = "-" Then

sfminutes = sfminutes * -1

Else

End If

sfconverted = sfhours + sfminutes
***************************************************

If anyone can improve on this I would be greatful.

Thanks again Rick for your help.

Andy

 

[Ron Weiner]

Andy

I am not exactly shure what it is that you are tying to do but I don't think
your code works. Here is a function that I think does what you want.

Public Function ToMinutes(strHM As String) As Long
' Purpose Convert a string formatted as H:M into minutes
ToMinutes = 60 * Abs(Left(strHM, InStr(1, strHM, ":") - 1)) _
+ Mid(strHM, InStr(1, strHM, ":") + 1)
If Left(strHM, 1) = "-" Then
ToMinutes = ToMinutes * -1
End If
End Function

You can put this into a bas module and call it from anywhere in your app. EX
MyTextBox.Value = ToMinutes("-12:22"). You should add an Error handler to
prevent failure if the caller passes a Null or improperly formatted string.
As it currently stands if there is no colon in the string passed, or if
either part of the string is not numeric, the function fails.

Ron W

 

[Andy]

Thanks Ron,

I tested the code briefly with both negative and positive
times, and it did appear to work.

I realise that I haven't covered errors and invalid
submissions in the code, but just wanted to get the
conversion part working at the moment.

Have checked when stepping through the code, and the
correct portions of the string are being passed and the
final figure shows correctly in the form after entering
all the times. Which part of the code was wrong?

I guess I should have also pointed out that there is more
code to what i submitted, as the whole process then adds
that result to another variant that was already passed as
a total minutes, but just included the section to convert
this bit.

Thanks for the code, i knew there should be a more
refined way of getting positive/negative string time to
total minutes, but just couldn't get my brain to think
logically...lol I will use your function instead

Thanks again

Andy

 

[Ron Weiner]

Andy

Re:

Have checked when stepping through the code, and the
correct portions of the string are being passed and the
final figure shows correctly in the form after entering
all the times. Which part of the code was wrong?

if startflexi = 20:20 then the line

sfhours = sfhours * 60

will fail as sfhours will contain "20:" which is not a number and therefore
can NOT be multiped by 60

Consider changing

toleft = Len(startflexi) - InStr(1, startflexi, ":")
toleft = toleft + 1
sfhours = Left(startflexi, toleft)

to

sfhours = Abs(Left(startflexi, InStr(1, startflexi, ":") - 1))

which should fix the problem.

Ron W

 

[Andy]

Hi Ron,

Thanks for that.

I had actually spotted the main problem later on when i
got home, where it failed also was on the data mismatch,
when somebody's starting flexi was positive. I had
originally tested a negative starting flexi (but only
tested the end result being positive or negative) hence
it appearing to work prior.

I have now used the code you supplied and tweaked it with
my details, and now it DOES work

I appreciate your help and explanation.

Cheers

Andy