Time format

[Guest]

In my form I have 2 fields that we enter date and time into, the input mask
is 99/99/9999\ 99:99;0;_ for both of them and it works fine for putting in
the date and time but I also have to subtract one time from the other
=([field1]-[field2])*24 is what my 3rd fields format is. What I'm getting is
the correct hours but the minutes are not coming out incorrect I think my
format is wrong somewhere. This is what it looks like 49.75, 49 hours is
correct but how do I get it to show 45 for the minutes instead of 75.

 

[Guest]

Hi Tankerman,

Try this KB article out:

How to Calculate Daily Hours Based on Clock In/Clock Out Times
http://support.microsoft.com/?id=237958


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[JK]

Tamkerman

I beleive that this will do it:

+++++++++

Public Function FormatTime( _
tmNumber As Double, _
Optional strSeparator As String = ":", _
Optional inclSuffix As Boolean = False, _
Optional dispDays As Boolean = False) As String
'-------------------------
'Return a string with time format, allowing more then
'31 days or more than 24 hours

'****Does NOT allow for more than 60 Minutes.
'*** Does NOT go to seconds or less ***

'Written by JK
'---------------------------------------

'Parameters:
'Examples are based on 2 days 15 hours 30 minutes

'tmNumber: Time Number, 1= 1 day, example=2.645833333
'strSparator: Time saparator eg ":" eg 02:15:30
'InclSuffix: Add suffix to time e.g.
' 02d:15h:30m (no option to change suffix)
'displayDays: If Fales, dispaly day in hours, eg.
' 02d:15h:30m = 63h:30m


Dim tmpNum As Double, _
tmpDD As Long, _
tmpHH As Long, _
tmpMM As Long, _
strDD As String, _
strHH As String, _
strMM As String, _
isNeg As Boolean

'tmpNum: Temprary storage
'tmp/StrDD: How days are displayed, if at all
'tmp/strHH: Number of hours to display
' depending on dispalyDays
'tmp/strMM: Number of Minutes
'isNeg: Negtive time indicator

tmpNum = tmNumber
isNeg = IIf(tmpNum < 0, True, False)
tmpNum = Abs(tmpNum)
tmpDD = Int(tmpNum)
tmpNum = (tmpNum - Int(tmpNum)) * 24
tmpHH = Int(tmpNum)
tmpHH = tmpHH + IIf(dispDays, 0, tmpDD * 24)
tmpNum = (tmpNum - Int(tmpNum)) * 60
tmpMM = Round(tmpNum, 0)

If tmpMM = 60 Then
tmpMM = 0
tmpHH = tmpHH + 1
End If


If dispDays Then
strDD = IIf(tmpDD <= 9, "0", "") _
& Trim(Str(tmpDD)) _
& IIf(inclSuffix, "d", "") _
& strSeparator
Else
strDD = ""
End If

strHH = IIf(tmpHH <= 9, "0", "") _
& Trim(Str(tmpHH)) _
& IIf(inclSuffix, "h", "") _
& strSeparator

strMM = IIf(tmpMM <= 9, "0", "") _
& Trim(Str(tmpMM)) _
& IIf(inclSuffix, "m", "")

FormatTime = IIf(isNeg, "-", " ") _
& strDD & strHH & strMM

End Function

+++++

Regards/JK

 

[Guest]

Date/time values in Access are stored as a 64 bit floating point number as an
offset from 30 December 1899 00:00:00, the integer part representing the days
the fractional part the time of day. Consequently when you subtract one
value from another and multiply by 24 you get the fractional part of the hour
in the result (the .75 in your case).

You can convert the numeric value resulting from date/time arithmetic into a
time format quite easily. The following function for instance returns the
value in the format hh:nn:ss:

Public Function TimeDuration(dblDuration As Double) As String

Const HOURSINDAY = 24
Dim strDays As String
Dim strMinutesSeconds As String

'get number of days
strDays = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

TimeDuration = strDays & strMinutesSeconds

End Function

Paste it into a standard module and call it in your form as the
ControlSource of the unbound text box with:

=TimeDuration([field1]-[field2])

Ken Sheridan
Stafford, England

 

[Guest]

I entered the function just as you have it and I keep getting "invalid
outside procedure" when I debug, this is what is wrong and I don't know how
to fix it (dblDuration)

Date/time values in Access are stored as a 64 bit floating point number as an
offset from 30 December 1899 00:00:00, the integer part representing the days
the fractional part the time of day. Consequently when you subtract one
value from another and multiply by 24 you get the fractional part of the hour
in the result (the .75 in your case).

You can convert the numeric value resulting from date/time arithmetic into a
time format quite easily. The following function for instance returns the
value in the format hh:nn:ss:

Public Function TimeDuration(dblDuration As Double) As String

Const HOURSINDAY = 24
Dim strDays As String
Dim strMinutesSeconds As String

'get number of days
strDays = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

TimeDuration = strDays & strMinutesSeconds

End Function

Paste it into a standard module and call it in your form as the
ControlSource of the unbound text box with:

=TimeDuration([field1]-[field2])

Ken Sheridan
Stafford, England

In my form I have 2 fields that we enter date and time into, the input mask
is 99/99/9999\ 99:99;0;_ for both of them and it works fine for putting in
the date and time but I also have to subtract one time from the other
=([field1]-[field2])*24 is what my 3rd fields format is. What I'm getting is
the correct hours but the minutes are not coming out incorrect I think my
format is wrong somewhere. This is what it looks like 49.75, 49 hours is
correct but how do I get it to show 45 for the minutes instead of 75.

 

[Guest]

Ken, I had entered the function wrong, it works great, THANKS for the help

Date/time values in Access are stored as a 64 bit floating point number as an
offset from 30 December 1899 00:00:00, the integer part representing the days
the fractional part the time of day. Consequently when you subtract one
value from another and multiply by 24 you get the fractional part of the hour
in the result (the .75 in your case).

You can convert the numeric value resulting from date/time arithmetic into a
time format quite easily. The following function for instance returns the
value in the format hh:nn:ss:

Public Function TimeDuration(dblDuration As Double) As String

Const HOURSINDAY = 24
Dim strDays As String
Dim strMinutesSeconds As String

'get number of days
strDays = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

TimeDuration = strDays & strMinutesSeconds

End Function

Paste it into a standard module and call it in your form as the
ControlSource of the unbound text box with:

=TimeDuration([field1]-[field2])

Ken Sheridan
Stafford, England

In my form I have 2 fields that we enter date and time into, the input mask
is 99/99/9999\ 99:99;0;_ for both of them and it works fine for putting in
the date and time but I also have to subtract one time from the other
=([field1]-[field2])*24 is what my 3rd fields format is. What I'm getting is
the correct hours but the minutes are not coming out incorrect I think my
format is wrong somewhere. This is what it looks like 49.75, 49 hours is
correct but how do I get it to show 45 for the minutes instead of 75.