Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Obtain file date in Excel (Read 4844 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Obtain file date in Excel
24.08.10 at 16:03:56
Obtain file date in Excel
I am interested in file date information from various directories and can only get it in certain formats with DOS commands. I have approximately 40,000 files that I'm trying to obtain creation and last modified date information to enable a sort by date ability.

The FileDateTime function in LitLib returns the date and time of when a file was created or last modified.  
I have provided an example below how to use this function in Excel.
  • Obtain a directory listing in Excel
    In A1 type: 1
    In B2 type: =Dir("c:\windows\*.*",A1)
    This will retrieve the first file in the list of files selected with c:\windows\*.*
  • Add the second rows in the listing
    In A2 type: =A1+1
    Copy cell B1 to B2
    This will retrieve the second file in B2
  • Add more rows in the listing
    Select A2:B2 and paste it into a block below, e.g. B3:B10
    You get a list like this:
    1      0.log
    2      Alcmtr.exe
    3      alcwzrd.exe
    4      Blauw 16.bmp
    5      clock.avi

  • Retrieve the file date in column C:
    In C1 type: =FileDateTime("c:\windows\" & B1)
    This will get the file date and time from the file displayed in B1
    Format C1 as a date/time or use a format mask like this: dd-mmm-yyyy hh:mm:ss
  • Copy C1 down to the cells C2:C10
    You may get a list like this:

    1      0.log      14-Jun-2010 19:52:03
    2      Alcmtr.exe      03-May-2005 12:43:28
    3      alcwzrd.exe      04-May-2006 10:26:36
    4      Blauw 16.bmp      04-Aug-2004 14:00:00
    5      clock.avi      04-Aug-2004 14:00:00

It is really that simpel. Just get a trial version and try it yourself:
litLIB project page
Back to top
« Last Edit: 24.08.10 at 16:13:05 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1