ERLANDSEN DATA CONSULTING Excel & VBA Tips

# Time intervals

If you need to calculate the difference between two time values within a certain limit (e.g. when calculating elapsed work time), the worksheet function below can be useful since it makes it simple to perform the time interval calculation.

```Function TimeInterval(StartTime As Double, EndTime As Double, _
LowerLimit As Double, UpperLimit As Double) As Double
' returns EndTime-StartTime limited by LowerLimit and UpperLimit
TimeInterval = 0
If StartTime > EndTime Then Exit Function
If StartTime > UpperLimit Then Exit Function
If EndTime < LowerLimit Then Exit Function
If StartTime < LowerLimit Then StartTime = LowerLimit
If EndTime > UpperLimit Then EndTime = UpperLimit
TimeInterval = EndTime - StartTime
End Function
```

Example:
The examples below assumes that you have a start time in cell A1 and an end time in cell B1.
=TimeInterval(A1;B1;0;8/24) returns the elapsed time within 00:00 and 08:00 (0 = 00:00)
=TimeInterval(A1;B1;8/24;16/24) returns the elapsed time within 08:00 and 16:00
=TimeInterval(A1;B1;16/24;1) returns the elapsed time within 16:00 and 24:00 (1 = 24:00)

Document last updated 2000-02-04 12:45:22      Printerfriendly version