Calculate hours by just entering number of hours/minutes without a start end time

eobrien

New Member
Joined
Apr 29, 2017
Messages
27
Office Version
  1. 365
Platform
  1. Windows
Good Morning,

I want to just put in actual hours worked by site. So, if 5 and a half hours worked at one location 5.30 would be put in. If 5 hours and 15 minutes were worked somewhere 5.15 would be put under that site. I have tried different formatting to give me a sum/result that will compute to regular hours/minutes, but doesn't work. Actually, results don't give enough hours/minutes worked. For example at one site where 2 hours and 90 minutes + 5 hours and 10 minutes is resulting in 8 hours versus 8 hours and 40 minutes. I get it, that it's just putting minutes into whole numbers. I am sure this is an easy fix with formatting, but not coming up with it. Any assistance is appreciated. Thank you. E
 

Attachments

  • Timesheet.png
    Timesheet.png
    74.6 KB · Views: 17

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
A picture does not allow us to review your formulas.
Please also answer the questions!

Edit the following for your range
N.B. 5 hours and 50 minutes is 5.8333 hours

Time 2023.xlsm
FGHIJ
170.2020
18110
190.3030
200.15015
210.3030
22110
23110
24110
250.15015
264110
27HoursMinutes
2841105505.833333333
295.833333333
30
2g
Cell Formulas
RangeFormula
G17:G25G17=INT(F17)
H17:H25H17=(F17-G17)*100
G26:H26G26=SUM(G17:G25)
F28F28=SUM(INT(F17:F25))
G28G28=SUM(MOD(F17:F25,1))*100
H28H28=F28+INT(G28/60)
I28I28=G28-60
J28J28=F28+INT(G28/60)+I28/60
J29J29=SUM(INT(F17:F25))+SUM(MOD(F17:F25,1))*100/60
 
Upvote 0
Your request stated " I want to just put in actual hours worked by site"

That is what the formula provides. I wasted time building another example that just confirms that the suggestions were correct.
50 minutes is not .5 of an hour.
 
Upvote 0
Your request stated " I want to just put in actual hours worked by site"

That is what the formula provides. I wasted time building another example that just confirms that the suggestions were correct.
50 minutes is not .5 of an hour.
My apologies, that my explanation wasn't sufficient & that I misunderstood what you presented. I am trying to pay a person appropriately and the HH:MM x Payrate is not computing correctly. I will go back to the drawing board. Again, apologies.
 
Upvote 0
Did you try putting the + sign in the formula you showed in your image from post#7?
 
Upvote 0
You stated "I am trying to pay a person appropriately and the HH:MM x Payrate is not computing correctly. "

What does this mean?
What calculation are you trying?

With a rate of 100 per hour 5 hours is 5*100=500
5 hours and .5 hours (30 min) is 5.5*100= 550.00
5 hours and .0833 hours (50 min) =5.8333*100 =583.33
=(5+50/60)*100=583.33
 
Upvote 0
Fluff's suggestion will also yield the correct answer.

Time 2023.xlsm
EFG
15Rate$100.00$583.33
16
170.2
181
190.3
200.15
210.3
221
231
241
250.15
2g
Cell Formulas
RangeFormula
G15G15=SUM(DOLLARDE(+F17:F25,60))*F15
 
Upvote 0
That will not give the correct answer, if the OP wants the result in the same format as the the data is entered. It will need to be as I posted.
The OP just needs to put the + sign back into the formula.
 
Upvote 0

Forum statistics

Threads
1,224,816
Messages
6,181,143
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top