IF(OR(LEN(A2)<1,LEN(B2)<1),"0m", formula) presents a Duration value (zero minutes) if either A2 or B2 does not contain an entry. IFERROR( formula,"") places a null string in the cell if the formula returns any error message. Thre are two ways of avoiding the error message-add an error trap by wrapping the formula in IFERROR, or preventing Numbers from carrying out the calculation by requiring entries in both A and B before permitting Numbers todo the calculation. In columns D and E, the result of this subtraction, a number, causes an error in DUR2HOURS, which is expecting a Duration value, not a Number. The rows of Column C, where there are no entries in columns A and B return a result of 0 (a number) as Numbers interprets both empty cells as containing the numeric value zero, and returns the result of 0-0=0. ![]() ![]() If you want to go directly to the results in column E, place that formula in C2, fill down from there, and eliminate columns D and E. The formulas in C2, D2 and E2 are independent of each other. 78.6 minutes can be converted to hours by dividing 78. The sidereal hour equals 59 minutes 50.17 seconds the sidereal minute equals 59.8362 seconds the sidereal second equals 0.997 270 second. How to convert decimal minutes to time format. That issue is resolved in column E by wrapping the formula in ROUND, and specifying the precision to one place after the decimal: Like the regular day it is divided into 24 sidereal hours, each sidereal hour being divided into 60 sidereal minutes and each sidereal minute into 60 sidereal seconds. C2 contains the formula below, filled down to the rest of the rows in column C.Ĭolumn D contains the same formula as column C, wrapped in DUR2HOURS:Īs you've noted,for Date & Time values not giving a result tht is a multiple of 6m, DUR2HOURS will returns a value with several places after the decimal. 15 minutes times 1 hour per 60 minutes will make the conversion to hours and minutes will cancel out. Youll need to convert the minutes part to hours. Here's a screen shot of a reproduction of your second example, exhibiting the issue (too many digits) mentioned in your initial post.Ĭolumns A, B and C are the same as your example. Converting from minutes to decimal hours. DUR2HOURS converts a Duration value to a number representing the number of hours in that Duration. Your original choice of DUR2HOURS is the correct one for the calculations you are using. HD's solution, as you've found, will not work with Duration values. You then divid that Duration by a number (60). HD's solution assumes you are recording the number of minutes (a number), a reasonable assumption given the example in your initial post.īut the example in your second post shows you are recording the start and end times (both Date and Time values) then subtracting one from the other, giving a result that is a Duration (formatted to express the result in minutes).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |