Avatar of Derek Brown
Derek Brown
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Date Format

This is very strange

When I use NewDate = CDate(CDbl(ItemDateCreated1) + CDbl(ItemDateCreated2) / 2)

Where both dates are 2019 I get 2079

If I use NewDate = CDate((ItemDateCreated1+ ItemDateCreated2) * 0.5)  I get correct result. Both dates are formatted correctly

I want to use double because it allows me to split seconds
Microsoft Access* AccessVBA

Avatar of undefined
Last Comment

8/22/2022 - Mon
John Tsioumpris

Can you give  a complete sample ?
John Tsioumpris

Check this :

Open in new window

Dale Fye

Can you explain what you are actually trying to do?  Adding dates is a bit strange.

I can understand adding a specific number of days, weeks, months, or years to another date, but performing arithmetic on two dates, doesn't make a lot of sense.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Anders Ebro (Microsoft MVP)

In the first example you are only dividing the LAST date by two, but in the last example you are dividing both dates. Thats why the calculations are off.

NewDate = CDate((CDbl(ItemDateCreated1) + CDbl(ItemDateCreated2)) / 2)

Open in new window

@Dale: He is trying to get the midpoint between the 2 dates.

Or in other words,parentheses... As you don't need CDbl(), cause the + operator does this for you:

NewDate = CDate((ItemDateCreated1 + ItemDateCreated2) / 2)
' or
NewDate = CDate(ItemDateCreated1 / 2 + ItemDateCreated2 / 2)

Open in new window

Derek Brown

Can I be sure that fractions of a second are being used rather than rounded to the nearest second with this format?
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

Well, it rounds for displaying it. Otherwise, not.

Option Explicit

Public Sub Test()

  Const NUMBER_FORMAT As String = "0.0000000000"

  Dim count As Long
  Dim doubleTime As Double
  Dim dateTime As Date
  Dim tenthSecond As Double
  dateTime = CDate(0)
  tenthSecond = CDbl(DateAdd("s", 1, CDate(0))) / 10#
  Debug.Print "tenthSecond:", Format(tenthSecond, NUMBER_FORMAT)
  For count = 0 To 9
    doubleTime = doubleTime + tenthSecond
    Debug.Print Format(doubleTime, NUMBER_FORMAT), Format(CDbl(CDate(doubleTime)), NUMBER_FORMAT), Format(CDate(doubleTime), "hh:mm:ss")
  Next count
End Sub

Open in new window

Derek Brown

Thanks Ste5an

That's very helpfull. I do get the milliseconds. So only thing now is:

If a date field is used for sort order in a query it will use the millisecond element and not just work to the displayed element rounded to seconds?
Anders Ebro (Microsoft MVP)

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Derek Brown

Thank all.

Correct bracketing was wrong
Correct Did not need CDbl

What is the format in a query grid that will show milliseconds?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Dale Fye

NewDate = DateValue(ItemDateCreated1) + DateDiff("d", ItemDateCreated1, ItemDateCreated2)/2

What is the format in a query grid that will show milliseconds?
You need a custom format function. E.g.

Option Explicit

Private Type TypeSystemTime
  Year As Integer
  Month As Integer
  DayOfWeek As Integer
  Day As Integer
  Hour As Integer
  Minute As Integer
  Second As Integer
  Millisecond As Integer
End Type

Private Declare Sub GetSystemTime Lib "kernel32" (lpSystemTime As TypeSystemTime)

Public Sub Test()

  Dim TimeAsString As String
  Dim TimeAsDate As Date

  TimeToMillisecond TimeAsString, TimeAsDate
  Debug.Print TimeAsString, TimeAsDate, FormatWithMilliseconds(TimeAsDate)
End Sub

Public Function FormatWithMilliseconds(ByVal CValue As Variant, Optional ByVal CBaseFormat As String = "hh:nn:ss") As String

  On Local Error GoTo LocalError
  Dim Result As String
  Dim TimeReminder As Double
  Dim ValueAsDate As Date
  Result = "#N/A"
  If Len(Trim(CValue & "")) > 0 Then
    ValueAsDate = CDate(CValue)
    TimeReminder = CDbl(ValueAsDate) - CLng(ValueAsDate) - CDbl(TimeSerial(Hour(ValueAsDate), Minute(ValueAsDate), Second(ValueAsDate)))
    TimeReminder = TimeReminder * 86000000#
    Result = Format(ValueAsDate, CBaseFormat) & "." & Format(TimeReminder, "0000")
  End If
  FormatWithMilliseconds = Result

End Function

Public Sub TimeToMillisecond(ByRef OTimeAsString As String, ByRef OTimeAsDate As Date)

  Dim SystemTime As TypeSystemTime
  GetSystemTime SystemTime
  OTimeAsString = _
    Format(SystemTime.Hour, "00") & ":" & _
    Format(SystemTime.Minute, "00") & ":" & _
    Format(SystemTime.Second, "00") & "." & _
    Format(SystemTime.Millisecond, "0000")
  OTimeAsDate = CDate(CDbl(TimeSerial(SystemTime.Hour, SystemTime.Minute, SystemTime.Second)) + CDbl(SystemTime.Millisecond) / 86000000#)

End Sub

Open in new window

This shows, that the fractions are used, but you may already run into floating point issues.