Replace decimal separator in time format to colon

  • Can you help me to change a time format in cell A1 from H.MM.SS to cell A2 in H:MM:SS please. The difference being a colon instead of a full stop.The formula bar in both cases is showing 0:12:34AM but the next part of the work returns #VALUE! as the result. This is what I need to happen next. The next cell A3 changes A2 into a number like 0.123456 which then has 2% added to it and returned in cell A4. And finally cell A5 returns a new time as H.MM.SS again. I think there are some hidden cels that do this final conversion.
    Many thanks
    Paul

  • Re: Time formats


    Hi. Thanks junho lee. Unfortunately not. It now returns another error saying there is an error in the formula. Cheers


    Paul

  • Re: Replace decimal separator in time format to colon


    Hello Strider,


    Welcome to Ozgrid. We're glad to have you on board, however, please note the following regarding thread titles:


    Thread titles are used in searching the forum, therefore, it is vital the be written to accurately describe your [COLOR="blue"]thread content or overall objective[/COLOR] using ONLY search friendly key words.


    • The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
    • The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters
    • The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different


    Please note the change to your title, which is based on the objective stated in your thread.
    ------------------------------------------------------------------------------------------------


    Try:


    =TEXT(A1,"h:mm:ss AM/PM")+0


    Or


    =TIME(HOUR(A1),MINUTE(A1),SECOND((A1)))


    and format the target cell with desired time format

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!