Times from Strings (1 Viewer)

kirkm · Jan 15, 2020

I have two strings which are times in "mm:ss" format.
I want to put these into a tables Date/Time field, also formatted "mm:ss"

But I'm getting a result of 12:00 instead of the mm and ss.

Should I change the table to a text field, solving the problem, or convert the strings into a time-compatible value ? If the latter is there a built in function for this ? Thanks.

June7 · Jan 15, 2020

Use Short Time as format property setting, not mm:ss.

Are your mm:ss values a 24-hr clock time of day (as in 17:30 hours) or elapsed time?

If this is elapsed time, should be saved as a decimal value in a number field

pbaldy · Jan 15, 2020

Also note you haven't shown your method, but in some instances minutes are represented by "n", as "m" is month.

Galaxiom · Jan 15, 2020

kirkm said:
I have two strings which are times in "mm:ss" format.
I want to put these into a tables Date/Time field, also formatted "mm:ss

Note the maximum time period that can be displayed like this is 59:59.

Cronk · Jan 16, 2020

Actually, I believe the maximum displayed for any time when formatted "mm:ss" is 12:59 because mm is for months.

Maybe the OP would have got what was desired with formatting "nn:ss"

Galaxiom · Jan 16, 2020

Cronk said:
Actually, I believe the maximum displayed for any time when formatted "mm:ss" is 12:59 because mm is for months.

Maybe the OP would have got what was desired with formatting "nn:ss"

Paul already noted the "n" for minutes because "m" is for month. I was just pointing out that there was a limit of 59 to the number of minutes displayed in this way (formatting a time value).

Some of us also work in SQL server where "m" is minute and "M" is month, hence Paul's qualification of "in some instances".

June7 · Jan 16, 2020

Already tested. Neither nn:ss nor mm:ss provide valid output.

Galaxiom · Jan 16, 2020

June7 said:
Already tested. Neither nn:ss nor mm:ss provide valid output.

Under what circumstances?

nn:ss works fine in the Format Property of a control or the Format() function.

Galaxiom · Jan 16, 2020

To convert a string to time use the CDate function.

Code:

CDate(strTime)

MajP · Jan 16, 2020

As stated if this is elapsed time then it needs to be stored as a long integer representing the amount of seconds, using a date field is a waste of time. It is not a time and cdate is not going to work. So store as seconds and display and enter in MM:SS or seconds. You will probably need an unbound control on the form to enter as MM:SS and convert to seconds.

Use these

Code:

'use to convert Seconds into MM:SS
Public Function SecondsToMMSS(TheSeconds As Long) As String
  Dim MM As String
  Dim SS As String
  
  MM = Format(TheSeconds \ 60, "00")
  SS = Format(TheSeconds Mod 60, "00")
  SecondsToMMSS = MM & ":" & SS
End Function

'Used to convert MM:SS to seconds
Public Function MMSStoSeconds(MMSS As String) As Long
  MMSStoSeconds = CLng(Split(MMSS, ":")(0)) * 60 + CLng(Split(MMSS, ":")(1))
End Function

June7 · Jan 16, 2020

Galaxiom said:
Under what circumstances?

nn:ss works fine in the Format Property of a control or the Format() function.

Tested in table field Format property and Format() in Immediate Window.

The time is 11:28 AM

?Format(Now(),"nn:ss")
28:52

?Format(Now(),"mm:ss")
01:17

?Format(Now(),"Short Time")
11:28

isladogs · Jan 16, 2020

June
The first example you gave the correct time. Actually all were 'correct' in the sense that they showed what they were designed to do.

Also from the Immediate window a few seconds apart:

Code:

?Time
20:36:11 
?Format(Time, "nn:ss")
36:16
?Format(Time, "hh:nn")
20:36

?Now
16/01/2020 20:40:03
?Format(Now, "nn:ss")
40:06
?Format(Now, "hh:nn")
20:40

The_Doc_Man · Jan 16, 2020

kirkm,

Part of the problem is that Date/Time fields are actually stored numerically as a typecast (alternate interpretation) of a DOUBLE. IF you have a time as a string showing minutes and seconds (which I shall call xInput assumed to be in "nn:ss" format with leading zeroes as appropriate) then the correct way to convert that is something like

Code:

Dim xTime as String, dtNNSS as Date

xTime = "# 00:" & xInput & "#"
dtNNSS = CDate( xtime )

The keys are (a) adding in the hours field even though it is zero, (b) bracketing the time string with the octothorpe character (#), and then (c) using the appropriate function for conversion. The concatenation and function call can be done in a query, too, though it gets trickier because of quoting issues.

Hope this helps you to see where you are going.

Just for further clarification, part of the awkwardness is because the date/time formatting routines were designed to only deal with numbers that could eventually lead to valid and meaningful and unequivocal dates or times. In isolation, you have ask what this number means - 12:10. Is that 12 minutes and 10 seconds? Or 12 Noon plus 10 minutes? That ambiguity leaves Access not sure what to do.

June7 · Jan 16, 2020

Thanks, Colin - I do see now how I was reading question and results incorrectly. So "nn:ss" is likely what OP wants. Unless this is really about elapsed time.

Times from Strings (1 Viewer)

kirkm

Registered User.

June7

AWF VIP

pbaldy

Wino Moderator

Galaxiom

Super Moderator

Cronk

Registered User.

Galaxiom

Super Moderator

June7

AWF VIP

Galaxiom

Super Moderator

Galaxiom

Super Moderator

MajP

You've got your good things, and you've got mine.

June7

AWF VIP

isladogs

MVP / VIP

The_Doc_Man

Immoderate Moderator

June7

AWF VIP

Similar threads

Users who are viewing this thread