convert time to text

C

checkQ

Hi,
Does anyone knows a formula to convert the decimal time 47.75 to text in
order to read 47 hrs and 45 minutes?
Thanx
 
D

Dave Peterson

Something like:
=INT(A1)&" hours and "&INT(MOD(A1,1)*100)&" minutes"
with 47.45 in A1
 
R

Ron Coderre

Try something like this:

With
A1: 47.45


B1: =TEXT(I7/24,"[h] \h\r\s mm \m\i\n\u\t\e\s")

or....
Just give A1 a Custom Number Format

Select A1
From the Excel main menu:
<format><cells><number tab>
Category: Custom
Type: [h] "hrs" mm "minutes"
Click the [OK] button

Is that something you can work with?
Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
R

Ron Coderre

Sorry....my ref to I7 should have been to cell A1.

B1: =TEXT(A1/24,"[h] \h\r\s mm \m\i\n\u\t\e\s")

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


Ron Coderre said:
Try something like this:

With
A1: 47.45


B1: =TEXT(I7/24,"[h] \h\r\s mm \m\i\n\u\t\e\s")

or....
Just give A1 a Custom Number Format

Select A1
From the Excel main menu:
<format><cells><number tab>
Category: Custom
Type: [h] "hrs" mm "minutes"
Click the [OK] button

Is that something you can work with?
Does that help?
***********
Regards,
Ron

XL2002, WinXP


checkQ said:
Hi,
Does anyone knows a formula to convert the decimal time 47.75 to text in
order to read 47 hrs and 45 minutes?
Thanx
Click to expand...
Click to expand...
 
C

CLR

This will work, even if you have more or less than 2 digit hours and minutes

=MID(A1,1,FIND(".",A1,1)-1)&" hrs and "&MID(A1,FIND(".",A1,1)+1,99)&" mins"

Vaya con Dios,
Chuck, CABGx3
 
R

Ron Coderre

The warranty on my fingers must have run out this morning,
because this afternoon they're just not working right.

The value in A1 should have been 47.75
.....like you posted (and I mis-typed as 47.45)

Hence...
A1: 47.75
B1: =TEXT(A1/24,"[h] \h\r\s mm \m\i\n\u\t\e\s")


***********
Regards,
Ron

XL2002, WinXP


Ron Coderre said:
Try something like this:

With
A1: 47.45


B1: =TEXT(I7/24,"[h] \h\r\s mm \m\i\n\u\t\e\s")

or....
Just give A1 a Custom Number Format

Select A1
From the Excel main menu:
<format><cells><number tab>
Category: Custom
Type: [h] "hrs" mm "minutes"
Click the [OK] button

Is that something you can work with?
Does that help?
***********
Regards,
Ron

XL2002, WinXP


checkQ said:
Hi,
Does anyone knows a formula to convert the decimal time 47.75 to text in
order to read 47 hrs and 45 minutes?
Thanx
Click to expand...
Click to expand...
 
C

checkQ

Thanks for the resspose they worked perfectly

CLR said:
This will work, even if you have more or less than 2 digit hours and minutes

=MID(A1,1,FIND(".",A1,1)-1)&" hrs and "&MID(A1,FIND(".",A1,1)+1,99)&" mins"

Vaya con Dios,
Chuck, CABGx3
Click to expand...
 
C

CLR

Sorry, didn't read your post real well...........this one will convert the
..75 to 45 minutes............

=MID(A1,1,FIND(".",A1,1)-1)&" hrs and "&MID(A1,FIND(".",A1,1)+1,99)/100*60&"
mins"

Vaya con Dios,
Chuck, CABGx3
 
Top