Half-hour integer formula

[DE_Tx]

I have tried searching multiple combinations for "hour" and "half hour" but have yet to find the answer for my question. If anyone has a quick link or an answer please point me in the right direction.

I know Excel has the HOUR function that returns the hour of the day for a time value.
Example: 09/05/2017 11:18 PM (US format) will return an HOUR of 23.
I need a formula that will return the half-hour integer (i.e. 46).
09/05/2017 11:35 PM would return a value of 47.

Thanks for any assistance you may offer.

DE_TX

 

[shrivallabha]

Possibly something like below then:
=INT((A1-INT(A1))*48)

 

[DE_Tx]

I thought of using the INT function but was unsuccessful. You came much closer than I did but we are still not quite there.
I've attached a sample file with your formula.
Some intervals are correct, others are not.
The quick/easy solution may just be to build a map, but where's the fun in that.

DE_TX

 

[bosco_yip]

Maybe,

=ROUND((A2-INT(A2))*48,0)

or,

=ROUND(MOD(A2,1)*48,0)

Regards
Bosco

 

[DE_Tx]

Thanks, Bosco. I think you may have supplied the winning ticket.
Although, I don't think the ROUND is necessary.
I broke your formula down into the two pieces and it worked perfectly.
I've attached a sample file.
Mark this one as SOLVED!
Thank you both.

DE_TX

 

[bosco_yip]

Thanks, Bosco. I think you may have supplied the winning ticket.
Although, I don't think the ROUND is necessary.
I broke your formula down into the two pieces and it worked perfectly.
I've attached a sample file.
Mark this one as SOLVED!
Thank you both.

DE_TX

Try to test "Formula without ROUND" and check result.

In J2, formula copied down :

=C2=I2

Regards
Bosco

 

[shrivallabha]

Sorry, I did not test my formula fully.

However, the formula posted by Bosco or you doesn't give expected result for entry "09/05/2017 11:18 PM" mentioned in post #1 and it will fail in every even quarter i.e. 16-29, 46-59 minutes. Have you checked it?

My revised suggestion would be:
=HOUR(A2)*2+INT(MINUTE(A2)/30)

 

[shrivallabha]

I thought of using the INT function but was unsuccessful. You came much closer than I did but we are still not quite there.
I've attached a sample file with your formula.
Some intervals are correct, others are not.
The quick/easy solution may just be to build a map, but where's the fun in that.

DE_TX

I have spent some time on the original formula after finding that the formula didn't work for you.

Just curious, I worked out the results for complete 24 hours using formula at post#2 and #7 respectively i.e. for all 1440 possibilities (24 X 60). I am attaching the workbook for reference.

What I found was rather interesting. Formula posted at #2 fails in 16 cases and works otherwise wrt post #7. Ideally it should've failed at 24 places . The erroneous results are caused by what is called "Floating point error" in excel.
https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel

It was good that you spotted the erroneous results.

PS: For a change, I can blame Excel for wrong results