Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding the result of two times
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. -- Mr.B |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding the result of two times
On Fri, 28 Nov 2008 19:17:01 -0800, Mr.B wrote:
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding the result of two times
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 "Ron Rosenfeld" wrote: On Fri, 28 Nov 2008 19:17:01 -0800, Mr.B wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding the result of two times
On Fri, 28 Nov 2008 19:56:00 -0800, Mr.B wrote:
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 Glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ROUNDING RESULT OF CALCULATION UP OR DOWN | Excel Discussion (Misc queries) | |||
Rounding Error when Calculating similar times | Excel Worksheet Functions | |||
Rounding times to the nearest 15 minutes in Excel | Excel Discussion (Misc queries) | |||
Rounding up the result of an IF function | Excel Worksheet Functions | |||
Rounding up the result | Excel Worksheet Functions |