Conversion of time stored as numbers to actual time.

[SINNARKAR]

Recently, after lockdown, our Administration has started noting the In and Out time as 8.17 and 17.30. They have recorded the timings for about 100 employees for a week and when they tried to get number of hours, it was impossible for them to get the exact number of hours worked. Then I used the technique to convert those numbers to time by using copy and paste in word and find and replace "." with ":" and paste back to excel.
This is the out of way conversion of number to time. But is there any way for doing such conversion? Please guide me.

 

[SINNARKAR]

Dividing the number / 24 does not work properly.

 

[Fluff]

Select the cells with the time, then Ctrl H to bring up the search/replace dialogue box, Put . in the Find what box & : in the replace with box, then Replace all.

 

[SINNARKAR]

The way in which I have done is Taken the timings i.e. 8.17 copied in word and then replaced . with : and pasted back to Excel. This is the fastest alternative I found.

 

[Dave Patton]

format to your preference

T202101a.xlsm
	A	B
2	8.17	8:17 AM
3	17.3	5:03 PM
4b

Cell Formulas
Range	Formula
B2:B3	B2	=TIME(LEFT(A2,FIND(".",A2)-1),MID(A2,FIND(".",A2)+1,99),0)

 

[Dave Patton]

You can ignore the above post.
I posted it in case you wanted to use the Time function.
Excel's find and replace as suggested is the solution that I would use.