Convert text to time
I am having trouble converting a text cell to a time cell. Please help as this is driving me mad! I have found several fixes online but none of them specifically address my problem.
I have time inputted as text, such as 8.40 to indicate 8 hours and 40 minutes. However, if I format the cell as a time cell, it changes the time to 9:36. This is not right. I just want it to display as 08:40. If I go 'find' and 'replace' the full stop with a colon, it doesn't work either, it returns 08:04 which is also wrong. Any help will be very much appreciated PLEASE!!!
N
it cant be text as find replace would work fine,i just tried it, also formatting as anything else would not change it
try
=TIMEVALUE(INT(A1)&":"&TEXT(100*(A1-INT(A1)),"00")) cell formatted as time
Last edited by martindwilson; 01-06-2012 at 04:59 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Brilliant! Thanks! That has worked! Relief!! Thanks so much :-)
Try also,
=SUBSTITUTE(A1,".",":")+0
format as time
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
@Haseeb, that would return 8:04 rather than 8:40
FWIW, another variant:
=SUBSTITUTE(TEXT(A1,"00.00"),".",":")+0
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
see there's always a shorter way!
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks