Converting Total Minutes into Days, Hours & Minutes

[Guest]

I would like to create my own vacation db. 1 day = 8 hours. I want to track
my Vacation Time, Compt Time & Personal Time. Each displays the total minutes
I have available. Minutes are my smallest unit of measurement.
Q1 - How do I take my total minutes (25320.0) and display it as: 52-days,
6-hours, 0-minutes?
Q2 - Time used is subtracted in this order: from Compt Time, Personal
time, then Vacation time. How would this be set up.

Thanks again for all you.

 

[Douglas J. Steele]

A1

Function FormatMinutes(TotalMinutes As Long) As String

Dim lngDays As Long
Dim lngHours As Long
Dim lngHoursAndMinutes As Long

lngDays = TotalMinutes \ 480
lngHoursAndMinutes = TotalMinutes Mod 480
lngHours = lngHoursAndMinutes \ 60

FormatMinutes = lngDays & "-Days, " & _
lngHours & "-Hours, " & _
lngHoursAndMinutes - (lngHours * 60) & "-minutes"

End Function

A2

Afraid I don't understand the question. Can you give an example to clarify
what you mean?

 

[Arvin Meyer [MVP]]

Try this. Watch out for Word wrap:

Function DayHourMin(varMin As Variant)

Dim lngTotalHours As Long
Dim lngTotalMins As Long
Dim lngDays As Long
Dim lngHours As Long
Dim lngMin As Long

varMin = varMin / 1440

lngDays = Int(CSng(varMin))
lngTotalHours = Int(CSng(varMin * 24))
lngTotalMins = Int(CSng(varMin * 1440))
lngHours = lngTotalHours Mod 24
lngMin = lngTotalMins Mod 60

DayHourMin = lngDays & " Days " & lngHours & " Hours " & lngMin & " Minutes"

End Function

 

[Guest]

Thanks for responding Douglas, I appreciate your assistance. Maybe this will
help. Example: I currently have
Vacation: 5-days, 6-hours, 0-minutes
Personal: 0-days, 2-hours, 0-minutes
Compt: 0-days, 2-hours, 0-mintues

I use 6 hours - which shoud be deducted from compt, personal, then vacation
leaving the balance of:
Vacation: 6-days, 4-hours, 0-minutes
Personal: 0-days, 0-hours, 0-minutes
Compt: 0-days, 0-hours, 0-mintues
Thanks Again

 

[Guest]

Thanks Arvin, I will try it now.

Try this. Watch out for Word wrap:

Function DayHourMin(varMin As Variant)

Dim lngTotalHours As Long
Dim lngTotalMins As Long
Dim lngDays As Long
Dim lngHours As Long
Dim lngMin As Long

varMin = varMin / 1440

lngDays = Int(CSng(varMin))
lngTotalHours = Int(CSng(varMin * 24))
lngTotalMins = Int(CSng(varMin * 1440))
lngHours = lngTotalHours Mod 24
lngMin = lngTotalMins Mod 60

DayHourMin = lngDays & " Days " & lngHours & " Hours " & lngMin & " Minutes"

End Function
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Douglas J. Steele]

I assume that's an error in your example: that what's left as vacation
should be Vacation: 5-days, 4-hours, 0-minutes, not 6-days, 4-hours,
0-minutes.

What do your tables look like? Hopefully Vacation, Personal and Compt aren't
stored as day, hours, minutes, but strictly total minutes. (Also, hopefully
they aren't 3 separate fields in the same table)

In essence, you're going to have to use VBA to figure out what to do.

Assuming what you used is stored in a variable lngUsed, you need something
like the following pseudocode:

If Compt >= lngUsed Then
Compt = Compt - lngUsed
lngUsed = 0
Else
lngUsed = lngUsed - Compt
Compt = 0
If Personal >= lngUsed Then
Personal = Personal - lngUsed
lngUsed = 0
Else
lngUsed = lngUsed - Personal
Person = 0
If Vacation >= lngUsed Then
Vacation = Vacation - lngUsed
lngUsed = 0
Else
lngUsed = lngUsed - Vacation
Vacation = 0
End If
End If
End If

If lngUsed > 0 Then
MsgBox "Not everything could be allocated"
End If

That's about the best I can do without knowing more about your setup.