Format of date

[ARowbot]

Hi All,

I have an automatically generated workbook with the name of the form:

"rpttemp" and then the date in the format yyyymmddhhmmss.

For Example: "rpttemp20170607070000" For 7 am on the 7th June 2017

I need to reformat this so I can use it to say in another cell:

"Last updated: 07/06/2016 - 07:00:00"

Many Thanks for your help.

ARowbot

[Winon]

Hello ARowbot,

Select the Cell/s where you want the desired format, right Click with the mouse within that selection, then Select Custom, and enter dd-mmm-yyyy - hh:mm:ss

Regards.

[leelnich]

Hi all- not sure if this is what you're after, but this in Display cell (Reading "rpttemp20170607070000" from A4):
="Last updated: "& MID(A4,14,2)& "/"&MID(A4,12,2)& "/"& MID(A4,8,4)&" - "& MID(A4,16,2)&":"&MID(A4,18,2)&":"&MID(A4,20,2)

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[ARowbot]

Thanks all, but the date is the name of a workbook and not in a cell. I am trying to take the name of the workbook and put it in the new format in a different workbook using VBA.

[leelnich]

Like this?

Please Login or Register  to view this content.

This will work if the name is a consistent length. If not, we can count back from the end.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[leelnich]

To count back from the end:
With the file extension (".xlsx")

Please Login or Register  to view this content.

or without the file extension

Please Login or Register  to view this content.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[Norie]

Using Format and conversion functions.

Please Login or Register  to view this content.

[ARowbot]

All work brilliantly but definitely like the use of CDate which I did not know of before!

Thanks all.