VBA file dates in Julian format

Posted on 2013-09-30
Medium Priority
Last Modified: 2013-10-01
In VBA - Could anyone provide examples of how to get the julian dates (datelastmodified) of 2 files so those julian dates can be compared to see which file is older and newer?
Question by:tesla764
LVL 15

Assisted Solution

unknown_routine earned 400 total points
ID: 39534259
Here how you convert the date of files ro julian dates:

Dim jd As Double ' julian day
  Dim mDate As Date ' your date
  mDate = Now()
 jd = mDate.ToOADate


To get date of the files:

Dim File1 As new System.IO.FileInfo

Dim strLastModified As String
strLastModified = System.IO.File.GetLastWriteTime(strFilePath & File.ToString()).ToShortDateString()

Author Comment

ID: 39534709
I will try that 1st thing tomorrow. Thanks.
LVL 35

Accepted Solution

[ fanpages ] earned 1600 total points
ID: 39535245

In Visual Basic for Applications, you don't need to convert two "Last Modified" dates to Julian Date values to compare then.

Simply format each in [yyyymmddhhmmss] Format, using the Format$() statement.

Here are two examples of usage, as well as a third method using the DateDiff() function, a fourth utilising a Function that converts the two dates into Julian Date values (as you originally requested), & a fifth simply subtracting one Date (data type) from the other...

Public Sub Q_28253825()

  Dim datLastModified1                                  As Date
  Dim datLastModified2                                  As Date
  datLastModified1 = DateValue("30/09/2013") + TimeValue("11:30:23")
  datLastModified2 = DateValue("01/10/2012") + TimeValue("02:26:14")
' First method [using Format$()]...

  If Format$(datLastModified1, "yyyymmddhhmmss") > Format$(datLastModified2, "yyyymmddhhmmss") Then
     MsgBox "datLastModified1 is later than datLastModified2 [using Format$()]"
  End If
' Second method [again, using Format$()] showing alternate approach...

  Select Case (Format$(datLastModified1, "yyyymmddhhmmss"))
      Case Is > Format$(datLastModified2, "yyyymmddhhmmss")
          MsgBox "datLastModified1 is later than datLastModified2 [also using Format$()]"
      Case Is < Format$(datLastModified2, "yyyymmddhhmmss")
          MsgBox "datLastModified1 is earlier than datLastModified2 [also using Format$()]"
      Case Else
          MsgBox "datLastModified1 is equal to datLastModified2 [also using Format$()]"
  End Select
' Third method [using DateDiff() to compare the number of seconds between each date]...

  If DateDiff("s", datLastModified2, datLastModified1) > 0& Then
     MsgBox "datLastModified1 is later than datLastModified2 [using DateDiff()]"
  End If
' Fourth method [using conversion to Julian Dates via the strJulian_Date() function]...

  If strJulian_Date(datLastModified1) > strJulian_Date(datLastModified2) Then
     MsgBox "datLastModified1 is later than datLastModified2 [using strJulian_Date()]"
  End If
' Finally, a fifth method [simply subtract one Date from the other]...

  If datLastModified1 - datLastModified2 > CDate(0&) Then
     MsgBox "datLastModified1 is later than datLastModified2 [using subtraction]"
  End If

End Sub
Function strJulian_Date(ByVal datDate As Date) As String

  strJulian_Date = Right$(Format$(Year(datDate), "0000"), 2) & _
                   Format$(DateDiff("d", DateSerial(Year(datDate), 1, 0), datDate), "000")

End Function

Open in new window



Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
An ASP.NET Web Form User Control is not newly introduced in ASP.NET. In fact, it was an old technology yet still playing a role to generate web content, especially when we want to use it to have a better and easy way to control part of the web conte…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Watch the video to know how one can repair corrupt Exchange OST file effortlessly and convert OST emails to MS Outlook PST file format by using Kernel for OST to PST converter tool. It can convert OST to MSG, MBOX, EML to access them. It can migrate…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question