Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Replace Please
Hello from Steve Dee
I would like to please to the following replace Decimal with colon to make it into time as an example 9.30 to 9:30 please if I use . then replace : using 9.30 as an example it will return 9.03 so I need a formula please. Thanks for your help in this matter. |
#2
|
|||
|
|||
this seems to work if i8 is formatted as text and entered as 09.30
=TEXT(LEFT(I8,LEN(I8)-FIND(".",I8))&":"&RIGHT(I8,FIND(".",I8)-1),"HH:MM") -- Don Guillett SalesAid Software "Steved" wrote in message ... Hello from Steve Dee I would like to please to the following replace Decimal with colon to make it into time as an example 9.30 to 9:30 please if I use . then replace : using 9.30 as an example it will return 9.03 so I need a formula please. Thanks for your help in this matter. |
#3
|
|||
|
|||
Didn't we do this this morning?
=TIME(INT(A1),MOD(A1,1)*100,0) -- HTH ------- Bob Phillips "Steved" wrote in message ... Hello from Steve Dee I would like to please to the following replace Decimal with colon to make it into time as an example 9.30 to 9:30 please if I use . then replace : using 9.30 as an example it will return 9.03 so I need a formula please. Thanks for your help in this matter. |
#4
|
|||
|
|||
Try:
=SUBSTITUTE(TEXT(A1,"#.00"),".",":")*1 HTH Jason Atlanta, GA -----Original Message----- Hello from Steve Dee I would like to please to the following replace Decimal with colon to make it into time as an example 9.30 to 9:30 please if I use . then replace : using 9.30 as an example it will return 9.03 so I need a formula please. Thanks for your help in this matter. . |
#5
|
|||
|
|||
Hi
=TIME(INT(A1),INT(MOD(A1,1)*100),0) Arvi Laanemets "Steved" wrote in message ... Hello from Steve Dee I would like to please to the following replace Decimal with colon to make it into time as an example 9.30 to 9:30 please if I use . then replace : using 9.30 as an example it will return 9.03 so I need a formula please. Thanks for your help in this matter. |
#6
|
|||
|
|||
Hello Bob from Steve Dee
Was a substraction. Using your formula below =TIME(INT(A1),MOD(A1,1)*100,0) and to use your formula below To Subtract =INT((A1-B1)*24)+MOD((A1-B1)*24,1)*60/100 12:17 from 3:20 returns -223 What is required please to get 3:03 and 9:30 from 12:25 to return 2:55 Yes Bob I had decimal time but have now changed it using your formula 9.30 to 9:30 Thankyou. -----Original Message----- Didn't we do this this morning? =TIME(INT(A1),MOD(A1,1)*100,0) -- HTH ------- Bob Phillips "Steved" wrote in message ... Hello from Steve Dee I would like to please to the following replace Decimal with colon to make it into time as an example 9.30 to 9:30 please if I use . then replace : using 9.30 as an example it will return 9.03 so I need a formula please. Thanks for your help in this matter. . |
#7
|
|||
|
|||
Thankyou
-----Original Message----- Try: =SUBSTITUTE(TEXT(A1,"#.00"),".",":")*1 HTH Jason Atlanta, GA -----Original Message----- Hello from Steve Dee I would like to please to the following replace Decimal with colon to make it into time as an example 9.30 to 9:30 please if I use . then replace : using 9.30 as an example it will return 9.03 so I need a formula please. Thanks for your help in this matter. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
replace command | Excel Discussion (Misc queries) | |||
Replace using wildcards | Excel Discussion (Misc queries) | |||
macro to Find Replace in Excel | Excel Discussion (Misc queries) | |||
Replace function not working properly in Excel 2000 SP3 | Excel Worksheet Functions | |||
search & replace | Excel Worksheet Functions |