Solved Split Minutes into Hours, Minutes, Seconds

[MattBaldry]

Hi All,

Does anyone have a snippet somewhere on how to separate a minute field into each hour, minutes, seconds?

IE 75.5 would be 1 hour 15 minutes 30 seconds

~Matt

 

[Gasman]

No, but a quick search here will get you hours and minutes

Solved - Help with column of hours and minutes summing as hh:mm

I track the time spent on a task in hours (as number) and minutes (as number) . A person may have 7 entries in a month. I need to be able to show total hours and total minutes on the report per each employee. Currently, i have a calculated field that takes my hours and converts them to...

www.access-programmers.co.uk

I would probably adapt that.

Of course, then you have google.

Google Search

 

[ebs17]

? CDate(75.5 / (24 * 60))
01:15:30

? Hour(#01:15:30#), Minute(#01:15:30#), Second(#01:15:30#), Format(#01:15:30#, "hh:nn:ss") 
 1             15            30           01:15:30

Do you know how to explain the 24 and the 60?

 

[arnelgp]

Public Function MinToHrMinSec(ByVal nMinutes As Double) As String
    Dim nSec As Double
    Dim nHr As Double
    Dim nMin As Double
    ' convert to seconds
    nSec = nMinutes * 60
    nHr = nSec \ 3600
    nSec = nSec - (nHr * 3600)
    nMin = nSec \ 60
    nSec = nSec - (nMin * 60)
    MinToHrMinSec = nHr & " hour" & IIf(nHr > 1, "s", "") & " " & nMin & " minute" & IIf(nMin > 1, "s", "") & " " & nSec & " second" & IIf(nSec > 1, "s", "")
End Function

 

[MattBaldry]

Public Function MinToHrMinSec(ByVal nMinutes As Double) As String
    Dim nSec As Double
    Dim nHr As Double
    Dim nMin As Double
    ' convert to seconds
    nSec = nMinutes * 60
    nHr = nSec \ 3600
    nSec = nSec - (nHr * 3600)
    nMin = nSec \ 60
    nSec = nSec - (nMin * 60)
    MinToHrMinSec = nHr & " hour" & IIf(nHr > 1, "s", "") & " " & nMin & " minute" & IIf(nMin > 1, "s", "") & " " & nSec & " second" & IIf(nSec > 1, "s", "")
End Function

Thank you, works perfect

~Matt

 

[Josef P.]

Note to #3:
Eberhard's variant would have the benefit that you have a value to calculate (not a string) and could convert the format via the format setting of a TextBox or Format function.

Example:
Control soruce: = [MinuteTextbox] / 60 / 24
Format: = h" hours "n" minutes "s" seconds"
.. but only if the time does not exceed one day.

 

[Gasman]

? CDate(75.5 / (24 * 60))

That is so neat.

 

[The_Doc_Man]

@MattBaldry - you have several workable suggestions. I will merely emphasize that some methods given to you, because they invoke the Access FORMAT function, will not work correctly if your time in minutes exceeds 24 hours, i.e. >1440 minutes. Access does not support a format template such as "hhh:nn:ss" - because that 3rd "h" would exceed 24 hours and the formatting routines can't do that.

Therefore, if you think you will possibly need such a long time span, come back for a slightly more versatile but also more robust method. If you know that you will never exceed 1440 minutes, then you are fine with one of these FORMAT-based solutions.

Note also that if you choose ArnelGP's solution, that one WOULD handle more than 1440 minutes (1 day) correctly as it does its own formatting and does not depend on the built-in FORMAT function.

 

[ebs17]

I will merely emphasize that some methods given to you, because they invoke the Access FORMAT function, will not work correctly if your time in minutes exceeds 24 hours

The conversion to a date value works without any doubts or errors. How to evaluate this date value is a subsequent question.
Format for more than 24 hours

Using a composite string as primary output is also debatable. You can't count on that, you have to break the text apart again using string functions.

 

[The_Doc_Man]

@ebs17 - While I understand perfectly well that you can overdefine FORMAT as shown in that link, it is something I would not choose to do. A custom and specifically named function, in my opinion, is the preferred solution. Date conversion DOES work correctly and I don't disagree with you there, either. But in this case, MattBaldry is doing an elapsed-time conversion, NOT a date conversion, and therefore will run into trouble if he takes the wrong path.

 

[MattBaldry]

Thanks all. I have this working perfectly now. I used arnelgps code and it did exactly what I needed.

~Matt

 

[arnelgp]

in case there are many decimal place on the returning Seconds string, like in the case of:

ex: MinToHrMinSec2(33.11)
rslt: 0 hour 33 minutes 6.59999999999991 seconds

to get rid of those 9999's, modify the function to this:

Public Function MinToHrMinSec(ByVal nMinutes As Double) As String
    Dim nSec As Double
    Dim nHr As Double
    Dim nMin As Double
    ' convert to seconds
    nSec = CDec(nMinutes) * CDec(60)
    nHr = CDec(nSec) \ CDec(3600)
    nSec = CDec(nSec) - CDec(nHr * 3600)
    nMin = CDec(nSec) \ CDec(60)
    nSec = CDec(nSec) - CDec(nMin * 60)
    MinToHrMinSec = nHr & " hour" & IIf(nHr > 1, "s", "") & " " & _
                    nMin & " minute" & IIf(nMin > 1, "s", "") & " " & _
                    nSec & " second" & IIf(nSec > 1, "s", "")
End Function

using the new function will result to:

0 hour 33 minutes 6.6 seconds

 

[cheekybuddha]

But in this case, MattBaldry is doing an elapsed-time conversion, NOT a date conversion,

A date in Access is an elapsed time! (Number of days since '1899-12-30 00:00:00')

 

[The_Doc_Man]

Yes, David, you are correct. However, the formatting routines ALWAYS assume the system reference date/time. Time-card summation, date differences, and other date manipulation methods produce a time that is elapsed based on a different reference point. Therein lies the real problem. If you didn't mean the "real" reference date, you have to roll your own.

In another fine point, the formatting routines do not believe in fractions of a second whereas, if you had a high-precision timer (it's available through an API call), you could get fractions down to single milliseconds or even into the level of microseconds. Today's elapsed day count since the reference date is about 45.3K, which is less than 65K. That means it requires 16 bits. Seconds per day is just above 87K, which takes 17 bits to express. For the next 20 thousand days, you use 16 bits for days and 17 bits for seconds, or 33 bits total. Since DATE is merely a typecast of DOUBLE which has a 53-bit mantissa, you have about 20 bits left for the fraction, which equates to microsecond precision. But don't ask the time routines to make use of that precision, even if you use the HPT (High precision timer) API. The format routines won't even try.

 

[ebs17]

To stay on the record: A value of 75.5 minutes does not indicate the desired high precision in the millisecond range, and for me it also does not indicate measurements over times greater than or equal to 24 hours. For me, a normally determined time would be sufficient, which would provide all the desired information.

But the questioner knows what he has and what he needs.

 

[MattBaldry]

For the full background on this I have a time labour calculator for our internal BOMs. Our company timings are done as a decimal of the minute, in 6 second (0.10) or 15 second (0.25) breaks. When I export our BOM into a 3rd party application, their labour is broken into 3 fields, Hours, Minutes and Seconds.

I used this to find a way of converting my decimal into HH:MM:SS to then split into left, mid and right to match the 3rd party import.

The code arnelgp provided worked perfectly, I think changed it slightly to appear as 00:00:00 to make the left, mid, right easier.

    Dim nSec As Double
    Dim nHr As Double
    Dim nMin As Double
    ' convert to seconds
    nSec = nMinutes * 60
    nHr = nSec \ 3600
    nSec = nSec - (nHr * 3600)
    nMin = nSec \ 60
    nSec = nSec - (nMin * 60)
    MinToHrMinSec = Format(nHr, "00") & ":" & IIf(nHr > 1, "s", "") & Format(nMin, "00") & ":" & IIf(Format(nMin, "") > 1, "", "") & Format(nSec, "00") & "" & IIf(Format(nSec, "00") > 1, "", "")

Thank you all for your help and feedback. This is where I learn what I have to do, and this did the job for me.

~Matt

 

[Gasman]

If you had left it as a date as per post #3, then you could have just used Hour, Minute & Second functions?
I used each in turn in the Immediate window.

? second(CDate(75.5 / (24 * 60)))
 30 
 15 
 1