Assist with formula to convert min to percentage of an hour

[jrl1208]

Hi
I'm trying to write up a formula to convert the minute into percentage of an hour (Eg 2.25 = (25/60)+2)
I'm struggling to get the correct formula to work. Any kind of help would be appreciated.
File is attached with the formula which requires modification.
Thank you.

 

[vletm]

jrl1208​

cell J6 =INT(C6)+(C6-INT(C6))/0.6
if You would like to take care blanks then =IF(C6>0,INT(C6)+(C6-INT(C6))/0.6,"")

 

[bosco_yip]

Or,

In J6, formula copied across and down:

=IF(C6="","",DOLLARDE(C6,60))

 

[jrl1208]

jrl1208​

cell J6 =INT(C6)+(C6-INT(C6))/0.6
if You would like to take care blanks then =IF(C6>0,INT(C6)+(C6-INT(C6))/0.6,"")

Thank you so much. I also want to modify the LWP cell. If there is a value in Cell G6 then carried out the calculation plus on top of that do a

IF(SUM(J6:M6)<=8,8-SUM(J6:M6)). How can I modify it. Thank you

 

[vletm]

jrl1208​

# Do Your plus on top of that means below? ... if over 8 then ... what?
cell N9 =IF(G9>0,INT(G9)+(G9-INT(G9))/0.6,IF(SUM(J9:M9)<=8,8-SUM(J9:M9),"what?"))
... and copy as You need.
# You missed FALSE part of Your function -- I used what? for it.

BUT
Why do You want to do something You've done?
Instead that ... could You fill Your times ... as times ... hrs and minutes ... like below?
... then those can calculate as times?
Of course, those time-cells should be formatted as times and those can show hrs:minutes more than 24hrs too.

Ps. Zeros can hide by hiding Zeros in workbook.

 

[jrl1208]

jrl1208​

# Do Your plus on top of that means below? ... if over 8 then ... what?
cell N9 =IF(G9>0,INT(G9)+(G9-INT(G9))/0.6,IF(SUM(J9:M9)<=8,8-SUM(J9:M9),"what?"))
... and copy as You need.
# You missed FALSE part of Your function -- I used what? for it.

BUT
Why do You want to do something You've done?
Instead that ... could You fill Your times ... as times ... hrs and minutes ... like below?
View attachment 85305... then those can calculate as times?
Of course, those time-cells should be formatted as times and those can show hrs:minutes more than 24hrs too.

Ps. Zeros can hide by hiding Zeros in workbook.

Sorry i wasn't being clear. What i'm trying to do is convert the table with the heading Bio Time ( hours/ min) to percentage in hours in the other table with the heading HR3. The formula you provide is perfect.
However, the daily total hours from the Bio Table ( M-T) should total 8hrs in total and Frid should total 6hrs in total.
I want to add a condition, if the Total hours is less than or greater than 8hrs for Monday, I want to adjust the hours in LWP so that the total is 8.
(Eg Monday the total hrs is 8.37 but i want the formula to adjust the LWP in the HR table to 5.38 so the total is 8)
(EG Tuesday the total hours is 6.50 but I want the formula to adjust the LWP in the HR table to 6.17 so the total hours is 8)
Hopefully this makes sense. Thank you

 

[vletm]

jrl1208​

Did You notice my question? Why do You want to do something You've done?
Means same as why You do not use time with your BioTime as I've shown with green?
In many times 2:37 means 2 hrs and 37 minutes ... then You do not need Your HR3 'table' at all!
After You've replied above ... then I could try to continue.

 

[jrl1208]

jrl1208​

Did You notice my question? Why do You want to do something You've done?
Means same as why You do not use time with your BioTime as I've shown with green?
In many times 2:37 means 2 hrs and 37 minutes ... then You do not need Your HR3 'table' at all!
After You've replied above ... then I could try to continue.

 

[jrl1208]

Sorry if i missed your question. The data from the Biotime is an export from the system which needs to be entered into HR3. However HR3 can't accept the existing time format. It has to be converted hence why i need the formula which you have provided. I hope this makes sense. Thank you.

 

[vletm]

Sorry if i missed your question.
How to give answers if You'll skip writings?
Means same as why You do not use time with your BioTime as I've shown with green?
Means ... cannot You get data from somewhere which is already useful?
Means ... do that somewhere really give data exact as You've shown in Your files?
Do You have more data to do something than those two weeks?
However HR3 can't accept the existing time format.
... hmm? Now, Your given HR3 is ... those J..O-columns