The best way to specify time interval ...

[nicgendron]

Hi,

I'm writing and Xll Add-In for Excel.

I want to add a function that gives a result base on three parameters
- start time
- end time
- time interval

for start time and end time I project to use the same representation as
in excel.

But what for interval I don't know what to do.

For exemple :

How to specify :
at each 2hours 23 minutes
at each month
at each ....

Any idea are welcomed !

Nic

 

[K Dales]

This may give you some ideas - you may need to do some other function but
this shows you you can specify different time intervals to use in
calculations. This function would rely on the user to choose the appropriate
unit for their interval, e.g. they could not specify an interval of 02:23,
but 2 hours 23 minutes would be 143 minutes. The function returns the number
of specified time intervals between the start and end times; time intervals
are specified with an integer number of units, the units are specified as
seconds, minutes, hours, etc as you can see below - so, for example,
MyTimeFunction(Now(), Now+TimeValue("01:20:00"), 10, tuMinutes) would return
8 meaning there are 8 periods of 10 minutes in the 1:20 elapsed time.

Public Enum TimeUnitEnum
tuSeconds = 1
tuMinutes = 2
tuHours = 3
tuDays = 4
tuWeeks = 5
tuMonths = 6
tuQuarters = 7
tuYears = 8
End Enum

Public Function MyTimeFunction(StartTime As Date, EndTime As Date,
IntervalTime As Integer, IntervalUnits As TimeUnitEnum) As Double
Dim TimeInterval As Date

Select Case IntervalUnits
Case tuSeconds
TimeInterval = TimeValue("00:00:01") * IntervalTime
Case tuMinutes
TimeInterval = TimeValue("00:01:00") * IntervalTime
Case tuHours
TimeInterval = TimeValue("01:00:00") * IntervalTime
Case tuDays
TimeInterval = IntervalTime
Case tuWeeks
TimeInterval = IntervalTime * 7
Etc....

End Select

MyTimeFunction = (EndTime - StartTime) / TimeInterval

End Function