Custom Formatting: 5.23 into 5 hrs. 14 mins.
 

Hello,

I have a range of data (for time periods) that's in decimal format.

I need to have it formatted into time format ( h "hrs" mm "mins").

For example:

5.23 should be 5 hrs 14 mins.
3.79 should be 5 hrs. 47 mins.
etc.

How do I achieve this? I have tried with custom formatting but all formats I
have used convert 5.23 into 5 hrs. 31 mins., which is wrong.

Any ideas?

I appreciate all help.

--
igor

Custom Formatting: 5.23 into 5 hrs. 14 mins.
 

=INT(A2)&":"&INT(RIGHT((A2),LEN(A2)-FIND(".",A2))/100*60)

will show in h:mm format

Replace ":" with " hrs " and add &" mins" to get as h hrs mm mins

"Igorin" wrote:

Hello,

I have a range of data (for time periods) that's in decimal format.

I need to have it formatted into time format ( h "hrs" mm "mins").

For example:

5.23 should be 5 hrs 14 mins.
3.79 should be 5 hrs. 47 mins.
etc.

How do I achieve this? I have tried with custom formatting but all formats I
have used convert 5.23 into 5 hrs. 31 mins., which is wrong.

Any ideas?

I appreciate all help.

--
igor

Custom Formatting: 5.23 into 5 hrs. 14 mins.
 

Thank you very much, Sean!

Excellent!

--
igor


"Sean Timmons" wrote:

=INT(A2)&":"&INT(RIGHT((A2),LEN(A2)-FIND(".",A2))/100*60)

will show in h:mm format

Replace ":" with " hrs " and add &" mins" to get as h hrs mm mins

"Igorin" wrote:

Hello,

I have a range of data (for time periods) that's in decimal format.

I need to have it formatted into time format ( h "hrs" mm "mins").

For example:

5.23 should be 5 hrs 14 mins.
3.79 should be 5 hrs. 47 mins.
etc.

How do I achieve this? I have tried with custom formatting but all formats I
have used convert 5.23 into 5 hrs. 31 mins., which is wrong.

Any ideas?

I appreciate all help.

--
igor

Custom Formatting: 5.23 into 5 hrs. 14 mins.
 

Thank you for the feedback!

"Igorin" wrote:

Thank you very much, Sean!

Excellent!

--
igor


"Sean Timmons" wrote:

=INT(A2)&":"&INT(RIGHT((A2),LEN(A2)-FIND(".",A2))/100*60)

will show in h:mm format

Replace ":" with " hrs " and add &" mins" to get as h hrs mm mins

"Igorin" wrote:

Hello,

I have a range of data (for time periods) that's in decimal format.

I need to have it formatted into time format ( h "hrs" mm "mins").

For example:

5.23 should be 5 hrs 14 mins.
3.79 should be 5 hrs. 47 mins.
etc.

How do I achieve this? I have tried with custom formatting but all formats I
have used convert 5.23 into 5 hrs. 31 mins., which is wrong.

Any ideas?

I appreciate all help.

--
igor