Need assistance with converting hours into days

Copper Contributor

Hello everyone,

I would need assistance, I need to find how to make a formula where I can:

- calculate how many days did someone work, based on the fact that 1 day of work corresponds to 5 hours and not 8H or 24H.

Thank you for your assistance.

3 Replies
more information is needed like how and what information is being entered now. A sample sheet of the format and sample data would be ideal (attached or linked here).
Some key points to know is that a date/time is based on #days (i.e. the integer portion) and fraction of a day so if you have actual date/times in cells then a difference will result in a value in #of days. If you say 7pm - 7am then you would get 0.5 as in 1/2 day. So in your example 5/24 is the fraction of 1 day that would equal 5 hrs. If someone worked X #hrs and X is formatted as a date/time, then = X / (5/24) will result in the # of equivalent 5hr days.

@mtarler Hello, thank you for your reply.

Please see attached the spreadsheet i'm trying to create for my employees hour control and calculation of both hours worked, I need a formula that would convert these hours into days (each 5h is 1 work day) and also allowing me to calculte the payment based on the days worked.

 

I'm currently working on June (lines 63-73), it' in portuguese but it's basically resumed into the sum of the month's worked hours into a cell where it shows the total amount of normal hours, extra hours..

 

Then I would need the cell called DIAS to have a formula that would calculate the total amount of hours into days (5h equals to 1 day) and another cell where a formula would use the amount of days worked and multiply the hours by the daily salary (or divide it) in order to know how much should we pay each month.

 

thank you

 

Capturar.PNG

It looks like you want the total days in H73 so in that cell type =B73/5
I don't know what you want to do about partial days. right now I see 20 hours which is exactly 4 days but what if it was 21 hrs? should it ROUND off =ROUND(B73/5,0) or just cut off the excess like INT(B73/5) or leave the fraction as is