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

✔ 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

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

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 £ amountFor 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.

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

Timesas anumber

representing the number of days since Jan 0 1900,

plus afractional portionof a 24 hour day.So you may see: 12/26/2014 02:30 PM

But Excel sees: 41999.6041666667MIKE

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

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History