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
Replace decimal separator in time format to colon
-
-
-
Re: Time formats
Hi Strider,
Nice to meet you.
Try this function.
=SUBSTITUTE(A1,".",":")
Regards, junho -
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
-
Re: Replace decimal separator in time format to colon
Hi AAE
Thanks for the title tip. That make a lot of sence.
Regards
Paul -
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!