Convert Integer to hour (Military time) 1

[jpl458]

Traying to creae query to used as a summary table for inout into other queries.

Table has UserStart and UserEnd as date times, and those time will span the hour boundries. Example: UserStart = 7/6/2003 09:45:15 and UserEnd = 7/6/2009 10:23:10. Thus I have to split the difference between the times to that part goes onto the 10:00 hour and the rest goes into the 09:00 hour. If I can convert the inter result of Hour(UserEnd) to a proper date then I can use DateDiff to get the parts I need. So the questionis how do you convert and integer into an acceptble time? Example: Hour(UserEnd) = 8, and turn that 8 into 08:00:00 that couldbe used in datediff.

Have tried all kind of Rube Goldberg things, non of which workd. Thought I had it but kept getting a huge number cback instead of a time (probably the difference from 1/1/1900)

Long way for a simple question.

Thanks

jpl

 

[SkipVought]

hi,

Date/Time values are numbers. The integral part is the Date part (the number of days since 1899/12/31) and the fractional part is the Time part. So TIME is not an integer at all.

You need to convert HOURS to DAYS. Therein lies your problem: "but kept getting a huge number cback instead of a time "

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Why not simply use something like this ?
DateDiff("h", UserStart, UserEnd)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpl458]

Skip, still confused. How do I cinvert hours to day and what do I convert? According to MS the Hours function returns an integer value, in my example, the number 8 representing the 8th hour. How do I convert that into 08:00:00 for use in datediff.

PHV , in order to get usage by houe I have to split the duration (DateDiff("h", UserStart, UserEnd) so the each part of the duration fall into the whoe hour boundry. 9:45 am tp 10:14 is 29 minutes, 15 of which are in the 09 hour and 14 are in the 10 hour. If the start hour and the end hour are the same, then the whole duration is in that hour, etc.

Hope this makes sense.

jpl

 

[PWise]

JPL:

Can you please provide a few examples and what you want the out put to be?

 

[SkipVought]

How do I cinvert hours to day and what do I convert?

use TimeSerial(HOURS,MINUTES,SECONDS), maybe like this...

msgbox TimeSerial(Hours(YourDateTimeValue),0,0)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jpl458]

Made some progress. I now can get a time with no minutes or second using Skips suggestion of TimeSerial using:

BorderHr: TimeSerial([HourNo],0,0) in the QBE Grid. Hour No is the result of HourNo:Hour(UserEndTime). This produced a time examp 9:00:00 AM

I then did the following:

up: DateDiff("n",UseEndTime,BorderHr) and output was
-57669122. If this the MS Number then it should convert to 2 min and 22 seconds. But at a loss as to how to do that.

thanks again for everyones help

jpl

 

[alvechurchdata]

You could also use the TimeValue function:

tEnd=timevalue(Hour(UserEnd) & ":00:00")

but it's a bit of a kludge because it's really expecting the time as a string.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[SkipVought]

now can get a time with no minutes or second

Well guess what. Skip's formula ...

msgbox TimeSerial(Hours(YourDateTimeValue),[b][red]0,0[/red][/b])

has ZEROS for minutes and seconds. Work it out, man!!!!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jpl458]

When I said no miniutes or seconds, I ment Zero minutes and Zero Second. As I mentioned Skips suggestion worked, but the outpot of the ensuing DateDiff yielded a value of -57669122 instead of 2 mintes and 22 secods.

Thanks for your help

jpl

 

[PHV]

What about this ?
Format(UserEnd-UserStart,"h:nn:ss")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpl458]

Thanks for the suggestion, but didn't work, the result was #error.

Datediff works for start and end times Tha is not the problem. I need to generate % of utilization by hour. Unfornunately start and end times don't fall on even hours, but span 1 maybe 2 hours. Example starttime = 07/06/2009 09:45:00 and Endtime = 07/06/2009 10:13:23. In hour to collect the data by hour I need to create datetimes that equal both 07/06/2009 09:00:00 and 7/6/2009 10:00:00 that i can use in datediff statement to the number of minutes that occure in both the 9 O'clok and 10 O'clock hours. But remember that that hours of operation are from 8AM to 10PM, so each record has a different start and end time throughout the day. There are around 250,000 rows in the table

I have spent a lot of time experimenting and reading and loggin time in the immediate window, and can't get it to work. (Sort of reminds me a a big White Whale at this point.)

Thanks again for you help.

Hope this explanation is clear.

jpl

 

[Golom]

The fundamental problem is that there are several possible ways that a StartTime - EndTime pair could overlap a particular hour and each of them requires a different calculation. To illustrate what I mean by that I've thrown together a function that does the calculations.

Public Function MinutesInHour(TheHour As Integer, _
                              ByVal StartTime As Date, _
                              ByVal EndTime As Date) As Integer

    ' Strip of the DATE portion of StartTime and EndTime
    StartTime = StartTime - Int(StartTime)
    EndTime = EndTime - Int(EndTime)

    ' If the StartTime begins AFTER the specified hour OR
    ' the EndTime ends BEFORE the hour then there are no minutes
    ' in the hour.
    If Hour(StartTime) > TheHour Or Hour(EndTime) < TheHour Then
        MinutesInHour = 0
        Exit Function
    End If

    ' There are four possible cases
    ' - StartTime begins before TheHour and EndTime ends after it
    ' - StartTime begins during TheHour and EndTime ends after it.
    ' - StartTime begins before TheHour and EndTime Ends During TheHour
    ' - StartTime begins during TheHour and EndTime Ends during TheHour

    If Hour(StartTime) < TheHour And Hour(EndTime) > TheHour Then
        MinutesInHour = 60

    ElseIf Hour(StartTime) = TheHour And Hour(EndTime) > TheHour Then
        MinutesInHour = DateDiff("n", StartTime, TimeSerial(TheHour + 1, 0, 0))

    ElseIf Hour(StartTime) < TheHour And Hour(EndTime) = TheHour Then
        MinutesInHour = DateDiff("n", TimeSerial(TheHour, 0, 0), EndTime)

    ElseIf Hour(StartTime) = TheHour And Hour(EndTime) = TheHour Then
        MinutesInHour = DateDiff("n", StartTime, EndTime)

    End If

End Function

There are several ways that you may use that such as calling it from SQL or as a purely procedural VBA approach.

NB: The above is untested so please excuse any typos.

 

[MajP]

I modified Golom's suggestion to handle rolling into the next day. If you never roll past midnight then his suggestion will work. But assume you have a start of 1/1/2009 22:00:00 and an end of 1/2/2009 0900, then Golom's code will not work because it will appear that the end hour is less than the begin hour.

Public Function MinutesInHour(TheHour As Integer, _
                              ByVal startTime As Date, _
                              ByVal EndTime As Date) As Integer

    'Check to see if span midnight
    Dim blnSpanMidnight
    Dim hourStart As Date
    Dim hourEnd As Date
    Dim incrementStart As Date
    Dim incrementEnd As Date
    
    hourStart = Hour(startTime)
    hourEnd = Hour(EndTime)
    If Int(startTime) < Int(EndTime) Then
      blnSpanMidnight = True
      If hourStart <= TheHour And hourEnd <= 23 Then
        incrementStart = DateSerial(Year(startTime), Month(startTime), Day(startTime)) + TimeSerial(TheHour, 0, 0)
        incrementEnd = DateSerial(Year(startTime), Month(startTime), Day(startTime)) + TimeSerial(TheHour, 59, 59)
      ElseIf TheHour >= 0 And TheHour <= hourEnd Then
        incrementStart = DateSerial(Year(EndTime), Month(EndTime), Day(EndTime)) + TimeSerial(TheHour, 0, 0)
        incrementEnd = DateSerial(Year(EndTime), Month(EndTime), Day(EndTime)) + TimeSerial(TheHour, 59, 59)
      Else
        Exit Function
      End If
    ElseIf Not (hourStart > TheHour Or hourEnd < TheHour) Then
       incrementStart = DateSerial(Year(startTime), Month(startTime), Day(startTime)) + TimeSerial(TheHour, 0, 0)
       incrementEnd = DateSerial(Year(startTime), Month(startTime), Day(startTime)) + TimeSerial(TheHour, 59, 59)
    Else
      Exit Function
    End If
    
    'MsgBox incrementStart & "   " & incrementEnd
    ' There are four possible cases
    ' - StartTime begins before TheHour and EndTime ends after it
    ' - StartTime begins during TheHour and EndTime ends after it.
    ' - StartTime begins before TheHour and EndTime Ends During TheHour
    ' - StartTime begins during TheHour and EndTime Ends during TheHour
    'Debug.Print incrementStart
    If startTime <= incrementStart And incrementEnd <= EndTime Then
        Debug.Print "Spans Increment"
        MinutesInHour = 3600
    ElseIf startTime > incrementStart And incrementEnd <= EndTime Then
        Debug.Print "Starts in Increment"
        MinutesInHour = DateDiff("s", startTime, incrementEnd)
    ElseIf startTime <= incrementStart And incrementEnd >= EndTime Then
        Debug.Print "Ends in increment"
        MinutesInHour = DateDiff("s", incrementStart, EndTime)
    ElseIf startTime >= incrementStart And EndTime <= incrementEnd Then
        Debug.Print "Starts and ends in increment"
        MinutesInHour = DateDiff("s", startTime, EndTime)
    End If
    MinutesInHour = Round(MinutesInHour / 60, 0)
End Function

Public Sub TestTime()
  Debug.Print "Start In Range Day 1: Hour 21" & "  Start 1/1/2009 21:30:00 " & " End 1/2/2009  09:00:00  Minutes In Hour " & MinutesInHour(21, #1/1/2009 9:30:00 PM#, #1/2/2009 9:00:00 AM#)
  Debug.Print "Span Range day 1: Hour 23" & "  Start 1/1/2009 21:30:00 " & " End 1/2/2009  09:00:00  Minutes In Hour " & MinutesInHour(23, #1/1/2009 9:30:00 PM#, #1/2/2009 9:00:00 AM#)
  Debug.Print "Span Range day 2: Hour 02" & "  Start 1/1/2009 21:30:00 " & " End 1/2/2009  09:00:00  Minutes In Hour " & MinutesInHour(2, #1/1/2009 9:30:00 PM#, #1/2/2009 9:00:00 AM#)
  Debug.Print "All in range: Hour 21" & "  Start 1/1/2009 21:30:00 " & " End 1/1/2009  21:50:00  Minutes In Hour " & MinutesInHour(21, #1/1/2009 9:30:00 PM#, #1/1/2009 9:50:00 PM#)
  Debug.Print "End in Range Hour 22" & "  Start 1/1/2009 21:30:00 " & " End 1/1/2009  22:15:00  Minutes In Hour " & MinutesInHour(22, #1/1/2009 9:30:00 PM#, #1/1/2009 10:15:00 PM#)
  Debug.Print "End In Range second Day Hour 02" & "  Start 1/1/2009 21:30:00 " & " End 1/2/2009  02:15:00  Minutes In Hour " & MinutesInHour(2, #1/1/2009 9:30:00 PM#, #1/2/2009 2:15:00 AM#)
End Sub

output:
Starts in Increment
Start In Range Day 1: Hour 21 Start 1/1/2009 21:30:00 End 1/2/2009 09:00:00 Minutes In Hour 30
Spans Increment
Span Range day 1: Hour 23 Start 1/1/2009 21:30:00 End 1/2/2009 09:00:00 Minutes In Hour 60
Spans Increment
Span Range day 2: Hour 02 Start 1/1/2009 21:30:00 End 1/2/2009 09:00:00 Minutes In Hour 60
Starts and ends in increment
All in range: Hour 21 Start 1/1/2009 21:30:00 End 1/1/2009 21:50:00 Minutes In Hour 20
Ends in increment
End in Range Hour 22 Start 1/1/2009 21:30:00 End 1/1/2009 22:15:00 Minutes In Hour 15
Ends in increment
End In Range second Day Hour 02 Start 1/1/2009 21:30:00 End 1/2/2009 02:15:00 Minutes In Hour 15

 

[Golom]

MajP

Good enhancement.

I just ignored the midnight rollover because the OP said

... hours of operation are from 8AM to 10PM ...

Never hurts to be comprehensive however.

 

[MajP]

Yeah, I did not see the part about the hours of operation being between 8 and 10. I should have read closer.

 

[jpl458]

Golom and MajP thanks again for the crisp code. I will be using the Function to fill a table that I have created that has 365 rows (Days) with Transaction date and 16 hour columns. Read the transmaster, do the function and add result to the Utilization table. Once I have that done then it is an easy matter to slice and dice in all sorts of ways. Plus I can save the table at year-end for year-on year-analysis.

But the graphics in ACCESS leave something to be desired, so I will use EXCEL as the graphing tool.

Will be doing some testing wiuth the function tomorrow.

Thanks again, made my life a lot easier.

jpl

 

[jpl458]

Sorry for the delay in testing, but got sidetracked. Trying the function above, the first one since data does not span days. I enter the following in the immediate window, which I copied from MajP's test code

MinutesInHour(21, #1/1/2009 9:30:00 PM#, #1/1/2009 9:50:00 PM#)

I get "Compile Error"
"Expected variable or procedure not module"

Tried several variations. Get the same answer if I try the function in "On Click" event.

Thanks again

jpl

 

[jpl458]

Disregard previous post. I had the function name the same as the module name. Once I changed the module name all was right with the world - at least my little part - for tonite.

Sorry

jpl