‘am’ or ‘pm’ time stamps (VBVBA)

Topic

New Reply

I get a table that is sent to us each day with two text fields in it that represent times (see attached) . As you will see, the SessionStart and the Finished_Time each have values like “8:03:21” or “10:51:07” and are text fields. Neither field is of data type DateTime and neither is stampped “AM or PM”

I need to do a calculation where the Total_Daily_Time = Max(Finished_Time) – SessionStart (SessionStart repeats for each Name). The ActivityID field puts each row in chronological order from earliest to latest. I have a query to get the Max ([ActivityID]) for each Name and therefore associate the Finished_Time to it.

In a Recordset, I read in each row and create each field to look like a DateTime format by adding the mm/dd/yy before each of the times. I am to the point where each field looks something like this “12/05/2005 8:04:31” However, to do the calculation Total_Daily_Time = (DateDiff(“s”,[SessionStart],[Finished_Time])/60)/60, I need to know if these times are “AM” or “PM”.

I can make the assumption that any times that begin with 12,1,2,3,4,5 are “PM”.

Any logic help is greatly appreciated.

Reply | Quote

Replies

While I can’t look at the details of this, you could add 12 hours to all times before some “opening time” like 8 (AM) (i.e., the hour is < 8) . Then, your sequence would be 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, with possible confusion between 8, 9, 10, and 11 AM and PM. Hopefully those evening hours, and the midnight hour, don't matter to your application.

Check out the DateAdd function for doing the “time math.”

Reply | Quote

Does this do what you want?

SELECT Sample_Data.Name, TimeValue([SessionStart]) AS S, TimeValue([Finished_Time]) AS F, [F]--0.5*([F]<) AS T
FROM qryMaxFinish INNER JOIN Sample_Data ON qryMaxFinish.MaxOfActivityID = Sample_Data.ActivityID;

Reply | Quote

Thanks Hans. The TimeValue function will help with the calculation portion but I still need a way to determine “AM” or “PM”. It appears that the TimeValue function assumes “AM” if the argument is not stamped. I have decided that looping through a recordset of each persons Finished_Time and counting the changes in the Hours will help determine if the span of time crosses noon. If that is the case, I know that SessionStart is AM and Finished_Time is PM.

I put in this block of code….
=======================================
Sub TestTime()
‘some other dim statements here…

Dim intFirstFinish As Integer
Dim intNextFinish As Integer
Dim intLastFinish As Integer

‘ I open a recordset here called rsdata…
‘ I get the count of records in rsdata. intRecordCount

‘ val function captures the numbers in the hours portion of the string.
intFirstFinish = Val(rsData.Fields(“Finished_Time”))
intLastFinish = Val(rsData.Fields(“Finished_Time”))

For i = 1 To intRecCount

intNextFinish = Val(rsData.Fields(“Finished_Time”).Value)
If intNextFinish intFirstFinish Then
‘calculation of the number of times the hour changes.

intHrs = intHrs + 1

intFirstFinish = intNextFinish

End If
rsData.MoveNext
Next i

Select Case intHrs
‘ I think I need some logic here that says if the number of hour changes is > some number then
‘ SessionStart is AM and Finished_Time is PM
‘ Else Both are AM or Both are PM — I don’t have this logic figured out yet.

End Select

Reply | Quote

Here is another version that uses only queries. Does it calculate the times correctly, in your opinion?

Reply | Quote