• 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.

Converting numbers to Time [SOLVED]

Shayeebur

Member
Hi chandoo

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

2.3

2.3

0.1

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?
 

NARAYANK991

Excel Ninja
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 ?


Narayan
 

Faseeh

Excel Ninja
Hi Shayeebur,


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


=TEXT(TIME(INT(B4),(B4-INT(B4))*100,0),"HH:MM")


Hope that helps,


Regards,
 
Last edited by a moderator:

Hui

Excel Ninja
Staff member
You can add them up using:

=(INT(SUM(A2:A4))+(SUM(A2:A4)-INT(SUM(A2:A4)))/0.6)/24

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

=5:10


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

etc

Then a simple =Sum(A2:A4)


Excel will add them up properly without fancy formulas like above
 

Shayeebur

Member
hi

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.
 

Shayeebur

Member
hi

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.
 
Top