Trying To Find 2 Formulas Based On Time Of Day

[mrscott]

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.

 

[mrscott]

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

 

[mrscott]

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

 

[Logic]

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.

 

[RobertSmart]

Yes, it's definitely possible to achieve this with some math. Here's how you can do it:

To convert time (0:00 - 23:59) to an integer (1-96):

First, convert the time to minutes.

Then, divide the total minutes by 15.

Round up the result to the nearest whole number.

Formula:

Integer= [(Hours×60+Minutes)/15]

To convert an integer (1-96) back to a time:

Multiply the integer by 15 to get the total minutes.

Divide the total minutes by 60 to get the hours (integer division).

The remainder will be the minutes.

Formula:

Hours= [Integer×15/60]

Minutes=Integer×15mod 60

Implementing these formulas in your computer program should give you the desired results


By the way, if you need further assistance with math assignments or want to explore more problems, you might find useful resources at website of MathsAssignmentHelp.com. You can contact them at +1 (315) 557-6473.