Hi Team,
I have date format like this: 30-Sep-2020: 6:00:00 AM
I want to print yyyy-mm-dd hh:mm:ss.
without colon after year i am getting the above format with text and datevalue function.
but with colon i am not getting required format. I want to write the formula with text or some other functions.
Please tell me how to remove colon.
Administrative note
Welcome to the forum
in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?
Please take a moment to read it and attach a sheet accordingly.
Thanks you for helping us help you
Try:
=DATEVALUE(LEFT(A1,11))+TIMEVALUE(RIGHT(A1,11))
With A1 is date-string
Quang PT
alternatively
=SUBSTITUTE(A1,":","",1)+0
should be:Originally Posted by XLent
=SUBSTITUTE(A1,": "," ",1)+0
to prevent time-string changing
No - I am afraid that assertion is incorrect; the use of 1 as optional fourth parameter ensures only the first semi-colon is replaced, the time serial is completely unaffected.
Last edited by XLent; 11-26-2020 at 04:45 AM. Reason: reworded
Thanks for your reply..
can we pass required date format with DATEVALUE like yyyy-mm-dd hh:mm:ss
Custom format the cell or using
=TEXT("Working_formula","yyyy-mm-dd hh:mm:ss")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks