• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to convert decimals into days, hours and minutes

D

DAL

New Member
Hello, I looked up this formula on Excel forums, but when I use it, it doesn't give me the right result as you can see in the attached file.
=IF(INT(L17/24)=0,"",INT(L17/24)&" Days ")&IF(HOUR(MOD(L17,24)/24)=0,MINUTE(MOD(L17,24)/24)&" Minutes",HOUR(MOD(L17,24)/24)&" Hours")
Can somebody help me please?
Thank you.
DAL
 

Attachments

  • Timesheet alternative.xlsx
    28.4 KB · Views: 7
Maybe try

=IF(SIGN(L17)=-1,"-",)&TEXT(INT(ABS(L17/24)),"[<>0]0 \Da\y\s ;")&TEXT(INT(MOD(ABS(L17),24)),"[<>0]0 \Hour;")
 

Attachments

  • Timesheet alternative.xlsx
    28.6 KB · Views: 6
Thank you but unfortunately it doesn't work either. It only gives me the first part of what I need and not for example the days AND the hours AND the minutes. Why? Also : the data here is negative but it could and even should be possible.
Thanks a lot for your help!
Kind regards.
 

Attachments

  • Timesheet alternative.xlsx
    28.3 KB · Views: 3
DAL
Could this give Your expected results?
Note: All filled times have to be correct!
I modified some parts ... times are times!
 

Attachments

  • Timesheet alternative.xlsx
    32.2 KB · Views: 5
Hello Vletm, thank you for your input. There is still a problem with column N : when the worker doesn't work for 4 days the number of days he still owes doesn't add correctly. When you take N44 for exemple it should already be -4days....
Working with time and dates is really brainburning for me!
Thanks you.
 

Attachments

  • Timesheet alternative (2).xlsx
    32.5 KB · Views: 6
DAL
What is problem?
As I wrote Note: All filled times have to be correct!
Are those 'zero times' correct?
As You could see that those number of 'days' has solved from C-column
>> You can modify that as it works with Your needs - eg count dates if those daily start times are over zero.
Working with time and dates are as clear as working with numbers or anything else ... for some reason, You've done it Your way.
 
DAL
... but that also would cause some challenges...
If worker left 'sort days' without marking ... is it okay?
If worker works only afternoon?
... and so on.
Do You have a clear idea - what would You need to see from those markings?
 
You must log in or register to reply here.
Back
Top