# 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

Last Comment
ste5an

8/22/2022 - Mon
John Tsioumpris

Can you give  a complete sample ?
John Tsioumpris

Check this :
``````cdate(cdbl((ItemDateCreated1)+cdbl(ItemDateCreated2))/2)
``````
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.
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)
``````

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

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)
``````
Derek Brown

Can I be sure that fractions of a second are being used rather than rounded to the nearest second with this format?
ste5an

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
``````

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)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
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?
Dale Fye

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

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

LocalError:
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
``````
This shows, that the fractions are used, but you may already run into floating point issues.