Timestamp

[Cheryl Richter]

I know there are questions about timestamp out there and I'm NOT trying to create one. I have over 700 excel files where the time is listed in the filename, but not in the document. We are going to merge all of them, but then the dates are 'lost'.

Is there any tip or trick that could help me pull the time from the filename and insert it into the document itself? Repeating for each column with data?

Example: fileone2018-01-01

Product code Date
123456 2018-01-01
456789 2018-01-01


Thank you.

Cheryl

 

[Chihiro]

Hi, welcome to the forum.

Please have a read of below link. It will help you in asking questions in a way that will get you faster and better answers.
https://chandoo.org/forum/threads/new-users-please-read.294/
https://chandoo.org/forum/threads/posting-a-sample-workbook.451/#post-73705

Your post does not give enough details for me to answer your question.

How are you merging all files?
Via ADO, PowerQuery, MSQuery, or some other method?

How is your merged output organized and structured?
etc.

 

[p45cal]

There is, but how are you going about doing the merging? Manually?, with macros?
Usually the name of an Excel file ends with an extension such as .xlsx or some such. With a bit of string manipulation we can determine the date (and time?) stamp from the file.
For example, if you had your file name in cell G5 then
=MID(G5,FIND(".xl",G5)-10,10)
would give you a string that looks like a date and
=DATEVALUE(MID(G5,FIND(".xl",G5)-10,10))
would give you a real excel date (a number) which when formatted as a date would show that date properly.

 

[Cheryl Richter]

I appreciate the help Chihiro. Please don't get hung up on the merging.
Thanks to other posts - I know how to do that. All I need right now is how to pull the date portion of a file name into a column in that file.

I was hoping I wouldn't have to open each and every file and add the date. If that's what I have to do, I can but it's going to take a lot of time.

Since the file name is NOT in the file, I think that might be what I need to do.
Thanks!

 

[GraH - Guido]

Hi Cheryl Richter

I strongly advice to use a Power Query solution for this (available in Excel as from version 2010). You can recuperate filenames with ease and split it based on x-last characters and neat stuff like that, while having in the other table columns the merged (content) of the +700 files. Given they all have the same layout/structure.

I made something where these 3 files are appended (to use the PQ transformations' name):


I've attached the samples and PQ. I started the PQ from File-Folder. So I might just work of you just change the folder name in the source step of the Power Query. Press the gear icon for that.


If you need further help, let us know.

Kr
Guido

 

[Chihiro]

You can do this via code, or using PowerQuery (as Guido has shown above) without issue.

Hence my question on how the data is merged. Without that info we can't give you the solution that fits your need.

 

[p45cal]

Hence my question on how the data is merged. Without that info we can't give you the solution that fits your need.

Hear hear.

 

[Charlie Day]

I'm posting my question here since I didn't wanted to create a new thread on a similar topic.

I came across this post "https://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/" on chandoo. =IF(C3<>"",IF(B3="",NOW(),B3),"") is the formula Chandoo suggested and it works well except when you change the value in the cell (similar question was posted by one of the readers 'Oliver': "but I also would like to change the timestamp in case someones modifies the cell. Is that possible without VBA?"
Now this post was made on 2009 and I'm wondering if anyone was able to find a solution to this problem. Thanks