Excel 2007 : how convert time in decimals to time format

[pksreela]

friends




can you help?




sreela

[vlady]

Hello friend,

How about:
A2 is the number
Divide each number by 24
=A2/24
Format your result cells as custom "[hh]:mm".

Cheers,
Vlady

[pksreela]

sorry.
I want the time entered in decimals to be read in time format eg 5.3 ,it should read 5:30

sreela

[pksreela]

5.3 should read 5:30 ,its using substitute

[protonLeah]

You should have posted some sample data so people would know what you are working with.

[pksreela]

Dear friends

The start time and ending time taken for travelling from an origin to destination available with me,is entered in different columns in excel.During the data entry process itself,the time is entered in decimals.for eg 5.30 is entered for 5:30.In order to get the time taken for travel I require the difference between the start time and end time of trip .For this purpose I require my time entered in Excel as 4:30,5:15,2:30,16:40 etc instead of 4.3,5.15,16.4 etc.Since the data is too much,I require the help from excel

regards
sreela

[protonLeah]

Given:
Column A = start time formatted as text
Column C = end time formatted as text

formula in column B (formartted as time):

Please Login or Register  to view this content.

this will replace the period/decimal with a colon and, if the period/decimal is the second to the last character, then it will add a "0" such that 5.3 in A2 will result in 5:30 to be operated on by the TIMEVALUE function. Thus, in column D:

Please Login or Register  to view this content.

Then your duration will be D2-B2

[pksreela]

sir,
problem is solved
thank you very much
sreela

[pksreela]

Thank you very much