Date Difference VBA Code

MHamid

Active Member
Joined
Jan 31, 2013
Messages
385
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I have the below function to calculate the difference between two times (when the user LogsIn and LogsOut). I want to incorporate if Idle time was used to close the program, then deduct 15 minutes. If not, then do nothing.
Is this possible?

Code:
Function DisplayTime(start As Date, finish As Date, fmt As String)
Dim Hours As Long, Minutes As Long, totalSeconds As Long, SecondLeft As Long
'format the differece of 2 dates or time
totalSeconds = DateDiff("s", start, finish)
Hours = Int(totalSeconds / 3600)
SecondLeft = totalSeconds Mod 3600
Minutes = Int(SecondLeft / 60)
Select Case fmt
    ' format = 100 Hours 50 Minutes
    Case "H M"
    If Hours = 0 Then
    DisplayTime = Minutes & IIf(Minutes <> 1, " Minutes", " Minute")
    Else
    DisplayTime = Hours & IIf(Hours <> 1, " Hours", " Hour") & Minutes & IIf(Minutes <> 1, " Minutes", " Minute")
    End If
End Select
End Function

Let me know if you need the idle time code as well.

Thank you
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
If idle time is used then just subtract the extra minutes you want to subtract. You just need to know if to subtract or not - that can be a parameter in your function or something you determine in the body of the function.
 

MHamid

Active Member
Joined
Jan 31, 2013
Messages
385
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I'm sorry, I'm trying to wrap my head around how to go about doing this when the idle time function is used in one form and the DisplayTime function is being used in another form. I will only need to deduct 15 minutes from the DisplayTime in the Report_rptLogInTime when IdleTime was used.

I'm thinking that the minus 15 minutes if IdleTime was triggered should be used in the Report_rptLogInTime since that is the report that will be calculating how long a user has been using the Data Entry Form. Current in the Report_rptLogInTime there is a textbox labeled "Text27" with a formula in the control source (on the property sheet) as =DisplayTime([LogInTime],[LogOutTime],"H M").

But I'm also thinking that based on your comment, I would need to incorporate parts of the IdleTime function as a parameter within the DisplayTime function. Am I misunderstanding?


Thank you
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Not sure I can answer your question. Basically how do you know if idle time is used. Whatever makes that work, you would incorporate it into your calculation (either when it happens or at some point later on as an adjustment).

My own take is probably oversimplified, but I imagine it this way:

1) An idle time trigger fires (i.e., database has been idle for 15 minutes).
2) you immediately record the the current user login time and close forms. Since this is an idle time event you also subtract 15 minutes from the users login time.

The technicalities of how you do this are up to you as a programmer. I would probably use a parameter in my function/query/form that records the time (subtractIdleTime -- yes/no or IdleTimeAdjustment -- time in minutes). You can do it any way you like though.

To be honest I don't really know if you are just displaying some value or storing some value - if it's just a matter of display, however, its more or less the same thing going on.
 
Last edited:

Forum statistics

Threads
1,140,917
Messages
5,703,166
Members
421,279
Latest member
emzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top