Date within file name in cell

[Slade]

Hi

I am tying to insert a date from another cell on another sheet to a cell that contains a filename that must have a date. it does get the value but changes it to a random number. how can i fix this?

Sheet1, Cell A1 is the date: 20110804
Sheet2, Cell A1 is the filename with date:

Formula is:"=IA_Hoops__"&DATE!$B$3&".csv"
Value that i get is: ="IA_Hoops__40759.6666666667.csv"
The "40759.6666666667" is suppose to be the date from A1 sheet1

Tnx

 

[CWatts]

The reason is because the date is formatted as a serial date in excel. By default it will pass as the numerical value. You have to manually specify the output format, in your case,

Replace:
="IA_Hoops__"&DATE!$B$3&".csv"

With:

="IA_Hoops__"&TEXT(Date!$B$3,"yyyymmdd")&".csv"

 

[Slade]

Works 100% tnx allot

 

[Slade]

Another question.

Let me explain my problem.

I use file names to import the date and size to excel. All work well. But now the problem is some of the files change date and time.

The first part of the file name always stay the same but the last part is the date and time. It look like this "Filename_20110804193605.txt"

20110804193605
yyyy/mm/dd/hh/mm/ss

This changes each day depending on the time the file arrives. I have the date sorted so i just need to add the time.

So i want to do something like this:
="Filename_20110804&(GetFileDateTime).txt"

Will this be possible?

Tnx

 

[CWatts]

You can use the following functions.

Assuming your file name and directory structure will always be identical this one would work well.

=MID(CELL("filename"),48,17)&MID(CELL("filename"),71,5)

You just have to adjust the numbers of the MID function to suit your needs.

Otherwise, you can use this overkill formula as a one-size-fits all. It's a bit overkill, but if your directory path keeps changing, then it would work.

=LEFT(MID(CELL("filename"),FIND("[",CELL("filename"),1)+1,FIND("]",CELL("filename"),1)-FIND("[",CELL("filename"),1)-1),24-7)&RIGHT(MID(CELL("filename"),FIND("[",CELL("filename"),1)+1,FIND("]",CELL("filename"),1)-FIND("[",CELL("filename"),1)-1),LEN(MID(CELL("filename"),FIND("[",CELL("filename"),1)+1,FIND("]",CELL("filename"),1)-FIND("[",CELL("filename"),1)-1))-FIND(".",MID(CELL("filename"),FIND("[",CELL("filename"),1)+1,FIND("]",CELL("filename"),1)-FIND("[",CELL("filename"),1)-1),1)+1)

 

[Slade]

Howzit. Thanks for the reply.

The file name is and in root so it is C:\dir20110808075125.txt

Ok i tried this because the "dir" always stay the same but date and tiem change every day.
=MID(CELL("C:\dir*"),4,11)&MID(CELL("C:\dir*"),12,25)

and got only #VALUE

Then this: to see if i put the whole file name if it will work.

=MID(CELL("C:\dir20110808075125.txt"),4,11)&MID(CELL("C:\dir20110808075125.txt"),12,17)

and only still got #VALUE

Do i need to add soem vb script for this like when i need to when i do a GetFileDate?

Tnx

 

[AdityaTyagi]

="Filename_"&TEXT(Date!B3,"yyyymmdd")&TEXT(Date!B3,"hhmmss")

 

[Slade]

="Filename_"&TEXT(Date!B3,"yyyymmdd")&TEXT(Date!B3,"hhmmss")

This works well if the file name stays the same but my problem is the date and time changes every day when the file is recieved.

I use this to monitor when a file is recieved. so excel updates every 5min to check a server for files and some of the stay exactly the same always but 5 or so comes in with the first part of the file name always the same but the rest is the date and time. so i need a way for excel to first import the file name and then i can get the date and time i want.

 

[AdityaTyagi]

In #4(post count),you wrote that filename is fixed.
any way will you please tell me wat is input in this case

 

[Slade]

the input meaning where i get the file?