Date format challenge

Question

. . . so I cut & pasted the code from https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/datecreated-property

and it works just FINE.

It gives me the creation date & time of any file.

BUT

I don't know what "format" it is in, because, having used that routine to acquire "FileDateStamp", this doesn't work :-

    FileAge = Application.Function.Now() - FileDateStamp

I have tried these :-

    Dim FileDateStamp 
    Dim FileDateStamp as Date
    Dim FileDateStamp as Text

Each format provides the datestamp, but none works in the formula. And neither does the "Date(Year, Month, Day)" function on FileDateStamp

FileAge = Now - FileDateStamp. Use Format$ afterwards to format into something readable. — BigBen
– BigBen, Commented Sep 3, 2021 at 18:09
"this doesn't work" - in what way does it not work? Application.Function.Now() is not a thing (at least in VBA), but just Now() works fine, so Now - FileDateStamp gives you file age in days. — Tim Williams
– Tim Williams, Commented Sep 3, 2021 at 18:41
@TimWilliams It is not clear if you are saying that it is a date without a time component, or a datetime, which it is. — Andrew Morton
– Andrew Morton, Commented Sep 3, 2021 at 18:46
The Date datatype includes time - there aren't two separate datatypes. There might not be any fractional component in an assigned value though. You can always Debug.Print 1 * FileDateStamp to see the underlying numeric value. — Tim Williams
– Tim Williams, Commented Sep 3, 2021 at 18:53

Andrew Morton · Accepted Answer · 2021-09-03 19:43:20Z

2

It isn't clear what you're asking for, but I think this is what you might be looking for.

At the time of writing, the UTC datetime is 2021-09-03 18:54:12. The UTC creation of the file in the code is 2020-06-12 14:04:50 (448 days ago).

Using the DateDiff function:

Function GetFileDateTime(filespec)
    Dim fs, f, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(filespec)
    GetFileDateTime = f.DateCreated
End Function

Sub ShowSampleDiff()
    Dim fname As String
    fname = "C:\temp\asp.txt"
    Dim diff As Long
    diff = DateDiff("d", GetFileDateTime(fname), Now())
    MsgBox (diff)
End Sub

The code shows a message box with the text "448".

edited Sep 3, 2021 at 19:43

answered Sep 3, 2021 at 18:35

Andrew Morton

25.3k9 gold badges66 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

RobinClay Over a year ago

Thank you ! But I don't have the code for the "DateDif" Function

Andrew Morton Over a year ago

@RobinClay In my answer I refer to the "DateDiff" function, not "DateDif" with one "f".

Gustav Over a year ago

This will not return the age of the file, only the difference in calendar dates, as the timeparts will be ignored.

RobinClay Over a year ago

@ Andrew Morton Sticky keyboard, sorry. Yes, "DateDiff" with two Fs

RobinClay Over a year ago

@ Gustav - good enough for MY purpose - but for "completeness" how would you solve that ?

|

Ron Rosenfeld · Accepted Answer · 2021-09-03 19:39:57Z

1

You are not clear in what you want.

This works to return the number of days (and fractional days)

    Dim FileDateStamp As Date    
    FileDateStamp = f.DateCreated
    s = "Created: " & FileDateStamp
    
    MsgBox s & vbLf & Now - FileDateStamp

For a particular file, the above returns:

Depending on what you want for output, you could format the result of:

FileAge = Now - FileDateStamp

edited Sep 3, 2021 at 19:39

answered Sep 3, 2021 at 19:28

Ron Rosenfeld

62.1k7 gold badges35 silver badges72 bronze badges

3 Comments

RobinClay Over a year ago

Thank you - but the "challenge" is to find the age of the file, rather than being given the two dates. e.g. That could be followed by code along the lines of "If the file is more than a week old, delete it." i.e.. If FileAge > 7 then delete file

Ron Rosenfeld Over a year ago

@RobinClay Huh? Silly me. I would have thought the age of the file in Days would be sufficient. What is the problem with you converting the days difference into an "age"?

RobinClay Over a year ago

That WAS exactly my problem ! But "all sorted now". Thanks for your help.

Gustav · Accepted Answer · 2021-09-04 06:25:18Z

0

It returns a value of data type Date.

However, you can use VBA to reduce to a minimum:

Dim FileSpec As String
Dim FileAge  As Double
Dim TextAge  As String

FileSpec = "d:\path\filename.ext"
FileAge = VBA.Now - VBA.FileDateTime(FileSpec)

' Optionally, format as days.hours:minutes:seconds
TextAge = VBA.Fix(FileAge) & "." & VBA.Format(FileAge - VBA.Fix(FileAge), "hh:nn:ss")

Debug.Print FileAge
' for example: 349.739988425928
Debug.Print TextAge
' for example: 349.17:45:35

edited Sep 4, 2021 at 6:25

answered Sep 3, 2021 at 20:36

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

6 Comments

RobinClay Over a year ago

Thank you ! But I don't have the Functions "CLng" nor "Format"

Tim Williams Over a year ago

@RobinClay - you might find following one or two tutorials on VBA useful - both CLng and Format are built-in.

Gustav Over a year ago

I don't have the Functions "CLng" nor "Format" ... yes you do. They belong to VBA.

Gustav Over a year ago

As you seem to require only a numeric age of the file, the solution can be reduced further. See edited answer, please.

RobinClay Over a year ago

Well . . I did suspect that I did have those two functions, so I tried typing them in - but they just produced an error.

|

Collectives™ on Stack Overflow

Date format challenge

3 Answers 3

6 Comments

3 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related