Time Calculation (1 Viewer)

MarkK

bit cruncher
Local time
Today, 12:49
Joined
Mar 17, 2004
Messages
8,178
Hi, so I'm not clear where you "put in" 11p to 12a. Also, if a result is generated, how is it generated? Can you use very clear and simple language that describes your steps, something like...
1) I double click the named query in the navigation window
2) the query seems to open, but immediately prompts me for a parameter called ????
3) I type in 23:00 hours and click OK
...that way we can get a very clear understanding of the steps you are taking, and what kind of "result" you are talking about. And now that we understand the function calls you are making there, I think we are getting close to moving ahead.
hth
Mark
 

dttate66

Registered User.
Local time
Today, 14:49
Joined
Feb 16, 2018
Messages
36
Hi, so I'm not clear where you "put in" 11p to 12a. Also, if a result is generated, how is it generated? Can you use very clear and simple language that describes your steps, something like...

...that way we can get a very clear understanding of the steps you are taking, and what kind of "result" you are talking about. And now that we understand the function calls you are making there, I think we are getting close to moving ahead.
hth
Mark

Ok, in the first screen shot, you enter the event details, then click on the event task tab, which is the second screen shot. You fill in each line for the task assigned, when you get to the column "Start Time", you enter the time the employee "Clocked In", the next column is "End Time", which you type in the time the employees' shift ended. The column after that is "Man Hours" that is the hours the employee worked. But when the time is one hour or less it reads like this, "0.999999999999". in the query that gathers the information and displays it, for the "Man Hours" column. The formula looks like this.

Man/Hrs: Nz(TimeDurationAsDate([Start Time],[End Time])*[Staff]*24,"")
 

Attachments

  • Screen Shot - 1.jpg
    Screen Shot - 1.jpg
    89.6 KB · Views: 46
  • Screen Shot - 2.jpg
    Screen Shot - 2.jpg
    98.5 KB · Views: 44
Last edited:

JHB

Have been here a while
Local time
Today, 20:49
Joined
Jun 17, 2012
Messages
7,732
Why do you have that in,(what should that suppose to do)?
*[Staff]*24
 
Last edited:

MarkK

bit cruncher
Local time
Today, 12:49
Joined
Mar 17, 2004
Messages
8,178
What are the exact values for [StartTime], [EndTime] and [Staff] for one of the rows that fails.

And you can't do this?...
Code:
Man/Hrs: Round(Nz(TimeDurationAsDate([Start Time],[End Time])*[Staff]*24, 0), 2)
Mark
 

dttate66

Registered User.
Local time
Today, 14:49
Joined
Feb 16, 2018
Messages
36
Thanks Mark!, that did the trick. Thank you for being patient and explaining exactly in, layman terms, what you need from me to help.

David
 

MarkK

bit cruncher
Local time
Today, 12:49
Joined
Mar 17, 2004
Messages
8,178
You bet David, way to go sticking with it, and thanks to everyone who contributed. Persistence is THE underlying skill required to solve this kind of thing.
Cheers all,
Mark
 

dttate66

Registered User.
Local time
Today, 14:49
Joined
Feb 16, 2018
Messages
36
You bet David, way to go sticking with it, and thanks to everyone who contributed. Persistence is THE underlying skill required to solve this kind of thing.
Cheers all,
Mark

Hey Mark,

I Don't know if you remember me, but you helped me with this equation;

Man/Hrs: Round(Nz(TimeDurationAsDate([Start Time],[End Time])*[Staff]*24,0),4)

But the one thing is if all the fields are not filled in it returns a value of "#Error" and I cannot run any reports. I am using the "Nz()" command correcttly??

Thanks,
David
 

MarkK

bit cruncher
Local time
Today, 12:49
Joined
Mar 17, 2004
Messages
8,178
Yeah, I remember. The question back to you is: what do you want to have happen when the data is incomplete? What should the 'right' answer be then? I can't tell you that, it's your call. I would be tempted to go back to your system and add the correct data.

Also, looking at this again, the Nz() function never does anything, because the Function TimeDurationAsDate() always returns a Date. It's impossible for that function to return a null, so that Nz() is useless.

Unless you change the parameters to--or the return type from--the function, you might as well do...
Code:
Man/Hrs: Round(TimeDurationAsDate([Start Time],[End Time])*[Staff]*24),4)

So yeah, to sum up, what do you want to have happen if the data is incomplete?
Mark
 

dttate66

Registered User.
Local time
Today, 14:49
Joined
Feb 16, 2018
Messages
36
The ideal result would be blank space. As it is, when I run a report, with the "#Error" in that place it tells me "Data type mismatch in criteria expression", so a blank would enable to let me run my reports without having to fill in the data.

David
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 19:49
Joined
Jan 14, 2017
Messages
18,186
Have you tried using something like IIf(IsError(MyCodeHere),"",MyCodeHere)?

If that doesn't work, you may want to consider modifying your functions which seem more complex than necessary

Another way of doing this is using the fact that date/time values are stored as numbers (double)
For this to work you must store both date and time in the same field (or concatenate them)

For example:
Code:
Round(24 * (CDbl([EndTime]) - CDbl([StartTime])), 2)
will give you a result such as 8.25 where the interval is 8 hr 15 min

To get that as hh:nn format (8:15 in this case), you could use
Code:
Fix(24 * (CDbl([EndTime]) - CDbl([StartTime]))) & ":" & (60 * 24 * (CDbl([EndTime]) - CDbl([StartTime])) Mod 60)

You would still need to manage the error situation BUT it just might be easier
 
Last edited:

dttate66

Registered User.
Local time
Today, 14:49
Joined
Feb 16, 2018
Messages
36
Thanks Colin, but my formula works for what I need it to do, I just need to have it return a blank value when no data is input in any of the data fields, or only partially filled.
David
 

isladogs

MVP / VIP
Local time
Today, 19:49
Joined
Jan 14, 2017
Messages
18,186
Thanks Colin, but my formula works for what I need it to do, I just need to have it return a blank value when no data is input in any of the data fields, or only partially filled.
David

Yes I realise that - so have you tried something like IsError?
 

dttate66

Registered User.
Local time
Today, 14:49
Joined
Feb 16, 2018
Messages
36
No...yes. I have tried putting it in, but I do not think I am doing it correctly, it is still giving me trouble in the query. Here is what I tried

Man/Hrs: IIf(Not IsNull(Round(Nz(TimeDurationAsDate([Start Time],[End Time])*[Staff]*24)))
 

isladogs

MVP / VIP
Local time
Today, 19:49
Joined
Jan 14, 2017
Messages
18,186
IIf functions need a true part AND a false part

Difficult to follow what your attempt is trying to do without going through all the earlier posts

Suggest you go back to your original function TimeDurationAsDate and do the error handling in that
 

Mark_

Longboard on the internet
Local time
Today, 12:49
Joined
Sep 12, 2017
Messages
2,111
dttate66

To get a blank you would need to rewrite not only the function but change what it returns. As is it handles, poorly, being passed anything other than exactly what it expects.

The function should handle what to return if it is not passed start or end dates. It should also check better if it is passed an end date less than the start date, not just assume it can do -1 and hope they were both times.

In reality you should not let it get to the function if you don't have both a good start and end for it.

As a way to fix this issue so it will return 0 rather than an error, you may wish to try the following;

Code:
Public Function TimeDurationAsDate( pvFrom As Variant, pvTo As Variant) As Date

Dim afError as integer

afError = 0
if  nz(pvFrom,0) = 0 then
   afError = 1
End If
           
if  nz(pvTo,0) = 0 then
   afError = 1
End If

if IsDate(pvFrom) = False then
   afError = 1
End If

if IsDate(pvTo) = False then
   afError = 1
End If

If afError = 1
   TimeDurationAsDate = 0
Else
    If pvTo < pvFrom Then
        If Int(pvFrom) + Int(pvTo) = 0 Then
            pvTo = pvTo + 1
        End If
    End If
    TimeDurationAsDate = pvTo - pvFrom
End If

This way if you don't pass dates you will get 0 returned instead of an error.

NOTE: In the function I am looking for variants instead of dates. This means if you pass a string formatted as a date/time it will work.
 

dttate66

Registered User.
Local time
Today, 14:49
Joined
Feb 16, 2018
Messages
36
Mark,
This looks good, but am I putting it in my module in place of the old function, for example, here is the whole code for making my time work;

' basDateTimeStuff
Option Compare Database
Option Explicit

Public Function WeekStart(intStartDay As Integer, Optional varDate As Variant)

' Returns 'week starting' date for any date

' Arguments:
' 1. intStartDay - weekday on which week starts, 1-7 (Sun - Sat)
' 2. vardate - optional date value for which week starting
' date to be returned. Defaults to current date

If IsMissing(varDate) Then varDate = VBA.Date

If Not IsNull(varDate) Then
WeekStart = DateValue(varDate) - Weekday(varDate, intStartDay) + 1
End If

End Function

Public Function TimeToString(dtmTime As Date, _
Optional blnShowdays As Boolean = False) As String

Dim lngDays As Long
Dim strDays As String
Dim strHours As String

' get whole days
lngDays = Int(dtmTime)
strDays = CStr(lngDays)
' get hours
strHours = Format(dtmTime, "hh")

If blnShowdays Then
TimeToString = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
Else
TimeToString = Format((Val(strDays) * 24) + Val(strHours), "00") & _
Format(dtmTime, ":nn:ss")
End If

End Function

Public Function TimeElapsed(dtmTime As Date, strMinSec As String, _
Optional blnShowdays As Boolean = False) As String

' Returns a date/time value as a duration
' in hours etc or days:hours etc if optional
' blnShowDays argument is True.
' Time format is determined by strMinSec argument,
' e.g. "nn" to show hours:minutes,
' "nn:ss" to show hours:minutes:seconds,
' "" to show hours only
' Call the fucntion, in a query for example, like this:
' SELECT EmployeeID,
' TimeElapsed(SUM(TimeDurationAsDate(TimeStart, TimeEnd)), "nn") As TotalTime
' FROM TimeLog
' GROUP BY EmployeeID;

Dim lngDays As Long
Dim strDays As String
Dim strHours As String

' get whole days
lngDays = Int(dtmTime)
strDays = CStr(lngDays)
' get hours
strHours = Format(dtmTime, "hh")

If blnShowdays Then
TimeElapsed = lngDays & ":" & strHours & Format(dtmTime, ":" & strMinSec)
Else
TimeElapsed = Format((Val(strDays) * 24) + Val(strHours), "#,##0") & _
Format(dtmTime, ":" & strMinSec)
End If

' remove trailing colon if necessary
If Right(TimeElapsed, 1) = ":" Then
TimeElapsed = Left(TimeElapsed, Len(TimeElapsed) - 1)
End If

End Function

Public Function TimeDurationAsDate(dtmFrom As Date, dtmTo As Date) As Date

' Returns duration between two date/time values
' as a date/time value

' If 'time values' only passed into function and
' 'from' time if later than 'to' time, assumed that
' this relates to a 'shift' spanning midnight and one day
' is therefore subtracted from the 'from' time

' subtract one day from 'from' time if later than 'to' time
If dtmTo < dtmFrom Then
If Int(dtmFrom) + Int(dtmTo) = 0 Then
dtmFrom = dtmFrom - 1
End If
End If

' get duration as date time data type
TimeDurationAsDate = dtmTo - dtmFrom

End Function


Public Function TimeDuration(dtmFrom As Date, dtmTo As Date, _
Optional blnShowdays As Boolean = False) As String

' Returns duration between two date/time values
' in format hh:nn:ss, or d:hh:nn:ss if optional
' blnShowDays argument is True.

' If 'time values' only passed into function and
' 'from' time is later than or equal to 'to' time, assumed that
' this relates to a 'shift' spanning midnight and one day
' is therefore subtracted from 'from' time

Dim dtmTime As Date
Dim lngDays As Long
Dim strDays As String
Dim strHours As String

' subtract one day from 'from' time if later than or same as 'to' time
If dtmTo <= dtmFrom Then
If Int(dtmFrom) + Int(dtmTo) = 0 Then
dtmFrom = dtmFrom - 1
End If
End If

' get duration as date time data type
dtmTime = dtmTo - dtmFrom

' get whole days
lngDays = Int(dtmTime)
strDays = CStr(lngDays)
' get hours
strHours = Format(dtmTime, "hh")

If blnShowdays Then
TimeDuration = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
Else
TimeDuration = Format((Val(strDays) * 24) + Val(strHours), "#,##0") & _
Format(dtmTime, ":nn:ss")
End If

End Function

With the purple colored text is at?
 

isladogs

MVP / VIP
Local time
Today, 19:49
Joined
Jan 14, 2017
Messages
18,186
In case you want to look at how I handled the errors caused by empty datetime fields using my much simpler functions, have a look at the attached example:



I've also used variants and zeroes as Mark has done
 

Attachments

  • Capture.PNG
    Capture.PNG
    16.3 KB · Views: 173
  • TimeElapsed.zip
    24.6 KB · Views: 46

dttate66

Registered User.
Local time
Today, 14:49
Joined
Feb 16, 2018
Messages
36
That looks good, but I have to keep the dates separate from the times. It is first entered as a proposal, then at the event,I double click on the Start Time when the employee shows up for work and starts their post. sometime during that shift, I need to run a report that shows what employees where scheduled for what times and who actually showed up, but with the time having #Error in the field, it will not run the report.
 

Mark_

Longboard on the internet
Local time
Today, 12:49
Joined
Sep 12, 2017
Messages
2,111
dttate66,
Correct, you would be replacing it. The original code doesn't anything to avoid errors or to make sure bad data is handled in a reasonable way.
 

Mark_

Longboard on the internet
Local time
Today, 12:49
Joined
Sep 12, 2017
Messages
2,111
Colin,

For the OPs purposes, I figure returning 0 would work best. That way in his report he can test for 0 to specify "Currently on shift" or what have you.
 

Users who are viewing this thread

Top Bottom