Date Format
This is very strange
When I use NewDate = CDate(CDbl(ItemDateCreated
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
When I use NewDate = CDate(CDbl(ItemDateCreated
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
John Tsioumpris
Can you give a complete sample ?
Check this :
cdate(cdbl((ItemDateCreated1)+cdbl(ItemDateCreated2))/2)
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.
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.
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.
@Dale: He is trying to get the midpoint between the 2 dates.
NewDate = CDate((CDbl(ItemDateCreated1) + CDbl(ItemDateCreated2)) / 2)@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)ASKER
Can I be sure that fractions of a second are being used rather than rounded to the nearest second with this format?
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 SubASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank all.
Correct bracketing was wrong
Correct Did not need CDbl
What is the format in a query grid that will show milliseconds?
Correct bracketing was wrong
Correct Did not need CDbl
What is the format in a query grid that will show milliseconds?
NewDate = DateValue(ItemDateCreated1
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