Home Attributing values depending of another value w/ VBA ? Excel 2010
Reply: 1

Attributing values depending of another value w/ VBA ? Excel 2010

Shyning Published in 2017-12-07 13:08:51Z

First of all, sorry if it already exists but I can't find the words in English to describe rapidly my question, hence I don't know how to search for it. Skip to third paragraph if you only need the question.

I'm using Excel to build a complete document about my work hours (arrived at, finished at, if I've been late and of how long was I late, overtime, etc etc). I'm using VBA to smooth this task.

My workplace cuts everything into quarter of hours, which means that if you're late by one minute, you'll lose 15 minutes. If you work overtime for 14 minutes, it won't be seen as overtime. If I point at 07:45 (I'm using 24H format, not AM/PM) I'm fine. If I do it at 07:46 it'll be counted as 08:00. Same with 16:14, it'll show up as 16:00 (details are shown but on the corrected sheet used for salary, it'll show this way). If I do it at 16:15 I've confirmed my 15 minutes of overtime.

So while I could do some workarounds, what I've got in mind is shitcode, really. I'd like to know a good (clean) way to do this : I want for every timestamps I input to be cut in degressive quarter of hours (for instance, I want 17:12 to show up as 17:00 or 08:57 as 08:45).

Thanks for your help, if you didn't understand something or doubt about something I've said, it's probably because of the way I explained it, please ask whatever's needed :)

Update 01: Values are indeed Time processed by Excel, not Strings looking like a timestamp.

Update 02: Solved by @FunThomas with TimeSerial-command and some explanations. Thanks, I hope this can be of help for future reference, however I lack the words to write a better title for this problem.

FunThomas Reply to 2017-12-07 15:23:49Z

Assuming that you data is really time (and not a string that looks like time) and is stored in Cell A1, you can use a formula like this:

=TIME(HOUR(A1), INT(MINUTE(A1)/15) * 15, 0)

The time-command gets three paramater (hour, minute, second).
The hour-part is simple.
For the minute, we make an integer division by 15 (resulting in values from 0..3) and multiply it back with 15 (to get 0, 15, 30 or 45).
The second is simply set to 0.

Update: As VBA-function:

Function roundTime(t As Date) as date
    roundTime = TimeSerial(Hour(t), Int(Minute(t) / 15) * 15, 0)
End Function
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.300157 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO