VB roundup to nearest integer isn't exact

[P0lar]

I have some times and need to know how many minutes there are to a 15 minute interval in vb. here's my code:

stVal = (WorksheetFunction.RoundUp(CDate(tmFirst) * 96, 0)) - (CDate(tmFirst) * 96)

where tmFirst is the time as a string, e.g. "07:00"

the trouble is that although

 (WorksheetFunction.RoundUp(CDate(tmFirst) * 96, 0)) = 28

and

 (CDate(tmFirst) * 96) = 28

my full formula is returning 28 - 28 = -1.776E-15 instead of 28 - 28 = 0

 

[Chihiro]

Acutally, your issue is with CDate(tmFirst).

Since Excel uses decimal value to store time (and not true fraction)...
CDate(tmFirst)*96 has small decimal value in it.

Try doing something like...

stVal = (WorksheetFunction.RoundUp(CDate(tmFirst) * 96, 0)) - INT(CDate(tmFirst) * 96)

Or...

stVal = Round(WorksheetFunction.RoundUp(CDate(tmFirst) * 96, 0) - CDate(tmFirst) * 96, 12)

Though I'm not sure if I understood your requirement.

 

[NARAYANK991]

Hi ,

You can use TimeValue(tmFirst).

Narayan

 

[vletm]

P0lar ...You wrote:
I have some times and need to know how many minutes there are to a 15 minute interval
Why do You use RoundUp?
even RoundDown would work more correct way...
but I would use INT.

 

[P0lar]

Thanks for the help, I managed to get things working to the nearest minute (which should be good enough) using

(15 - Right(tmFirst, 2) * 1 Mod 15) / 15

But I'll try a version with your suggestions as they will still work if there are any entries passed through with seconds included.