Date Format

Derek Brown
Derek Brown used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
Can you give  a complete sample ?
John TsioumprisSoftware & Systems Engineer

Commented:
Check this :
cdate(cdbl((ItemDateCreated1)+cdbl(ItemDateCreated2))/2)

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

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.
ste5anSenior Developer

Commented:
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

Author

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

Commented:
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


Capture.PNG

Author

Commented:
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?
Yes it will use the full value for sorting.

Author

Commented:
Thank all.

Correct bracketing was wrong
Correct Did not need CDbl

What is the format in a query grid that will show milliseconds?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
NewDate = DateValue(ItemDateCreated1) + DateDiff("d", ItemDateCreated1, ItemDateCreated2)/2
ste5anSenior Developer

Commented:
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

Open in new window

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial