# 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.70Whatever I try I get a #VALUEAny help would be greatly appreciated

See More: Mulitplying minus times by an hourly rate

December 26, 2014 at 11:46:55
 PaulYour 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:MMMIKEhttp://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.MIKEhttp://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 minusFor minus I took the value as a text stringremoved this minus signtimes the value by 24times the decimal value by the hourly rateused -1 to make the answer a minus £ amountFor positive number done the normal.Its not the tidiest of formulas, no doubt someone could tidy it up for me, but it worksJust 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 Q5that 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 PMBut Excel sees: 41999.6041666667MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
December 26, 2014 at 11:46:55