LinkBack URL
About LinkBacks
Hi,
I have a problem regarding the formatting of my cells. I have values in the form of 01.02.2020 08.00.07 in 1500 cells. How can I change all of these into 01.02.2020 08:00:07?
Thanks!
Hi,
I have a problem regarding the formatting of my cells. I have values in the form of 01.02.2020 08.00.07 in 1500 cells. How can I change all of these into 01.02.2020 08:00:07?
Thanks!
Assuming that is in A2, you can use this formula in B2:
=LEFT(A2,11)&SUBSTITUTE(RIGHT(A2,8),".",":")
and copy down as required. Note that it is a text value.
Hope this helps.
Pete
Thanks for your reply! Now the text formatting is kinda problematic because I need to count with these. Sorry for not mentioning this earlier.
=(SUBSTITUTE(LEFT(A2,11),".","/")&SUBSTITUTE(RIGHT(A2,8),".",":"))*1 may work or
=DATEVALUE((SUBSTITUTE(LEFT(A2,11),".","/")))+TIMEVALUE(SUBSTITUTE(RIGHT(A2,8),".",":"))
however as we can not see any sample data to see if there are inconsistencies in the data. I think you are also wanting to change the underlying value so it becomes date time, which isn't the format. It goes without saying the column needs to be formed as dd/mm/yyyy hh:mm:ss to display as you wish
Last edited by davsth; 10-18-2021 at 05:48 AM.
Hi! My data has no inconsistencies. I used the first function and it works, but it displays only the time. That is okay though so its fine. Thanks!
what have you formatted it as ? dd/mm/yyyy or dd/mm/yyyy hh:mm:ss
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks