Trying to convert time in decimals into hours and minutes in excel

[NAtsa195]

Hi, I am trying to find a way to convert calculated annual leave in decimals to show in hours and minutes. I found the option to devide it by 24 and use the time option, but does not work if you have more than 100 hours for example the outcome is 155.40 hours which is in 155 hours and 24 minutes. If I use the Cell/24 and the time option the outcome is 11:24:00. Any idea how and which formula I need to use to get 155:24?? I am not a super user of excell, so any help is greatly appricated.

[#2]

@NAtsa195 

The formula =I21/24 is fine.

Apply the custom number format [h]:mm

The square brackets [] around h tell Excel to treat the value as cumulative hours instead of as clock hours.

[#3 · Accepted solution]

@NAtsa195 

I think you can't use the time format for that because there is no known hour equal to 155.

So treat the result as text.  =TEXT(INT(I21),"0")&TEXT((I21-INT(I21))*0.6,".00")

That said, as a former HR systems manager myself, I don't follow the logic you're using. I know Excel, so I know how to convert 155.40 to 155.24, but what is the full derivation of the 155.4 in the first place???

I can trace back the formulas; I'm not asking you to do that. I'm asking for an English language policy or procedure explanation of how annual leave is earned for a half-time employee.

Also, if you're expecting to have this used by other people you have a number of spelling mistakes that should be corrected.

In the opening paragraph:

• minites should be minutes
• multiplie should be multiply

Then in cell B22, Annull should be Annual

[#4]

@mathetes, thank you very much for your help. I am on the right way now. Still figuring out when outcome is negative. I am just a moderate user but more advanced then the rest of our team. I am trying to make it easier for them. Your help is greatly appreciated.

Regarding the spellings mistakes, I will proof read it before I am going to distribute the spreadsheet. But as Dutch native they do forgive me when I make spellings mistakes ;)

Wishing you a nice weekend,

With kind regards,

Natascha van der Pol

[#5]

@mathetescan I ask you for some more advice. I am successful with my calculation for annual leave changes and converting this to hours and minutes even when it is a minus  (so far, need them to get tested by others).

In my abatement calculation, the minus amount does not always convert to the correct minus amount in hours and minutes. Is there something I am not seeing correctly? Do you have any idea where my thought process is going wrong?

Thank you very much in advance!

[#6]

@NAtsa195 

As I said in a prior post, I have not been able to follow your thinking here, to understand how leave is earned in the first place. What is the rule, the heuristic? How is leave accumulated? How, when leave is used, is that accounted for? How can there be a negative number for leave allowance in the first place? And so forth.

You'd need to at the very least if those red numbers are not what you want or expect, please put next to the in some other color what the answer should be and why. Then maybe I can start to trace your logic. As it stands, that's far too complex a worksheet for someone unfamiliar with what you're doing...... I'm happy to try to help, but I need a bit of help from you