Automatically calculate working hours! PLEASE HELP!!

[bennett493]

Hi,

We have had a new and quite frankly stupid system put in at work which logs clock in times for my team however the format it gives me is in a decimal of a day for example. I clocked in at 08:00 and clocked out at 16:00 and the number of hours it showed for me working that day is 0.3. Now I understand that this is a 3rd of a day however it starts getting a bit more of a headache to work out when you clock out with more or less than 8 hours. is there any way I can input the decimal number and it automatically work out the number of hours and minutes worked in a day I am sure I haver seen this happen before in a previous job but I cant remember the formula for it. any help would be greatly appreciated. I am using office 365

cheers

 

[Special-K99]

That does sound rather stupid to me too.
8 hrs is indeed 1/3 of a day but 1/3 is 0.33333 not 0.3
Are you sure it's 0.3 and not something like 0.33333 cos if it's 0.3 it's gonna put the calculation out when converting back to hours and minutes.

 

[Chris Mack]

If you reformat the number as a time it will give you the hours/minutes. An example custom format would be:

h:mm

Hope this helps,

Chris.

 

[bennett493]

yes sorry I rounded down the hours. I have tried the formatting option and that works for one day which is great thank you however. at the end of the week it adds all of it up. so for my hours I have 1.472916667 when i try just formatting the option it gives me 11 hours 21 mins which is incorrect.

 

[Chris Mack]

How many hours should it be?

 

[bennett493]

35 hours approximately, I can work it out by simply adding it up day by day once I have formatted to time to get the total however it would be better if I can just pit the decimal in and it work out automatically the total.

 

[Special-K99]

=INT(A1*24)
give hours

=(A1*24-INT(A1*24))*60
gives minutes

 

[VoG]

Format the result cell as Custom

[h]:mm

 

[bennett493]

excellent thank you. all sorted now. diddnt realise it was that easy