Solved Mulitplying minus times by an hourly rate

December 25, 2014 at 04:33:45
Specs: Windows 7
I am working on an Excel holiday accrual spread sheet so if staff take more leave than they have accrued to the end of a set month I get a minus time, I then need to show this as a (£ cash value) any idea's?

Example -02:30 x £9.88 = -£24.70

Whatever I try I get a #VALUE
Any help would be greatly appreciated


See More: Mulitplying minus times by an hourly rate

Report •


✔ Best Answer
December 26, 2014 at 11:46:55
Paul

Your reply was attached to a different thread, and now seems to have disappeared.

But try this formula and see if it offers any advantages:

=IF(I2>H2,MOD(I2-H2,1),H2-I2)

Format the cell as HH:MM

MIKE

http://www.skeptic.com/



#1
December 25, 2014 at 06:25:52
Excel does not like Minus times.

How are calculating the Time, that you arrive at -2:30?

Entering the value -2:30 should give you a #VALUE error.

MIKE

http://www.skeptic.com/


Report •

#2
December 26, 2014 at 05:33:33
Cracked it!!!

I had to research a bit and put together some logic but it works for me

=IFERROR(IF(LEFT(Q5,1)="-",((VALUE(RIGHT(Q5,LEN(Q5)-1))))*24*-R5,(INT(Q5)*24+HOUR(Q5)+ROUND(MINUTE(Q5)/60,2))*R5)," ")

Used an if to determine if the value was plus or minus

For minus I took the value as a text string
removed this minus sign
times the value by 24
times the decimal value by the hourly rate
used -1 to make the answer a minus £ amount

For positive number done the normal.

Its not the tidiest of formulas, no doubt someone could tidy it up for me, but it works

Just proves the only thing excel cant do is give us a bit of imagination.



Report •

#3
December 26, 2014 at 07:15:31
I would still be curious to know how your doing the Time math for Q5
that you arrive at the value of -2:30?

That would seem to be at the root of your problem.

Excel stores Dates and Times as a number
representing the number of days since Jan 0 1900,
plus a fractional portion of a 24 hour day.

So you may see: 12/26/2014 02:30 PM
But Excel sees: 41999.6041666667

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
December 26, 2014 at 11:46:55
✔ Best Answer
Paul

Your reply was attached to a different thread, and now seems to have disappeared.

But try this formula and see if it offers any advantages:

=IF(I2>H2,MOD(I2-H2,1),H2-I2)

Format the cell as HH:MM

MIKE

http://www.skeptic.com/


Report •

Ask Question