Trying To Find 2 Formulas Based On Time Of Day

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.)

Is this possible? I listed all combinations in Excel and then tried to reverse engineer a formula, but I am not seeing it.

Thank you.

 

I was able to figure out this:

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 4 is :45. That works pretty well. I could also use .00, .25, .5 and .75.

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

: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) = 16.25 - Doesn't work. Should be 16.00
16 + 18 / 60 = 16.3 and MROUND(16.3) = 16.25 - This does work
16 + 30 / 60 = 16.5 and MROUND(16.5) = 16.5 - This does work
16 + 51 / 60 = 16.85 and MROUND(16.85) = 16.75 - This does work
16 + 59 / 60 = 16.98333 and MROUND)16.98333) = 17 - Doesn't work. Should be 16.75

 

Typo: "if it returns 1 then time is :00, 2 is :15, 3 is :30 and 4 is :45." should read: "if it returns 1 then time is :00, 2 is :15, 3 is :30 and 0 is :45."

 

Time is zero-based, so in your calculations you want to have the blocknumber zero-based too.
You can than add 1 when you go from time-to-block or subtract 1 when you go from block-to-time.


--------------------------------------------------------------------------------------------------
Formula for time-to-block:
blocknumber = floor( ( (hours x 60) + minutes ) / 15 )+1

First you determine the total amount of minutes (which is (hours x 60) + minutes ) and devide that by 15 and round it down. That gives you the zero-based blocknumber from 0 to 95. Add 1 to get your desired blocknumber of 1 to 96.


--------------------------------------------------------------------------------------------------
Formula for block-to-time:

a) If you want the time in decimal (so hh.00 , hh.25 , hh.50 or hh.75) you can simply do:
time = (blocknumber - 1) / 4

First you make blocknumber zero-based to go from 1 - 96 to 0 - 95.
( this is (blocknumber - 1) )
When you devide that by 4 you will get 0 , 0.25 , 0.5 , 0.75 , 1 , 1.25 , 1.50 , 1.75 , 2 , 2.25 ... 23.75


b) If you want the time in hh:mm format (so hh.00 , hh.15 , hh.30 or hh.45) you can do:
hours = floor( (blocknumber - 1) / 4 )
minutes = ( (blocknumber - 1) modulo 4 ) * 15

Hours is the same calculation as 'time' above but rounded down to only get the hour-part.
Minutes is the zero-based blocknumber modulo 4
0 ,4 , 8 ,12 ,16 ,20 ... 92 mod 4 = 0
1 ,5 , 9 ,13 ,17 ,21 ... 93 mod 4 = 1
2 ,6 ,10 ,14 ,18 ,22 ... 94 mod 4 = 2
3 ,7 ,11 ,15 ,19 ,23 ... 95 mod 4 = 3

Multiply that by 15 and you have your base-minutes.

Good luck.