I'm dealing with a sheet that has a lot of time values in the cells. I need to match up times to a specific 30 second interval. So I need to round times so that they fall on :00 or :30 second intervals, then other cells need lookup these cells and match them exactly.
Essentially, I want to scale-up the decimal value of the time to an integer, I tried Mround() and I can't seem to get it to do it correctly.
The problem is that one cell might be, say 8:30:30 AM, which may have a value of 0.354513888888889
Another cell might get the 8:30:30 AM time from another source and it might happen to have a value of:
Becaus of the 3rd part solver program that's operating on these cells the values *must* be numeric, so I can't just truncate using Left(), or do a Text() or read the formatted value or anything like that--I need to deal with the underlying decimal value, which is why I'm thinking if I scaled everything prior to loading it to the sheet I could avoid this.
I'm thinking I need to scale the numbers up but any scaling *must* take place in an excel forumula--not a user-defined function. I tried the Fix(), Clng() functions that I'm used to in VB, but these don't seem to work in formulas, just in the vba environment.
So am I possibly barking up the wrong tree here? I'm sure others have come across similar needs and I'm wondering if there's an elegant solution to this. Maybe I just need help with the Mround() function, I'm not sure.
Thanks for any advice,