CODEWITHSUNDEEP
stringexceltimeexcel-formulaworksheet-function
tohobaby
tohobaby

Reputation: 9

Find the difference between two times and multiply that by a value

I wish to calculate the difference between two times multiplied by a value in another column. My columns are like this:

       Column A                Column B             Column C
1    09:00 - 21:00                 2

I want my Column C to return 24, since the time difference in Column A is 12 hours, and the multiplier in Column B is 2.

Upvotes: 1

Views: 209

Answers (2)

Byron Wall
Byron Wall

Reputation: 4010

Here is another approach which uses TIMEVALUE() in case your formatting around the hyphen includes whitespace.

=(TIMEVALUE(RIGHT(A1,LEN(A1)-FIND("-",A1)))-TIMEVALUE(LEFT(A1,FIND("-",A1)-1)))*24*B1

As stated in the comment up above, you will do better to have these in separate columns, but these formulas work nonetheless.

If you want to subtract times that are split across midnight (00:00), you can force clock arithmetic using the MOD(XXX, 24) formula. This will prevent negatives. It also means that the difference can never be greater than 24 (before multiplying by column B).

=MOD((TIMEVALUE(RIGHT(A1,LEN(A1)-FIND("-",A1)))-TIMEVALUE(LEFT(A1,FIND("-",A1)-1)))*24,24)*B1

Upvotes: 0

pnuts
pnuts

Reputation: 59475

Please try:

=B1*24*(MID(A1,FIND("-",A1)+2,LEN(A1))-LEFT(A1,5))

Upvotes: 1

Related Questions