Rounding the result of two times

[Mr.B]

Hi everyone, I have a problem I can't seem to solve.....Please help.
I have in cell A23 a date and time ---10/19/2008 14:12----
and in cell A24 a date and time----10/20/2008 16:54--- I need this to
calculate the number of hours NOT DAYS and minutes but rond the results to
the nearest half hour........See this is calculating the amount of time that
a product has been out of cold storage so it can't round the time before
calculating.....this is as close as I can come. =TEXT(A24-A23,"[h]:m")
this returns...26:42 and I want it to round to read 26.5
thanks.

 

[Ron Rosenfeld]

Hi everyone, I have a problem I can't seem to solve.....Please help.
I have in cell A23 a date and time ---10/19/2008 14:12----
and in cell A24 a date and time----10/20/2008 16:54--- I need this to
calculate the number of hours NOT DAYS and minutes but rond the results to
the nearest half hour........See this is calculating the amount of time that
a product has been out of cold storage so it can't round the time before
calculating.....this is as close as I can come. =TEXT(A24-A23,"[h]:m")
this returns...26:42 and I want it to round to read 26.5
thanks.

26:42 is the time in hours and minutes.

BUT, 26.5 is time in decimal hours -- so if that's what you really want, you
need to do a conversion.

Excel stores dates and times as decimal days, so to convert to hours you
multiply by 24; then round as desired.

For your problem:

=ROUND((A24-A23)*24*2,0)/2

or

=MROUND((A24-A23)*24,0.5)

--------------------

If you would want the result in hours and minutes, to read 26:30, then:

=TEXT(ROUND((A24-A23)/TIME(0,30,0),0)*TIME(0,30,0),"[h]:mm")

or

=TEXT(MROUND(A24-A23,TIME(0,30,0)),"[h]:mm")

Note that you don't need the TEXT function if you can just format the
containing cell.
--ron

 

[Mr.B]

Thanks a million I've been working on this for 2 days now and you solved it
for me in 10 minutes .....I can't thank you enough
--
Mr.B

Hi everyone, I have a problem I can't seem to solve.....Please help.
I have in cell A23 a date and time ---10/19/2008 14:12----
and in cell A24 a date and time----10/20/2008 16:54--- I need this to
calculate the number of hours NOT DAYS and minutes but rond the results to
the nearest half hour........See this is calculating the amount of time that
a product has been out of cold storage so it can't round the time before
calculating.....this is as close as I can come. =TEXT(A24-A23,"[h]:m")
this returns...26:42 and I want it to round to read 26.5
thanks.

26:42 is the time in hours and minutes.

BUT, 26.5 is time in decimal hours -- so if that's what you really want, you
need to do a conversion.

Excel stores dates and times as decimal days, so to convert to hours you
multiply by 24; then round as desired.

For your problem:

=ROUND((A24-A23)*24*2,0)/2

or

=MROUND((A24-A23)*24,0.5)

--------------------

If you would want the result in hours and minutes, to read 26:30, then:

=TEXT(ROUND((A24-A23)/TIME(0,30,0),0)*TIME(0,30,0),"[h]:mm")

or

=TEXT(MROUND(A24-A23,TIME(0,30,0)),"[h]:mm")

Note that you don't need the TEXT function if you can just format the
containing cell.
--ron

 

[Ron Rosenfeld]

Thanks a million I've been working on this for 2 days now and you solved it
for me in 10 minutes .....I can't thank you enough

Glad to help. Thanks for the feedback.
--ron