Time Formatting (Number Format?)

Hello,

I've stumbled across a task I need a assistance with I was hoping someone could kindly assist me.

I have a column of data with hourly time in each cell stored in fractional format.

For example: 1 Hour and 15 minutes is stored as 1.25. Half an hour (30 Minutes) is stored as 0.5.

I have thousands of rows of data in this column that I need to format from 1.25 to 01:30.
I don't want to tamper with the data in the cells by dividing by 24 and then changing the number format. This is for marketing purposes and the data cannot be changed, nor hidden rows and all of that mess.
I am looking for a way to apply some kind of number format or conditional format that automatically changes this entire column, so if I were to enter 0.5 in a cell, it would automatically become 00:30, ect.

I've attached a dummy spreadsheet for your reference.

Thanks!

2 and 2:00, for example, have different numeric values. 2:00 is actually 0.08333 (2/24)

Unfortunately, there is no way to keep one value (2) in a cell but format it to show in the same cell as another value (2:00).

Hi

I suppose you can not do what you want. You can not manipulate the format to somehow get the desired result.

Looks like I was proved wrong!

Nice Bo_Ry.

Bo_Ry you are a genius. Thank you very much for solving.