Forum Discussion
Multiplying time (for wage calculations).
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
- You're a hero.
I was expecting there to be a simple solution, but not that simple! Haha.
Thanks very much. 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.
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,
Joe
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.
