Forum Discussion
Solved
Multiplying time (for wage calculations).
Hi.
I’m new to the community (and a bit of an excel amateur). Just joined as I have an issue which I think is quite simple, but to which I can’t find a solution.
Basically I’m trying to add up ho...
Chapman,
It's simpler than you imagine!
This little formula is all you need:
=A2*24*B2
Chapman,
It's simpler than you imagine!
This little formula is all you need:
=A2*24*B2
Your answer works great until the number of hours worked is greater than 24. For example, if you put 25:00 into the time column, it automatically converts to 1:00 and the pay column is reduced to one hour's pay.
I haven't found the proper solution yet.
Mindanser if you put 25:00 is in the column it will be 1:00 so the answer is the salary they gave you to work on
25:00 will be shown as 01:00 if to use time format hh:mm. But if to use elapsed time format [hh]:mm it will be shown as 25:00.
In both cases the value will be exactly the same, format is only define how to show such value.
It works correctly
25:00 is shown as 1:00 if you apply h:mm format, not [h]:mm. But it is still 1+1/24
It seems like I'm still running into some strange things after all kinds of attempts... I'd greatly appreciate it if anyone can provide some pointers / explanation on what's going on. Thank you in advance for your time and response.
Your example (Haytham Amairah & SergeiBaklan) made sense, but for some reason that's not what I am getting with my Excel. (I'm using this on a Mac, does this change things?)
Looking at the chart below, I've tried several ways to get the "right value" of $1,456, but none is intuitive / perfect. I thought I loved Excel, but this is getting too confusing.
To explain what I've tried:
- Having G23 formatted as "[h]:mm;@", I am able to show the "total number of hours" worked for the pay period for my babysitter. However, it's not really usable as a number, as if I multiply it to ($)16, it gives me 60.666667. (I'm not sure how it arrived at that.)
- If I don't format G23, as I did in D23, the result of D23 * G24 is the same, as shown in D25.
- If I multiply it using HOUR() & MINUTE(), as G26 shows, I get 19 * $16. (19 would be the remainder of 91 hours / 24). At least it shows as a $ that I'd like to see, but how do I change it to 91 hours (G23)?
- If I multiple @Regular Hours to G24 ($16), I am able to get "128:00" as some value, which is supposed to be the daily total wage, but if I change it to currency cell format, it gives me $5.33...
- if I changed the format on cell G25 to custom with "[h]:mm;@", then it gives me 1456:00:00... so then I tried changing it to $[h], then I get $1456, but then I don't know how to have it display the comma at the $1,456.....
- In column H, i.e. H6, I tried =PRODUCT(HOUR(G6)+(MINUTE(G6)/60), $G$24), which gave me I don't know what that is, but if I summed them all, with =SUM(Table6[Daily Total]) in H22, it gave me my $1,456 total wage in currency view with comma!
- H25 is what I'm hoping to arrive at, but right now this is done manually with I23 typed in and multiplied with G24.
I imagine this is completely correctable, so I'd rather not type it out for every pay period. Not that I'm lazy, but like I said... I do love Excel... (most of the time).
Best,
JoeThat doesn't matter which format do you apply to the cell, value in it is not changed with change of the format. For example, here are different formats applied to the cells which have exactly the same value in them
Dates in Excel are sequential integer numbers where 1 is equal to 01 Jan 1900. Time is decimal part of the number, thus 1 hours equals to 1/24.
If time elapsed format shows 91:00 it is equal to 3 and 19/24 days. In 1 day there are 24 hours, thus above is 3.7916666667*24 = 91 if we'd like to have number of hours.
Thus in you case Total to pay =24*G23*G24
