Calculate Time

John_McDonald · June 20, 2020, 1:49am

I am writing a time keeper for my small business (cheaper than paying someone else and better than spreadsheets) and I have run into a conundrum. I know how to create the time stamp I want… BUT getting it to calculate hours… not having much luck.

Shift Start = 09:05:38
Lunch Start = 13:23:47
Lunch End = 13:54:02
Shift End = 17:12:58

So I have to calculate the lunch time taken and the total hours of a shift. Once I get that the rest is easy peasy. So… help?!

anon93744516 · June 20, 2020, 1:57am

If you’re using API 2.0, maybe the DateInterval class can help subtract the correct amount of time?

I hope that helps.

Thom_McGrath · June 20, 2020, 3:22am

Also with API2, you could use SecondsFrom1970. Just subtract the end from the start and you have the number of seconds. Divide by 3600, and you have the number of hours. This will work for the older Date class’s TotalSeconds property, but be aware that it does not correct for timezone differences like DateTime.SecondsFrom1970 does. This might not be an issue in your case, but if your start and end times cross a daylight savings boundary, your math would be wrong if you don’t compensate.

Hector_Marroquin · June 20, 2020, 3:30am

Non API 2 quick and dirty code that perhaps might help

Dim shiftStart As New date
Dim shiftEnd As New date
Dim lunchStart As New date
Dim lunchEnd As New date

shiftStart.Hour = 9
shiftStart.Minute = 5
shiftStart.Second = 38

shiftEnd.Hour = 17
shiftEnd.Minute = 12
shiftEnd.Second = 58

lunchStart.Hour = 13
lunchStart.Minute = 23
lunchStart.Second = 47

lunchEnd.Hour = 13
lunchEnd.Minute = 54
lunchEnd.Second = 58

Dim shiftHours As Double
shiftHours = shiftEnd.TotalSeconds - shiftStart.TotalSeconds - (lunchEnd.TotalSeconds - lunchStart.TotalSeconds)
shiftHours = (shiftHours / 60) / 60 
MessageBox("Total Shift Hours: " + shiftHours.ToString )

AlbertoD · June 20, 2020, 3:37am

As you can see, there are several ways to resolve your problem.

If you can give us the code you are using to create those time stamps, we can provide a solution, or if you prefer a hint, on how to get what you need.

Also, you want the lunch time taken and the total of hours in the same time stamp format? or just hours and decimal value, like 7.71 hours?

Hector_Marroquin · June 20, 2020, 3:52am

I thought about that but offered a simple example of one way it could be handled. We don’t have much info yet to be able to offer any more help

AlbertoD · June 20, 2020, 4:23am

My comment was not directed at your code, just confirming that we don’t have enough information to give the exact answer.

In another note, I’m testing API 2.0, I need to learn, and it is interesting for me that if I use this:

Var shiftTime As DateInterval = shiftEnd - shiftStart - lunchTime

Where shiftEnd and shiftStart are DateTime and lunchTime is DateInterval I get:

[quote]Undefined operator. Type DateInterval does not define “Operator_Subtract” with type DateInterval
Var shiftTime As DateInterval = shiftEnd - shiftStart - lunchTime
[/quote]
and if I change the code to:

Var shiftTime As DateInterval = shiftEnd - lunchTime - shiftStart

there is no error.

I guess shiftEnd - shiftStart create a DateInterval and there is no Operator_Substract for doing - lunchTime (another DateInterval) and by doing shiftEnd - lunchTime it creates a DateTime and subtracts another DateTime the result is a valid DateInterval. It makes sense but it is not what I expected at first.

John_McDonald · June 20, 2020, 4:25am

This is code I wrote way back that I am essentially re-using now to create time…

[code]// Variable declarations…
Dim dtDate As New Date
Dim stTime As String

// Function(s)…
If(dtDate.Hour) <= 9 Then
stTime = “0” + CStr(dtDate.Hour)
Else
stTime = CStr(dtDate.Hour)
End If

If(dtDate.Minute) >= 10 Then
stTime = stTime + “:” + CStr(dtDate.Minute) + “:”
Else
stTime = stTime + “:0” + CStr(dtDate.Minute) + “:”
End If

If(dtDate.Second) >= 10 Then
stTime = stTime + CStr(dtDate.Second)
Else
stTime = stTime + “0” + CStr(dtDate.Second)
End If

Return stTime[/code]

The formatting is how I prefer to see the time displayed. Thank you Navy!

So this is a module.method I can call from anywhere in a given program to give me the time as needed… anyhow until now I didn’t need to calculate time/hours. Because I am tracking wages as well as ensuring conformance to Komunistfornia labor laws surrounding lunches and breaks, I need to be able to calculate down to the 10th of a second. Hectors “dirty” solution looks like it will fit the bill. But I will look into it more tomorrow.

Thank you all!

Hector_Marroquin · June 20, 2020, 4:32am

[quote]My comment was not directed at your code, just confirming that we don’t have enough information to give the exact answer.

In another note, I’m testing API 2.0, I need to learn, and it is interesting for me that if I use this:[/quote]

I know

Norman_Palardy · June 20, 2020, 5:00am

[quote=493184:@Alberto DePoo]My comment was not directed at your code, just confirming that we don’t have enough information to give the exact answer.

In another note, I’m testing API 2.0, I need to learn, and it is interesting for me that if I use this:

Var shiftTime As DateInterval = shiftEnd - shiftStart - lunchTime

Where shiftEnd and shiftStart are DateTime and lunchTime is DateInterval I get:

and if I change the code to:

Var shiftTime As DateInterval = shiftEnd - lunchTime - shiftStart

there is no error.
[/quote]

I’d report this as a bug

AlbertoD · June 20, 2020, 5:04am

[quote=493185:@Ian McDonald]Dim stTime As String

// Function(s)…
If(dtDate.Hour) <= 9 Then
stTime = “0” + CStr(dtDate.Hour)
Else
stTime = CStr(dtDate.Hour)
End If

If(dtDate.Minute) >= 10 Then
stTime = stTime + “:” + CStr(dtDate.Minute) + “:”
Else
stTime = stTime + “:0” + CStr(dtDate.Minute) + “:”
End If

If(dtDate.Second) >= 10 Then
stTime = stTime + CStr(dtDate.Second)
Else
stTime = stTime + “0” + CStr(dtDate.Second)
End If

Return stTime[/quote]
The function can be changed to:

stTime = dtDate.SQLDateTime.Right(8)

instead of 3 if/else/end

You can keep using Date if it is a personal/small project because we have DateTime now, I think Date will stay for a long time. When you are ready you can start looking at DateTime.

John_McDonald · June 20, 2020, 2:31pm

Okay this has been a frustrating issue I have run into of late:

modSysFunc.mthCalcTime, line 25
There is more than one method with this name but this does not match any of the available signatures.
shiftStart.Second = Right( strStart.ToInteger, 2 )

[code]shiftStart.Hour = Left( strStart.ToInteger, 2 )
shiftStart.Minute = Mid( strStart.ToInteger, 4, 2 )
shiftStart.Second = Right( strStart.ToInteger, 2 )

shiftEnd.Hour = Left( strEnd.ToInteger, 2 )
shiftEnd.Minute = Mid( strEnd.ToInteger, 4, 2 )
shiftEnd.Second = Right( strEnd.ToInteger, 2 )

lunchStart.Hour = Left( strLncStrt.ToInteger, 2 )
lunchStart.Minute = Mid( strLncStrt.ToInteger, 4, 2 )
lunchStart.Second = Right( strLncStrt.ToInteger, 2 )

lunchEnd.Hour = Left( strLncEnd.ToInteger, 2 )
lunchEnd.Minute = Mid( strLncEnd.ToInteger, 4, 2 )
lunchEnd.Second = Right( strLncEnd.ToInteger, 2 )
[/code]

Why is this error happening? I am using the code with my changes, provided by Hector. So this is a Module.Method I am writing to calculate hours for shift and lunch, and it’s set up to return a String and takes 5 parameters.

Start Time :: strStart // Start of Shift
End Time :: strEnd // End of Shift
Lunch Start :: strLncStrt // Start Lunch
Lunch End :: strLncEnd // End Lunch
Which to Calculate

But this error:
modSysFunc.mthCalcTime, line 25
There is more than one method with this name but this does not match any of the available signatures.
shiftStart.Second = Right( strStart.ToInteger, 2 )

modSysFunc.mthCalcTime, line 26
There is more than one method with this name but this does not match any of the available signatures.

modSysFunc.mthCalcTime, line 27
There is more than one method with this name but this does not match any of the available signatures.
shiftEnd.Hour = Left( strEnd.ToInteger, 2 )

modSysFunc.mthCalcTime, line 29
There is more than one method with this name but this does not match any of the available signatures.
shiftEnd.Second = Right( strEnd.ToInteger, 2 )

modSysFunc.mthCalcTime, line 30
There is more than one method with this name but this does not match any of the available signatures.

modSysFunc.mthCalcTime, line 31
There is more than one method with this name but this does not match any of the available signatures.
lunchStart.Hour = Left( strLncStrt.ToInteger, 2 )

modSysFunc.mthCalcTime, line 33
There is more than one method with this name but this does not match any of the available signatures.
lunchStart.Second = Right( strLncStrt.ToInteger, 2 )

modSysFunc.mthCalcTime, line 34
There is more than one method with this name but this does not match any of the available signatures.

modSysFunc.mthCalcTime, line 35
There is more than one method with this name but this does not match any of the available signatures.
lunchEnd.Hour = Left( strLncEnd.ToInteger, 2 )

you get the point… why am I getting this error?

AlbertoD · June 20, 2020, 2:38pm

Left, Mid and Right need string, you are using strLncStrt.ToInteger (for example)

Is strLncStrt a String? What format does it have?

Edit: If strLncStrt has, for example, 01:10:15 and you want to get the hours, you can use strLncStrt.Left(2), seconds will be strLncStrt.Right(2). You can use Left(strLncStrt, 2) and Right(strLncStrt, 2) if you prefer.

Edit2: then you need to convert the String to Integer if you are going to assign that number to a Date

AlbertoD · June 20, 2020, 3:00pm

Example: change this code

shiftStart.Hour = Left( strStart.ToInteger, 2 )

to

shiftStart.Hour = strStart.Left(2).ToInteger

Note: .ToInteger doesn’t work in older Xojo versions, you can use .Val instead, that worked with Xojo2018r3

Thom_McGrath · June 20, 2020, 3:17pm

Since youre dealing with timeclocks and peoples money, I strongly recommend you consider the time zone. Either use Date.GMTOffset * 3600 to get the offset in seconds, or set Date.GMTOffset to 0 so youre always working in GMT. Id say in 99% of normal usage, ignoring the time zone would work fine. But this is money were talking about, where 99% accuracy isnt good enough.

Dale_Arends · June 20, 2020, 5:07pm

@Norman Palardy: Albert posted this observation earlier in this thread.

[quote]In another note, I’m testing API 2.0, I need to learn, and it is interesting for me that if I use this:

Var shiftTime As DateInterval = shiftEnd - shiftStart - lunchTime

Where shiftEnd and shiftStart are DateTime and lunchTime is DateInterval I get:

Undefined operator. Type DateInterval does not define "Operator_Subtract" with type DateInterval
Var shiftTime As DateInterval = shiftEnd - shiftStart - lunchTime

and if I change the code to:

Var shiftTime As DateInterval = shiftEnd - lunchTime - shiftStart

there is no error.[/quote]
You replied that he should report it as a bug. I’m confused why it is a bug?

According to the documentation for DateTime

[code]Operators

You can use the + operator to add a DateTime and an Interval together to get a new DateTime.
You can use the - operator to subtract a DateInterval from a DateTime to get a new DateTime.
You can use the - operator to subtract a DateTime from a DateTime to get a new DateInterval.[/code]

In Albert’s first example, he’s subtracting a DateTime from a DateTime, which yields a DateInterval, and then tries to subtract a DateInterval from that, which is an error. In his second example, he is subtracting a Dateinterval from a DateTime, which yields a DateTime, and then subtracts a DateTime from that which yields a DateInterval and is correct.

So I’m not sure where you see something that should be reported as a bug.

MarkusR · June 20, 2020, 5:21pm

if you have 2 datetimes objects (Api 2) use:

System.DebugLog "Hours " + Str((d2.SecondsFrom1970 - d1.SecondsFrom1970) / 3600.0)

one hour have 3600 seconds or 60*60

you should always log a datetime so you can calculate from before midnight to next day morning.

John_McDonald · June 22, 2020, 7:42pm

I am having issues with the Lunch time Calculation
Here’s the code:

[code]’ – Preliminary Code –

’ :–* None Defined * --:

’ Primary Variable Declaration(s)
Dim dtLnchStrt As New Date
Dim dtLnchEnd As New Date
Dim dblLnchHrs As Double
Dim strLnchHrs As String

’ Function(s) / Logic…
dtLnchStrt.Hour = strLncStrt.Left( 2 ).ToInteger
dtLnchStrt.Minute = strLncStrt.Middle( 4, 2 ).ToInteger
dtLnchStrt.Second = strLncStrt.Right( 2 ).ToInteger

dtLnchEnd.Hour = strLncEnd.Left( 2 ).ToInteger
dtLnchEnd.Minute = strLncEnd.Middle( 4, 2 ).ToInteger
dtLnchEnd.Second = strLncEnd.Right( 2 ).ToInteger

// Calculate shift hours…
dblLnchHrs = dtLnchEnd.TotalSeconds - dtLnchStrt.TotalSeconds
dblLnchHrs = (dblLnchHrs / 60) / 60
strLnchHrs = dblLnchHrs.ToString

If( dblLnchHrs >= 30 ) Then
MessageBox( "Total Lunch Hours: " + dblLnchHrs.ToString )
Return strLnchHrs
Else
MessageBox( “In Accordance with California State Law, you are required to take exactly 30 minutes for your lunch break. During this time you are not paid as is it your time.” )
Return strLnchHrs
End If[/code]

I am expecting .30 or 30 in return… and no… I get this instead --> “0002777777777778”
The shift time calculations is working perfectly… but the Lunch calcs are not returning as expected. What am I doing wrong

MarkusR · June 22, 2020, 7:56pm

i don’t see your input of strLncStrt & strLncEnd

thant is wrong you compare hours with minutes

use a break point and see debugger values and step through

try this to make a double to string

Var n2 As Double = 12 Var s2 As String = n.ToString(Locale.Current, "#.00") // s2 = 12.00

Thom_McGrath · June 22, 2020, 7:56pm

Half is 0.5, not 0.3. It’s not immediately obvious why you’d get 0002777777777778 returned from anything though.