SOLVED

Multiplying time (for wage calculations).

Copper Contributor

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 hours worked, and then multiply those hours by an hourly wage.

In order to make Excel tot-up the hours worked beyond 24, I’m using the custom cell format, [h]:mm. This is giving me the correct total hours and minutes worked (minutes are being rounded to the nearest 15 prior to input). However, when I try to multiply this total by the hourly wage, I’m getting an inaccurate (way too low) result. So, even though excel is displaying the time correctly, it is still considering it as a fraction.

After some online research, I managed to get the right result by using the formula =TEXT(V5, "[h]")*100. V5 is the cell with the total hours worked, in [h]:mm format, and 100 is the hourly wage.

However, this is only giving me the correct result to the nearest hour. So for example if the total (in V5) is 10:00 (ten hours), then the wage total is being correctly given as 1000. However, it the total is 10:30 (ten and a half hours), the total is still being given as 1000, instead of 1050.

I assumed this is because I am using just “[h]” in the formula, but if I try using “[h]:mm” (so, =TEXT(V5, "[h]:mm")*100), then I am just getting the same fraction-based incorrect result (with 10 and a half hours and 100 per hour, the result is coming out at 43.75, not 1050).

So, now I’m stuck. I’ve tried several things but haven’t found a solution. I imagine there’s a simple way to solve this that I’m just unaware of.

Apologies for the long explanation, and thanks in advance for your advice.

Simon

8 Replies
best response confirmed by Chapman Simon (Copper Contributor)
Solution

Chapman,

 

It's simpler than you imagine!

 

This little formula is all you need:

=A2*24*B2

Wage Calculation.PNG

You're a hero.
I was expecting there to be a simple solution, but not that simple! Haha.
Thanks very much.

@Haytham Amairah 

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 

It works correctly

image.png

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 & @Sergei Baklan) 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). 

 

Screen Shot 2021-12-19 at 1.48.18 AM.png

 

Best,
Joe

@yoyoyojoe 

That 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

image.png

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

 

@Sergei Baklan 

 

Thank you so much for clarifying!

 

(I guess since G23 is already showing the total hours, I did not think to try multiplying it by 24 hours again.)

@yoyoyojoe , you are welcome

1 best response

Accepted Solutions
best response confirmed by Chapman Simon (Copper Contributor)
Solution

Chapman,

 

It's simpler than you imagine!

 

This little formula is all you need:

=A2*24*B2

Wage Calculation.PNG

View solution in original post