Forum Discussion
Format problem calculating salary
I want to calculate salary. I enter hours 'from' and 'to' using Format Time: 13:30. Then I add the days in a cell using Format Custom: [h:mm]. I add the four weeks using the format Custom: [h}:mm. So far so good.
How do I take, let say, 149 hours and 3/4 - that is 149.45 - and dividing those hours by 20 and then multiply by $17.75 so that I end up in the cell with a format that will give me $132.91.
Thank you
6 Replies
You’ve got it — the key is that Excel stores time as fractions of a day.
So 10:15 means 10 hours and 15 minutes, which is actually 10.25 hours if you multiply by 24.So for your workflow:
- Use your [h]:mm format to enter & sum hours across days.
- Then in a separate cell, convert total time to decimal hours:
Now you can safely divide by 20 and multiply by your hourly rate. Example:If YourTotalCell was 119:45, then 119.75 (in decimal) will come automatically.
How do I take, let say, 149 hours and 3/4 - that is 149.45....
Stop: 3/4 is .75 so 149 and 3/4 hours would be 149.75
The formula would be =(149.75/20)*17.75
or, better, refer to the cells containing those various values. My calculator gave me $132.90, for what that's worth.
(What, by the way, is the source of the 20?)
mathetesHello, 3/4 of kan hour is really 45 minutes as there are 60 minutes in an hour.
The 20 is just the numbe to use to calculte the holiday to pay, it is based on the last 4 weeks of paid hours.
3/4 of kan hour is really 45 minutes as there are 60 minutes in an hour.
Yes, but 149.45 is a decimal number: 149 and 45/100, not 149 and 45/60.
As a decimal number, you should use 149.75 since .75 = 75/100 = 3/4.
It's different if you use time format: 149:45 is indeed 149 and 45/60. But that is not what you used in your calculation.
