Converting numbers to Time [SOLVED]

[Shayeebur]

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]

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]

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,

 

[Hui]

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]

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]

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.

 

[NARAYANK991]

Hi Shayeebur ,


Try this :


[hh]:mm:ss


Narayan