EXCEL: multiply a number by hours
Solved
cophol
-
Mohsen -
Mohsen -
Hello,
Can someone help me with multiplying a number by hours
for example:
in A1 I have the number 2
in B1 I have 2:10 (time format [hh]:mm)
and in C1 I would like to get the result in hours from the multiplication of A1 by B1 knowing that the value of A1 can be variable and the value of B1 is always fixed.
thank you for your help.
Can someone help me with multiplying a number by hours
for example:
in A1 I have the number 2
in B1 I have 2:10 (time format [hh]:mm)
and in C1 I would like to get the result in hours from the multiplication of A1 by B1 knowing that the value of A1 can be variable and the value of B1 is always fixed.
thank you for your help.
9 answers
-
To perform operations on hours, they must be converted into hundredths of an hour (ch)..
100ch = 60min
1ch = 0.6min
conversion minute > ch
30min = 30/0.6 = 50ch
42min = 42/0.6 = 70ch
60min = 60/0.6 = 100ch
1st example (2h30 x 3)
convert 2h30 to ch
= 2h + 30min = 200 + (30/0.6) = 250ch
operation:
250 x 3 = 750
convert 750 to hour minute
>> 700ch = 7h
>> 50ch = 50 x 0.6 = 30min
result:
2h30 x 3 = 7h30min
2nd example (3h45 x 3)
convert 3h45 to ch
= 3h + 45min = 300 + (45/0.6) = 375ch
operation:
375 x 3 = 1125
convert 1125 to hour minute
>> 1100ch = 11h
>> 25ch = 25 x 0.6 = 15min
result:
3h45 x 3 = 11h15min
it's up to you to optimize this with formulas in Excel.-
-
1-Create a column, for example C, with the number 1 in all cells (enter 1 in one cell, then copy/select column C/paste)
Format all responses in the same way YES/NO in column B (which leaves a margin in A if needed otherwise directly in A by modifying the formulas below)
Be careful, if the responses YES/NO are not in uppercase, it won't count them, or you need to modify the criteria in quotes of the formula.
In D1 indicate YES
In D2 retrieve the formula: =SUMIF(B:B; "YES"; C:C)
In E1 indicate NO
In E2 retrieve the formula: =SUMIF(B:B; "NO"; C:C)
There you go.
-
