Resolved Formula To Convert Time To Integer Based On 15 Minute Blocks Of Time. Almost There?

[zunebuggy65]

If you divide a day (24 hours) into blocks of 15 minutes, there are 96 blocks per 24 hour period.

I need a math formula that I can ultimately use in a computer program that will take any time 0:00 - 23:59 and return an integer 1 through 96.

I also need to be able to go the other way and take any integer 1-96 and return the 15 minute block (i.e. 0:45, 10:30, 13:15, 22:00, etc.). This one I have figured out with n Mod 4 (see below).

For 1 to 96 if I use n Mod 4 where n = 1 to 96, if it returns 1 then time is :00, 2 is :15, 3 is :30 and 0 is :45. That works pretty well.
I could also use .00, .25, .5 and .75 to represent :00, :15, :30 and :45.

It is reversing this that I am running into an issue:

For my purposes:
:00 - :14 should = 1st quarter, :15 - :29 should = 2nd quarter, :30 - :44 should = 3rd quarter and :45 - :59 should = 4th quarter.

take for example times: 16:08, 16:18, 16:30, 16:51 and 16:59

16 + 8 / 60 = 16.13333 and MROUND(16.13333,0.25) = 16.25 - Doesn't work. Should be 16.00
16 + 18 / 60 = 16.3 and MROUND(16.3,0.25) = 16.25 - This does work
16 + 30 / 60 = 16.5 and MROUND(16.5,0.25) = 16.5 - This does work
16 + 51 / 60 = 16.85 and MROUND(16.85,0.25) = 16.75 - This does work
16 + 59 / 60 = 16.98333 and MROUND(16.98333,0.25) = 17 - Doesn't work. Should be 16.75

If I get the above working, then I have to somehow convert this to an Integer 1-69.

I guess I need to find the formula that is the reverse of my Mod formula. Is this possible?

Thank you.

 

[JohnH]

This example works with TimeSpan, and shows converting back and forth:

Dim time = Date.Now.TimeOfDay
Debug.WriteLine(time.ToString("hh\:mm")) ' current, for example 14:49

Dim index = time.TotalHours * 60 \ 15 ' (TotalHours = whole and fractional hours)
Debug.WriteLine(index) ' block index, for example 59

time = New TimeSpan(0, index * 15, 0)
Debug.WriteLine(time.ToString("hh\:mm")) 'block start, for example 14:45

 

[zunebuggy65]

Thank you so much. That is perfect. At first I thought the index was off by one, but that's because when I listed the times and integers in Excel, I used 1-96. When I used 0-95 (which I should use) it's perfect.

Thanks again!

 

[cjard]

For

im index = time.TotalHours * 60 \ 15

I think I'd have done TotalMinutes\15

and for the reverse, TimeSpan.FromMinutes(15*index)

Same difference, but slightly more readable IMHO