converting time into amount via overtime pay

[gerard_gonzales33]

Hello there!

May I ask for assistance in solving my excel problem. This is regards to converting time into amount, this is used in computing overtime pay. In the attached file, Overtime is being categorized as follows:
Regular (125%);
ND-Night Diff (135%);
WDO - Working Day-off (130%);
WDO-OT -Working Day off OT (169%);
Spl -Special OT (30%);
Spl Ho OT - Special Holiday OT (169%);
Legal (100%); and
Lgl Ho OT - Legal Holiday OT (260%)

This columns is categorized into Time distribution and amount distribution per Ot category. For the time distribution, I had some problems but due to urgency, my queries will be focusing in converting time ([h] mm) into amount (into single cell). Please see attached file for your reference.

Thank you in advance.

[jason.b75]

Try this in AM5, then use it to fill the table.

Please Login or Register  to view this content.

[gerard_gonzales33]

Hi jason.b75

Thank you for the response. I tried your suggested formula, but it just gave me the result as (-) under AM5. What I did (initially) is I used this kind of formula: = $G5*24*$AK5*1.25 this will give me the result am looking but the down side is that for the cells that had no value, I got this instead "hash tag value" - (sorry my keyboard is malfunctioning can't locate the hash tag symbol). This makes may report confusing.

Hope you got other other idea for this.

Thanks again in advance.

[jason.b75]

The #Value! error that you were seeing would be the result of trying to perform a mathematical equation on a cell with a formula that is not numeric (any blank in G5:Q5, the "" to show a blank is seen as text by any formula looking at it).

You could simply error trap what you already tried, and it should work fine, =IFERROR($G5*24*$AK5*1.25,"") but with that you would need to edit each one. The extra bit that I added gets the percentage from the column headings in AM3:AW3 so that you don't have to change the formulas.

I did note that you have the cells in rows 3 and 4 merged in the sample file, if your actual file doesn't have this then it is possible that the formula is looking for the percentages in the wrong row, you would need to change the reference to AM$3 to allow for this.

I've attached a copy of your sample file with the formula alrady added to it to see if that helps, I've got results showing in AM5, AO6 and AP6.