Time Calculation (1 Viewer)

dttate66

Registered User.
Local time
Today, 06:40
Joined
Feb 16, 2018
Messages
36
I am having a problem with my calculation in my query. this is how it reads to give me my answer I need;
Man/Hrs: Nz(TimeDurationAsDate([Start Time],[End Time])*[Staff]*24,"")

but if it is one hour or less it will return a value of this;
"0.999999999999999"

If I use this calculation;
Man/Hrs Formatted: TimeElapsed(Nz(TimeDurationAsDate([Start Time],[End Time])*[Staff],0),"")
then is rounds everything off to the nearest hour. How do I get the topmost calculation to read just "1 hour"??
 

MarkK

bit cruncher
Local time
Today, 04:40
Joined
Mar 17, 2004
Messages
8,178
Hi and welcome to the forum.

We don't know anything about the functions TimeDurationAsDate() or TimeElapsed() that you have cited in the code you posted. What is the code for those?
Code:
Man/Hrs Formatted: [COLOR="Blue"]TimeElapsed([/COLOR]Nz([COLOR="blue"]TimeDurationAsDate([/COLOR][Start Time],[End Time][COLOR="blue"])[/COLOR]*[Staff],0),""[COLOR="blue"])[/COLOR]
Or, if the only problem is that the result is not rounded, you can use the Round() function...
Code:
MsgBox Round(0.999999999999999, 0)
hope this helps,
Mark
 

dttate66

Registered User.
Local time
Today, 06:40
Joined
Feb 16, 2018
Messages
36
This was given to me by another person in this forum, it works great, except when calculating Start Time, for example, I put in 05:00 pm and End Time is 05:40 pm it will show 4.66666666666667 instead of 0:40:00. It only does this when 1 hour or less.

I don't care if it shows 4.66, but all the rest I need to get ride of for invoicing reports.

Please help!
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:40
Joined
May 7, 2009
Messages
19,169
post the code of the 2 functions and well modify it.
 

dttate66

Registered User.
Local time
Today, 06:40
Joined
Feb 16, 2018
Messages
36
In my query this is the first;

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

My second is;
Man/Hrs Formatted: TimeElapsed(Nz(TimeDurationAsDate([Start Time],[End Time])*[Staff],0),"")
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:40
Joined
May 7, 2009
Messages
19,169
post the code for TimeElapsed() and TimeDurationAsDate() functions.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:40
Joined
Feb 28, 2001
Messages
26,996
OK, quickie overview of time computations.

Times are actually typecasts of DOUBLEs that give timeline distance from a reference point. Units are days and fractions thereof. Trust me, a DOUBLE holds all the bits you need to get times accurate to the second and then some. (Like, milliseconds easily.)

If you have EndTime - StartTime, the difference is days and fractions between the two. You can take the difference first with a

Code:
DeltaT = NZ([EndTime] - [StartTime], 0)

So if you take the difference first, you can convert it simply.


Code:
Dim DeltaT As Double, DeltaTDays As Long, DeltaTHMS As Date, DTFormatted As String

...

DeltaT = NZ([EndTime] - [StartTime], 0)
DeltaTDays = CLng( DeltaT )
DeltaTHMS = DeltaT - CDbl( DeltaTDays )

...

To get elapsed days, hours, and minutes, you should be able to use

Code:
DTFormatted = CStr( DeltaTDays ) & "-" & Format( DeltaTHMS, "hh:nn" )
 

dttate66

Registered User.
Local time
Today, 06:40
Joined
Feb 16, 2018
Messages
36
And this goes into my query?, I don't see how it fits into the cell.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:40
Joined
Feb 28, 2001
Messages
26,996
Ah, you want a single query to do difference AND formatting at the same time?

Rather than reinvent the wheel, I now join with the other who have asked you to post the code of those functions you were trying to use.
 

dttate66

Registered User.
Local time
Today, 06:40
Joined
Feb 16, 2018
Messages
36
Ah, you want a single query to do difference AND formatting at the same time?

Rather than reinvent the wheel, I now join with the other who have asked you to post the code of those functions you were trying to use.

This is the functions, they are highlighted black with white lettering;
Query.png

And this one when ran shows the result, also highlighted.
Query-2.jpg
 

isladogs

MVP / VIP
Local time
Today, 11:40
Joined
Jan 14, 2017
Messages
18,186
Please copy and paste the code from the query
 

dttate66

Registered User.
Local time
Today, 06:40
Joined
Feb 16, 2018
Messages
36
In my query this is the first;

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

My second is;
Man/Hrs Formatted: TimeElapsed(Nz(TimeDurationAsDate([Start Time],[End Time])*[Staff],0),"")
 

MarkK

bit cruncher
Local time
Today, 04:40
Joined
Mar 17, 2004
Messages
8,178
What is this part in purple? What does it do?
Code:
Man/Hrs: Nz([COLOR="Purple"]TimeDurationAsDate[/COLOR]([Start Time],[End Time])*[Staff]*24,"")
We don't know what that purple thing is, except we know it is not a built-in function. What does it do? Why is it there? You need to tell us. We can't explain it to you. It calls a function in YOUR code. We need to see that code.
hth
Mark
 

dttate66

Registered User.
Local time
Today, 06:40
Joined
Feb 16, 2018
Messages
36
That is the code, another user from another site gave that to me, told me to just drop it in the query, and it worked, except it gives me that funky number for anything less than an hour.
 

Minty

AWF VIP
Local time
Today, 11:40
Joined
Jul 26, 2013
Messages
10,354
As everybody has already asked - I'll have a go as well.

WHAT IS THE CODE from the other user? That is what we need you to post up. We know you are using it , but we don't know what it is.

Somewhere in a module in your database is a Public Function TimeDurationAsDate()

Find that and copy and paste it here for all to see.
 

dttate66

Registered User.
Local time
Today, 06:40
Joined
Feb 16, 2018
Messages
36
Oh, ok. I see what you are saying now, I did not realize he had made a module and dumped it in to my database, my apologies, here it is;

Code:
' 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
 
Last edited by a moderator:

boerbende

Ben
Local time
Today, 12:40
Joined
Feb 10, 2013
Messages
339
to find the code
- right click on the function and select: definition

then the cursor jumps to the code everybody is referring to
 

dttate66

Registered User.
Local time
Today, 06:40
Joined
Feb 16, 2018
Messages
36
to find the code
- right click on the function and select: definition

then the cursor jumps to the code everybody is referring to

The Code they were referring to, I found in my modules. It is listed above your reply.
 

dttate66

Registered User.
Local time
Today, 06:40
Joined
Feb 16, 2018
Messages
36
As everybody has already asked - I'll have a go as well.

WHAT IS THE CODE from the other user? That is what we need you to post up. We know you are using it , but we don't know what it is.

Somewhere in a module in your database is a Public Function TimeDurationAsDate()

Find that and copy and paste it here for all to see.

Here it is;
' 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
 

dttate66

Registered User.
Local time
Today, 06:40
Joined
Feb 16, 2018
Messages
36
Re: Hi and welcome to the forum.

We don't know anything about the functions TimeDurationAsDate() or TimeElapsed() that you have cited in the code you posted. What is the code for those?
Code:
Man/Hrs Formatted: [COLOR="Blue"]TimeElapsed([/COLOR]Nz([COLOR="blue"]TimeDurationAsDate([/COLOR][Start Time],[End Time][COLOR="blue"])[/COLOR]*[Staff],0),""[COLOR="blue"])[/COLOR]
Or, if the only problem is that the result is not rounded, you can use the Round() function...
Code:
MsgBox Round(0.999999999999999, 0)
hope this helps,
Mark

I see where you added "code tags", but it is still doing the same thing, If I put in "11:00 pm" to "12:00 am" the result is 0.999999999.

What am I doing wrong?
 

Users who are viewing this thread

Top Bottom