Time Formatting

[jpe2007]

Good morning all, I hope there is an easy answer to this one. I want to enter usage time for a call center rep into fields within my table.

I have formatted the fields for time, however since these are call center stats I need to be able to enter on the form HH:MM:SS as the format and don't know how to get there.

So If the time logged is 1 hour, 23 minutes and 2 seconds I want to be able to enter the following string into my field:

012302 and have it show as 01:23:02...

How do I get this to happen, any and all help would be appreciated thanks.

Please keep in mind I will then be using this time entry in calculations and converting it to total seconds in another field 'lets say secs' that will convert the time to seconds:

For example from above: (((1hour*60(for minutes))*60 for seconds)+(23*60sec)+02sec) which would ultimately get me to a total of 4,982 total seconds. This is the number I need for calculations.

Is this easy or difficult???

 

[missinglinq]

Maybe this will be of some use to you. Just place it in a standard module. If you place it in a new module, just remember to name the module anything but ElapsedTime.

Public Function ElapsedTime(Start As Date, Finish As Date) As String
'Calculates elapsed time between 2 date/times and
'parses it out into Hours-Minutes-Seconds in HH:MM:SS format
Dim HoursLapsed, SecondsLeft, MinutesLapsed, SecondsLapsed As Long

TotalSeconds = DateDiff("s", Start, Finish)

HoursLapsed = Int(TotalSeconds / 3600)

SecondsLeft = TotalSeconds Mod 3600

MinutesLapsed = Int(SecondsLeft / 60)

SecondsLapsed = SecondsLeft Mod 60

ElapsedTime = Format(HoursLapsed, "00") & ":" & Format(MinutesLapsed, "00") & ":" & Format(SecondsLapsed, "00")

End Function

In your form call the function like this:

Me.Duration = ElapsedTimeRounded(Me.StartTime, Me.FinishTime)

Note, that as far as the total seconds thing goes, this will do the job:

TotalSeconds = DateDiff("s", Start, Finish)

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[MajP]

I would work with times not integers as Missingling suggests. If you want to enter the value as you suggest then you can also just set the properties of the field to:

format: hh:nn:ss
inputmask: 00:00:00;0;_

and then time in seconds is as suggested:
=DateDiff("s",0,[dtmTime])

where dtmTime is the time field.