(Solved) – Calculate Frequency In Excel VBA


I have a start time and an end time and I am capturing that time in Hours. I have the user inputting a target goal, and I want to know how often a basket must be made in order for the target goal to be hit.

For example, the startTime = 08:00 AM and the endTime = 09:00 AM and the targetGoal = 5 simple math logic tells us that between 8 and 9 am is 1 hour or 60 minutes, so to hit the target goal of 5, the user must make at least 1 basket every 12 minutes (=60/5)

I have no issue with this logic, but in my VBA I can’t quite get the logistics of it down. This is what I have so far.

Dim sHour As Double
Dim eHour As Double
Dim targetMake As Double
Dim hitFreq As Double

targetMake = Sheets("Statistics").Range("C2").Value
sHour = Sheets("Statistics").Range("A2").Value
eHour = Sheets("Statistics").Range("B2").Value

hitFreq = (eHour - sHour) / targetMake

and instead of returning 12 as expected when I run this VBA I get 4.166666666

What is off in the calculation or declaration type?

Leave a Reply

Your email address will not be published. Required fields are marked *