Calculate Amount of time worked.. Help stop me working for nothing 4

[addicted2]

Hi there can some one help me please. I work different hours each day of the week and I cant seam to find how to calculate these hours to ensure that I do 40 hours a week and show If if i do over the 40 hours Please bear with me and I will try to explain

A)CELL A1 40 (representing the hours) CELL A2 2400 Representing the minutes-these will change progressivly as i enter how much how much time i work daily.
CELL B2 Monday B3 Hours that I work B4 minutes Minutes that I have worked B5 total Hours in B3 Plus Minutes in B4.

IE:
B2 Monday B3 6 (hours) B4 45 (Minutes) B5 405 (Total Minuts Worked)
This will be done for the 7 days.At the end of the week I need to convert these total minuts back to hours firstly for a cross reference Secondly to print out for the company.

When I go to convert the 405,dividing by 60,in Cell B5 I keep geting 6.75.

Sorry If i could i would have explained it better.
Regards Tony

 

[dsi]

I am not sure that I understand your problem.
405 / 60 = 6.75 is correct.
Are you just looking to convert that answer to hh:mm format?

 

[addicted2]

DI Sorry I have explianed it real bad. No what I am trying to do is convert the minutes that I have worked to hours and minuts. When I enter 405 and divide that by 60 to get the time I worked the answer that I get is 6.75. What I am trying to get is 6.45.. Representing 6 hours and 45 minuts

 

[bryanbayfield]

Stick this in an appropriate Standard Module:

Public Function hours_done(mins_tot As Integer) As Single
hours_done = CSng(WorksheetFunction.RoundDown((mins_tot / 60), 0) & "." & mins_tot Mod 60)
End Function

SuperBry!

 

[Loomah]

Hi addicted2
Bry's solution is a handy one!

But you can do the same without code by using the following calculations
If your 405 is in cell A1
in B1 put =INT(A1/60)
in C1 put =MOD(A1,60)

This will enable you to total minutes and hours, though it may also lead to other problems depending on what you ultimately want.

The above can also be 'concatenated' into one cell but the result will not be usable in calculations. Not sure if that's a problem with Bry's suggestion

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[bryanbayfield]

Loomah, there's no error handling in my function, but since it only takes an integer as an argument I don't think there's anything to worry about.

However, your code would be more useful than mine if the function had to be implemented in more than one workbook, since it would involve less faffing about. SuperBry!

 

[sfvb]

If you format cell B5 as TIME, then you could use either
=TIME(B3,B4,0)
or
=(B3*60+B4)/1440

 

[macbone2]

i use excel spreadsheet to generate a time sheet from which i invoice. it has 7 columns DATE, START TIME (HH:MM), START LUNCH BREAK (HH:MM), END LUNCH TIME, FINISH WORK, THE DATE IS CALCULATED DAILY FROM THE ONE BEFORE.
you enter the other 4 columns, and the hours & minutes worked are calced automatically. the last column is just (my) manual conversion from hh:mm to hours decimal eg 7:30 = 7.50 etc
if you'd like me to email you a copy of the sheet, let me know your email address

rgrds
geoff

 

[addicted2]

wow So much Info Thanx heaps to all of you for your time. Fantastic.

 

[reszek]

to do the conversion from 6.75 to 6:45 you could also do a combination of things, have a hidden column to get 6.75 and a hidden one that rounds to the nearest ones place, in this case 6, then subtract the first from the second, 6.75 - 6 then 60/100 is to a/75 and have excell solve for a