Hey, Scripting Guy! In Microsoft Outlook if you sort and group your email by date, you don’t just get groupings that show the date; instead, you get groupings like Today, Yesterday, Last Week, etc. I’d like to do something similar; I’d like to look at the last-modified date for all the files in a folder, then report back that date as one of the following values: Today; Yesterday; This Week; Last Week; Older. Can you help me with this?
— DA

Hey, DA. You know, this past weekend the Scripting Guy who writes this column actually went to the movies: he ventured out to see Indiana Jones and the Kingdom of the Crystal Skull. (Incidentally, seeing this movie keeps him right on his usual pace: in 2007 he saw two movies, and now in 2008 he’s seen one movie.) As far as the Scripting Guy who writes this column was concerned, the latest Indiana Jones movie was OK: it was entertaining, but ultimately pointless. But that’s fine; after all, the Scripting Guy who writes this column is also a Seattle Mariner fan, and what are Mariner games if not entertaining but ultimately pointless?

Interestingly enough, the movie – which, at the risk of giving away the plot, involves the search for a crystal skull – is based on fact: there really are crystal skulls (including a very nice one in the British Museum). Furthermore, a number of people believe that these skulls possess magical powers. According to one legend, there are 13 crystal skulls scattered about the world. If and when all 13 skulls are found and reunited (supposedly 8 of them have been found so far) – uh, well, something cool will happen, although no one is really quite sure what will happen. However, the consensus seems to be that, when reunited, the skulls will “ … form a grid matrix (reality is a consciousness holographic matrix) aligned with the 13th skull, allowing human consciousness to ascend (return) to its natural state of being, light.” Or, as Frank Dorland once said, “[The] crystal stimulates an unknown part of the brain, opening a psychic door to the absolute.”

Which, believe it or not, actually makes more sense than the ending of the movie did.

Needless to say, crystal skulls aren’t all that easy to come by. Fortunately, there’s a cheaper way to open a psychic door to the absolute; all you have to do is take a peek at the following script, a script that reports back file dates using labels like Today, Yesterday, and Last Week. Enjoy your trip to the psychic absolute:

dtmToday = Date
dtmFileDate = #5/24/2008#

intDaysDifference = DateDiff("d", dtmFileDate, dtmToday)

If intDaysDifference = 0 Then
    Wscript.Echo "Today"
    Wscript.Quit
End If

If intDaysDifference = 1 Then
    Wscript.Echo "Yesterday"
    Wscript.Quit
End If

strDayOfWeek = WeekdayName(WeekDay(dtmToday))

If strDayOfWeek = "Sunday" Then
    If intDaysDifference <= 7 Then
        Wscript.Echo "Last week"
    Else
        Wscript.Echo "Older"
    End If
End If

If strDayOfWeek = "Monday" Then
    If intDaysDifference <= 8 Then
        Wscript.Echo "Last week"
    Else
        Wscript.Echo "Older"
    End If
End If

If strDayOfWeek = "Tuesday" Then
    If intDaysDifference <= 2 Then
        Wscript.Echo "This week"
    ElseIf intDaysDifference <= 9 Then
        Wscript.Echo "Last week"
    Else
        Wscript.Echo "Older"
    End If
End If

If strDayOfWeek = "Wednesday" Then
    If intDaysDifference <= 3 Then
        Wscript.Echo "This week"
    ElseIf intDaysDifference <= 10 Then
        Wscript.Echo "Last week"
    Else
        Wscript.Echo "Older"
    End If
End If

If strDayOfWeek = "Thursday" Then
    If intDaysDifference <= 4 Then
        Wscript.Echo "This week"
    ElseIf intDaysDifference <= 11 Then
        Wscript.Echo "Last week"
    Else
        Wscript.Echo "Older"
    End If
End If

If strDayOfWeek = "Friday" Then
    If intDaysDifference <= 5 Then
        Wscript.Echo "This week"
    ElseIf intDaysDifference <= 12 Then
        Wscript.Echo "Last week"
    Else
        Wscript.Echo "Older"
    End If
End If

If strDayOfWeek = "Saturday" Then
    If intDaysDifference <= 6 Then
        Wscript.Echo "This week"
    ElseIf intDaysDifference <= 13 Then
        Wscript.Echo "Last week"
    Else
        Wscript.Echo "Older"
    End If
End If

To begin with, don’t let the size of this script scare you away; as you’re about to see, much of it is duplicate code. The truth is, we probably could have cut this script down by half (at least). So then why didn’t we do that? For this reason: because gazing at the entire script, just like gazing at a crystal skull, causes “ … the eyes [to] set up a harmonic relation stimulating the magnetism collected in that portion of the brain known as the cerebellum. The cerebellum therefore becomes a reservoir of magnetism which influences the quality of the magnetic outflow through the eyes, thus setting up a continuous flow of magnetism between gazer and crystal.”

Oh, and also because we wanted to make it very clear exactly what we were doing and how we were doing it. Therefore, we sacrificed a little brevity for a little more clarity

We should also note that, for demonstration purposes, we’ve hard-coded a single date into the script, and that’s the only date the script actually works with. But don’t panic: before we go we’ll show you an expanded version of the script, one that can actually work with all the files found in a specified folder.

For now, however, our script kicks off by using the Date function to retrieve the current date and store that value in a variable named dtmToday:

dtmToday = Date

Once that’s done we assign a hard-coded date (May 24, 2008) to a variable named dtmFileDate. And in case you’re wondering, yes, this is the variable that – in our expanded script, not to mention our new, expanded consciousness – will be used to hold the last-modified date of each file.

But first things first. Once we have the two date-time values we next use VBScript’s DateDiff function to determine the number of days (“d”) between the two dates:

intDaysDifference = DateDiff("d", dtmFileDate, dtmToday)

That brings us to this block of code:

If intDaysDifference = 0 Then
    Wscript.Echo "Today"
    Wscript.Quit
End If

What we’re doing here is checking to see if the number of days between the two dates is 0. Why do we do that? Well, if the difference is 0 that can mean only one thing: the two dates are the same (ignoring the time of day, of course). Therefore, that must mean that this file was last modified sometime today. With that in mind, we echo back the message Today, then use the Quit method to terminate the script.

And why do we terminate the script? You got it: having already determined that the file was last modified today there’s no need to do anything else. As they say, it’s time to get on with our lives.

Which is far more true than you might think. At least for the Scripting Guys.

Now, what if the difference between the two dates is not zero? No problem; in that case, we next check to see if the difference is 1:

If intDaysDifference = 1 Then
    Wscript.Echo "Yesterday"
    Wscript.Quit
End If

We do that because – well, you’re way ahead of us on this one, aren’t you? And you’re right: if the difference is just 1 day that must mean that this script was last modified 1 day ago (i.e., yesterday). Consequently we echo a message to that effect, then exit the script.

Now it starts to get just a little bit complicated. How do things start to get a little bit complicated? Well, suppose today is Wednesday (which it is). If you just rush blindly into this, without thinking it over, you might think that, to determine if a file was last modified this week, you could simply check to see if the day difference between the two dates is less than 7. (So did the Scripting Guys just blindly rush into this without thinking it over? Do you really need to ask?) If the day difference between the two dates is less than 7 then, logically enough, the file must have last been modified this week, right?

Wrong. Why? Well, suppose the difference in days between the two files is 4 (which, for our purposes, it is). In that case, that means that the file was last modified on Saturday, May 24, 2008. And guess what? If we assume that Sunday is the first day of the week, that means that the last modification took place last week. Granted, the file is less than 7 days old, but because the new week started on Sunday, well ….

In other words, it’s not good enough just to know the difference in days between the two dates, we also have to know the current day of the week; as it turns out, both of those factors are used to determine whether a file was last modified this week, last week, or some previous time.

See? We told you it was going to get a little bit more complicated.

But have no fear: we figured out at least one way to deal with this issue. To begin with, we use this line of code to determine the current day of the week:

strDayOfWeek = WeekdayName(WeekDay(dtmToday))

And no, your eyes are not deceiving you: we do have a function embedded within a function here. In this line of code we first use the Weekday function to return an integer value representing the current day of the week (1 for Sunday; 2 for Monday; 3 for Tuesday; etc.). We then use the WeekdayName function to convert that integer value into a recognizable string (e.g., Sunday, Monday, Tuesday, etc.). Finally, that string value gets stashed in the variable strDayOfWeek.

So what do we do now that we know the current day of the week? Well, what follows are a series of seven If Then statements, each one designed to work against a particular day of the week. For example, here’s the If Then block for Sunday:

If strDayOfWeek = "Sunday" Then
    If intDaysDifference <= 7 Then
        Wscript.Echo "Last week"
    Else
        Wscript.Echo "Older"
    End If
End If

As it turns out, doing our calculations for Sunday (and for Monday, for that matter) is easy: that’s because we don’t have to worry about files having been modified this week. Why not? Well, because Sunday is the first day of the week the only way a file could be listed as This week would be if the file was last modified on that very same Sunday. However, if the file was modified on the same exact Sunday then we’ve already marked it as Today and we’ve already exited the script. Therefore, all we have to do is check to see if the days difference between the two dates is less than or equal to 7:

If intDaysDifference <= 7 Then

If it is, then the file was last modified last week. If not, then we mark the file as Older. See how that works?

Now let’s take a look at the If Then block for Wednesday:

If strDayOfWeek = "Wednesday" Then
    If intDaysDifference <= 3 Then
        Wscript.Echo "This week"
    ElseIf intDaysDifference <= 10 Then
        Wscript.Echo "Last week"
    Else
        Wscript.Echo "Older"
    End If
End If

So what are we doing here? Well, for starters, we’re checking to see if the day difference between the two dates is less than or equal to 3; if it is, that means the file was last modified this week. How do we know that? Because of this handy-dandy chart we made up for ourselves:

If intDaysDifference is 3 or less, then the file was last modified this week.

OK, but what if intDaysDifference is not 3 or less? Well, then we check to see if the value is 10 or less:

ElseIf intDaysDifference <= 10 Then

Why 10 or less? Because that would mean that the file was last modified last week, just like another of our handy-dandy charts indicates:

And, of course, we make similar checks for the other days of the week, adjusting the values within the If Then blocks as needed.

Yes, we know. But if you’re having trouble seeing how this all works pick another day (say, Thursday) and try making a couple handy-dandy charts of your own. Once you do that you should start to see how this all works.

Now, what about that script that can calculate this date information for all the files in a folder? Well, here’s a hunk of code that reports back the file name, last-modified date, and our little date-time calculation for each and every file in the folder C:\Scripts. Give it a try and see what happens:

dtmToday = Date

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFOlder = objFSO.GetFOlder("C:\Scripts")

For Each objFile in objFOlder.Files
    dtmFileDate = objFile.DateLastModified
    intDaysDifference = DateDiff("d", dtmFileDate, dtmToday)

    If intDaysDifference = 0 Then
        Wscript.Echo objFile.Name & " -- Today"
    
    ElseIf intDaysDifference = 1 Then
    Wscript.Echo objFile.Name & " -- Yesterday"

    Else
        strDayOfWeek = WeekdayName(Weekday(dtmToday))

        If strDayOfWeek = "Sunday" Then
            If intDaysDifference <= 7 Then
                Wscript.Echo objFile.Name & " -- Last Week"
            Else
                Wscript.Echo objFile.Name & " -- Older"
            End If
        End If

        If strDayOfWeek = "Monday" Then
            If intDaysDifference <= 8 Then
                Wscript.Echo objFile.Name & " -- Last Week"
            Else
                Wscript.Echo objFile.Name & " -- Older"
            End If
        End If
        
        If strDayOfWeek = "Tuesday" Then
            If intDaysDifference <= 2 Then
                Wscript.Echo objFile.Name & " -- This Week"
            ElseIf intDaysDifference <= 9 Then
                Wscript.Echo objFile.Name & " -- Last Week"
            Else
                Wscript.Echo objFile.Name & " -- Older"
            End If
        End If

        If strDayOfWeek = "Wednesday" Then
            If intDaysDifference <= 3 Then
                Wscript.Echo objFile.Name & " -- This Week"
            ElseIf intDaysDifference <= 10 Then
                Wscript.Echo objFile.Name & " -- Last Week"
            Else
                Wscript.Echo objFile.Name & " -- Older"
            End If
        End If

        If strDayOfWeek = "Thursday" Then
            If intDaysDifference <= 4 Then
                Wscript.Echo objFile.Name & " -- This Week"
            ElseIf intDaysDifference <= 11 Then
                Wscript.Echo objFile.Name & " -- Last Week"
            Else
                Wscript.Echo objFile.Name & " -- Older"
            End If
        End If

        If strDayOfWeek = "Friday" Then
            If intDaysDifference <= 5 Then
                Wscript.Echo objFile.Name & " -- This Week"
            ElseIf intDaysDifference <= 12 Then
                Wscript.Echo objFile.Name & " -- Last Week"
            Else
                Wscript.Echo objFile.Name & " -- Older"
            End If
        End If

        If strDayOfWeek = "Saturday" Then
            If intDaysDifference <= 6 Then
                Wscript.Echo objFile.Name & " -- This Week"
            ElseIf intDaysDifference <= 13 Then
                Wscript.Echo objFile.Name & " -- Last Week"
            Else
                Wscript.Echo objFile.Name & " -- Older"
            End If
        End If
    End If
Next

And there you have, DA: that’s how you can report back last-modified dates using labels like Today, Yesterday, and Last Week. As for crystal skulls, however, well, we can’t really help you there: we don’t know how to find them, and we definitely don’t know how to make them. About all we do know is that, according to a somewhat-controversial analysis, the famed Mitchell-Hedges crystal skull (the Skull of Doom!) “… must have taken over 150 years [to make], generation after generation working all the days of their lives, patiently rubbing down with sand an immense block of rock crystal until finally the perfect Skull emerged.”

Needless to say, 150 years is a long time. And yet these ancient craftsmen still managed to get their crystal skull done faster than the Scripting Guys could write Part III of their HTA tutorial. Wow; maybe the skulls really were made by extraterrestrials, or by some highly-advanced but long-lost civilization.

Or maybe these ancient craftsmen just weren’t as lazy as the Scripting Guys.