• 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


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

Converting numbers to Time [SOLVED]


Hi chandoo

In column A1 to A3 I have entered the numbes as follows:




This means 2hours 30 minutes each & 10 minutes the 3rd one but when I sum them in A4 it calculates to 4.7 but I want to get result as 5hours 10 minutes. When I am trying to format the 2.3 to custom time format it automatically converting into 7:12am which is not right it should be 2:30 & when I enter 0.1 for 10minutes it automatically converting to 2:24am which is also wrong kindly suggest the solution for this. In simple words I can say If I convert 7.5 numbers to 7:30 (7hour 30 minutes) how?
Hi Shayeebur ,

There seems to be some inconsistency in your post ; on the one hand , you say 2.3 should represent 2:30 ( 2 hours and 30 minutes ) ; later on , you say 7.5 should represent 7:30 ( 7 hours and 30 minutes ) ; can you say which is correct ?

Hi Shayeebur,

if calculated value of 4.7 is present in B4 (lets assume) then you can try this formula:


Hope that helps,

Last edited by a moderator:
You can add them up using:


Then Use a Custom Number format of [h]:mm


BUT, I'd suggest using times as times are meant to be used

Entering 2:30 for 2 hrs and 30 mins

0:10 for 0 hrs and 10 mins


Then a simple =Sum(A2:A4)

Excel will add them up properly without fancy formulas like above

I am sorry NARAYANK991 i have merged two different questions in one, but now i know how to convert 7.5 decimal number to 7:30 hours & minutes.

Faseeh & Hui thanks a lot for giving me solutions for my question i was practicing a lot to get this answer but u people answered me correctly, once again thanks a lot.

i got the right answer for the above question but the thing is when the time is going more than 24 hours it is automatically converting to 1 hour like if the total sum of the time is 25 hours then it is showing as 1 hour. can anyone please tell me to get the answer as 25:00 instead of 1:00.