Working hours calculator

[the_black_code]

Dear All,

I am currentley working on a function that can calculate the deferance between two gevin times and return the result . My code is as follow:

Public Function TimeDiffCal(ByVal StartingTime As Date, ByVal FinishingTime As Date) As Date
    Dim BTime As Variant, ETime As Variant
    Dim Hours As Integer, Minutes As Integer, Seconds As Integer
    BTime = Split(CStr(StartingTime), ":")
    ETime = Split(CStr(FinishingTime), ":")
    
    Hours = Abs(ETime(0) - BTime(0))
    Minutes = Abs(ETime(1) - BTime(1))
    Seconds = Abs(ETime(2) - BTime(2))
    TimeDiffCal = CDate(Hours & ":" & Minutes & ":" & Seconds)
End Function

But when I entered the time values (IN: 11:45:00) and (OUT: 17:00:00) it returnes 06:45:00 ...

Please could anyone help me with this and I would be appreciated.

Thanks.


A

 

[Galaxiom]

Access already does this with the DateDiff function or by simple subtraction.

 

[the_black_code]

Hi GalaxiomAtHome,

Thanks for your reply, but I did try the DateDiff function, and it gives a wrong values... for example:
for (IN: 11:45:00) and (OUT: 17:00:00) example:

Hours = DateDiff("h","11:45:00","17:00:00") 'YES, It returns 6
Minutes = DateDiff("n","11:45:00","17:00:00") 'It returns 315 !!!!!!!!
Seconds = DateDiff("s","11:45:00","17:00:00") 'It returns 0, which is OK.

Thanks.

A

 

[Galaxiom]

You are entering dates as strings.
Literal Date/Time values are entered in the format:

#mm/dd/yyyy hh:nn:ss#

 

[the_black_code]

Hi GalaxiomAtHome,

Thanks for your reply, here is the code directley from the Immediate window in the Access VB Edittor:

?DateDiff("h","08/04/2011 11:45:00","08/04/2011 17:00:00")
 6 
[B][COLOR=red]?DateDiff("n","08/04/2011 11:45:00","08/04/2011 17:00:00")
 315[/COLOR] 
[/B]?DateDiff("s","08/04/2011 11:45:00","08/04/2011 17:00:00")
 18900

Do you have a solution for this problem?

Thanks.

A

 

[Galaxiom]

DateDiff("n", #08/04/2011 11:45:00#, #08/04/2011 17:00:00#)

 

[Galaxiom]

If you need to convert a Date field or control use this:

Format([fieldname], "\#mm\/dd\/yyyy hh\:nn\:ss\#")

 

[the_black_code]

DateDiff("n", #08/04/2011 11:45:00#, #08/04/2011 17:00:00#)

It returns:

?DateDiff("n", #08/04/2011 11:45:00#, #08/04/2011 17:00:00#)
315

 

[the_black_code]

If you need to convert a Date field or control use this:

Format([fieldname], "\#mm\/dd\/yyyy hh\:nn\:ss\#")

Hi GalaxiomAtHome,

Is it OK to use

CDate([fieldname])

Thanks.

A

 

[Galaxiom]

If the date is a field or control you can just refer to it directly. No function required.

The format expression I mentioned is only used when you need to put the code into sql strings. Sorry I was getting ahead of myself with that one.

 

[Galaxiom]

It returns:

?DateDiff("n", #08/04/2011 11:45:00#, #08/04/2011 17:00:00#)
315

315 minutes is the correct answer. 5 hours 15 minutes = 315 minutes.

Apparently it understands the string and does an implicit datatype conversion anyway. Learn something every day here.

If you want hours, minutes and seconds you can subtract them arithmetically. If you get a decimal answer apply CDate to it.

 

[the_black_code]

Hi GalaxiomAtHome,

Many thanks for helping me with this, but I want it to be as a time calcuation function to be used in other place in the code.

Do you have a working replacement to the above function?

Thanks and I realy appreciating your help.

A

 

[the_black_code]

315 minutes is the correct answer. 5 hours 15 minutes = 315 minutes.

Apparently it understands the string and does an implicit datatype conversion anyway. Learn something every day here.

If you want hours, minutes and seconds you can subtract them arithmetically. If you get a decimal answer apply CDate to it.

Could you please tell me how to do the arithmetical subtraction?

Thanks.

A

 

[Galaxiom]

This should work

Public Function TimeDiffCal(ByVal StartingTime As Date, ByVal FinishingTime As Date) As Date
 
   TimeDiffCal = EndingTime - StartingTime
 
End Function

 

[Galaxiom]

Date/Time is actually stored as a four byte real number. The integer part is the number of days since 30 December 1899 while the time is the decimal part of a day. The value is just formatted for display to the local date format.

Consequently they can be simply added and subtracted.

The Date section is suppressed if the date is that day so it appears as time only. Access cannot actually display the date 30/12/1899 in a date field or control.

 

[the_black_code]

This should work

Public Function TimeDiffCal(ByVal StartingTime As Date, ByVal FinishingTime As Date) As Date
 
   TimeDiffCal = EndingTime - StartingTime
 
End Function

Hi GalaxiomAtHome,

No, again it gives a wrong answer. Look what it gives when I tried it:

?TimeDiffCal1("11:45:00","17:00:00")
11:45:00

 

[the_black_code]

Date/Time is actually stored as a four byte real number. The integer part is the number of days since 30 December 1899 while the time is the decimal part of a day. The value is just formatted for display to the local date format.

Consequently they can be simply added and subtracted.

The Date section is suppressed if the date is that day so it appears as time only. Access cannot actually display the date 30/12/1899 in a date field or control.

Hi GalaxiomAtHome,

I know all of that, but (in code) can you solve this?

Thanks.

A

 

[Galaxiom]

Sorry. It is late here in Oz.

Change EndingTime to FinishingTime in the calculation.

It is a good reason to always use Option Explicit and declare variables. Saves silly mistakes like that.

 

[the_black_code]

Hi,

I solved the problem . Here is the solution:

Public Function TimeDiffCal(ByVal StartingTime As Date, ByVal FinishingTime As Date) As Date
    Dim Hours As Integer, Minutes As Integer, Seconds As Integer, TotalSeconds As Integer
 
    TotalSeconds = DateDiff("s", InTime, OutTime, vbMonday, vbFirstJan1)
 
    If TotalSeconds < 3600 Then
        Hours = 0
        If TotalSeconds < 60 Then
            Seconds = TotalSeconds
        Else
            Minutes = Int(TotalSeconds / 60)
            Seconds = TotalSeconds - (Hours * 3600) - (Minutes * 60)
        End If
    Else
        Hours = Int(TotalSeconds / 3600)
        If TotalSeconds < 60 Then
            Seconds = TotalSeconds
        Else
            Minutes = Int((TotalSeconds - Hours * 3600) / 60)
            Seconds = TotalSeconds - (Hours * 3600) - (Minutes * 60)
        End If
    End If
 
    TimeDiffCal = CDate(Hours & ":" & Minutes & ":" & Seconds)
End Function

Thanks for everyone who helped me in this.

Regards,

A