-
Sep 13th, 2017, 10:46 AM
#1
Thread Starter
Hyperactive Member
Format Function in date array
I have a simple date array where I intend to store 3 dates in a certain date-time format but I met some difficulties using the native Format function in conjunction with that date array. I am able to get month on the first position formatting current day but once I try to assign the result to myData array this result is somehow converted back to dd/mm/yyyy hh:mm:ss (that is the default date-time format of my OS via regional settings) . So, how could I preserve the new date-time format in myData array ?
Code:
Dim myData(1 To 3) As Date
myData(1) = Format(Now, "mm/dd/yyyy hh:mm:ss")
"VB code is practically pseudocode" - Tanner Helland
"When you do things right, people won't be sure you've done anything at all" - Matt Groening
"If you wait until you are ready, it is almost certainly too late" - Seth Godin
"Believe nothing you hear, and only one half that you see" - Edgar Allan Poe
-
Sep 13th, 2017, 11:05 AM
#2
Re: Format Function in date array
Hi Daniel,
Any variable declared as "Date" (array or not) knows nothing about any formatting. It's just a special case of an IEEE Double. It's only when you print (or otherwise show) the date that formatting comes into play.
If you wish to preserve formatting, possibly save your dates in a String array rather than a Date array. I'm not personally crazy about that idea, but maybe it'll meet your needs.
Good Luck,
Elroy
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Sep 13th, 2017, 11:06 AM
#3
Re: Format Function in date array
Format is for strings
You would use Format to display the contents of your date variables not to assign a value to them
-
Sep 13th, 2017, 11:10 AM
#4
Re: Format Function in date array
Hi,
try it like this
Code:
Dim myData(1 To 3) As Date
myData(1) = Format(Now, "\#mm\/dd\/yyyy hh:mm:ss#")
regards
Chris
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Sep 13th, 2017, 11:11 AM
#5
Re: Format Function in date array
@Chris: So long as myData() is declared as a Date, nothing is going to go into it that "remembers" any formatting.
@DataMiser: Yes, I like the way you said that.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Sep 13th, 2017, 11:20 AM
#6
Re: Format Function in date array
Originally Posted by Elroy
@Chris: So long as myData() is declared as a Date, nothing is going to go into it that "remembers" any formatting.
@DataMiser: Yes, I like the way you said that.
Hi Elroy,
your right didn't see that.
this should work
Code:
Public Enum cDateType
asDate = 1
asTime = 2
asDateTime = 3
End Enum
Public Function strDate(varDate As Variant, DateTyp As cDateType) As String
Dim s As String
Dim d As Date
If TypeName(varDate) = "String" Then
varDate = Trim(varDate)
If Len(varDate) = 0 Then
strDate = "NULL"
Exit Function
End If
End If
d = CDate(varDate)
If d = 0 Then
s = "NULL"
Else
Select Case DateTyp
Case 1: s = Format(d, "\#mm\/dd\/yyyy#")
Case 2: s = Format(d, "\#hh:mm:ss#")
Case 3: s = Format(d, "\#mm\/dd\/yyyy hh:mm:ss#")
Case Else: Exit Function
End Select
End If
strDate = s
End Function
regards
Chris
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Sep 13th, 2017, 01:09 PM
#7
Re: Format Function in date array
Dan
Given the foregoing good posts, it seems that now we are dealing only with the display of your info.
Perhaps you could post a screenshot of what you have.
Spoo
-
Sep 13th, 2017, 03:20 PM
#8
Thread Starter
Hyperactive Member
Re: Format Function in date array
Originally Posted by Elroy
Any variable declared as "Date" (array or not) knows nothing about any formatting.
Very well pointed out. Unfortunately, this is the truth... I know that Format is for strings but honestly I would have expected that a date variable to preserve the format returned by function. My requirement is related to the need to compare 2 dates that have a different format, one is starting with month and other with day. For this this reason both dates should be converted in the same format but in this case, considering the result of Format function, what we do compare: strings or dates ? In my opinion a date variable had to be more versatile with its content without having to call other functions or making tricks in strings for this purpose. Indeed, saving the dates as string make possible the comparison (greater vs. lower) but are strings able to evaluate their content as date ? Thank you.
"VB code is practically pseudocode" - Tanner Helland
"When you do things right, people won't be sure you've done anything at all" - Matt Groening
"If you wait until you are ready, it is almost certainly too late" - Seth Godin
"Believe nothing you hear, and only one half that you see" - Edgar Allan Poe
-
Sep 13th, 2017, 03:32 PM
#9
Re: Format Function in date array
If you have dates that are strings and in different formats then you may be able to use CDate(Format$()) to place the string in the correct format and then convert it to a date.
I have not tried this myself so can't be sure it that would work in all cases.
The actual format of the date is based on the system regional settings.
-
Sep 13th, 2017, 03:43 PM
#10
Re: Format Function in date array
If both are dates and both have been types as DATES, then the format is irrelevant. Again, the format of a date is for display. It doesn't change the underlying value (which is a decimal value by the way) ... so if both date value have been properly cast into Date values, you should be able to compare them safely w/o worrying about dmy or mdy ...
That said... you have to be careful about the source of the dates... where did they come from and in what form (ie, did they come in as strings initially in their different formats? or did they actually come in as dates, simply masquerading as strings? did that make senses? it did in my head at first).
-=tg
-
Sep 13th, 2017, 04:17 PM
#11
Re: Format Function in date array
Originally Posted by techgnome
Again, the format of a date is for display. It doesn't change the underlying value (which is a decimal value by the way)
Hey Techgnome,
Just wondering what you mean by the bolded part of what I've quoted. A Date is truly just a special case of a Double where 0 = #12/30/1899 00:00:00#
The integer part is a count of days, and the fractional part is the portion of a day that has lapsed.
It's not terribly efficient, but it gets it done.
Here's a test...
Code:
Option Explicit
Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (ByRef Dest As Any, ByRef Source As Any, ByVal Bytes As Long)
Private Sub Form_Load()
Dim dt As Date
Dim d As Double
dt = 1.1234
MsgBox dt ' <--- reports: 12/31/1899 2:57:42 AM, which is correct.
CopyMemory d, dt, 8
MsgBox d ' <--- reports: 1.1234
Unload Me
End Sub
If a Date wasn't really a Double, there's no way that the second MsgBox would report correctly.
Best Regards,
Elroy
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Sep 13th, 2017, 07:12 PM
#12
Re: Format Function in date array
Sorry, double, not decimal.... but yeah, that's what I meant... the internal storage of a date uses a double storage bucket. All too often people get hug up on what it looks like rather than focusing on the value itself.
-tg
-
Sep 13th, 2017, 07:30 PM
#13
Re: Format Function in date array
Using CDate on strings could fail if the format does not match your regional settings.
If you pass July 1st using mm/dd when your system settings is dd/mm, you should get a date of Jan 7th, not July 1st. Likewise, if you pass July 15th using mm/dd then an error should occur but doesn't. VB knows there aren't 15 months in a year, so it assumes you got the dd & mm reversed and auto-guesses.
If you want to use a different format from the system settings, you may want to use the DateSerial() function?
-
Sep 14th, 2017, 02:10 AM
#14
Thread Starter
Hyperactive Member
Re: Format Function in date array
Strings versus dates, storage as double and conversions considering the format from system settings...I am a bit more confused than I was before. The idea is I have to find the minimum date/time in a day. I have a variant array (2 dimensional) that comes via ADO with many values from a database. On a certain element of this array are stored these date values I am interested in and they keep the format from database (date/time as mm/dd/yy hh:mm:ss). Mainly I have to loop this array searching for minimum date/time value but for this purpose I need to initialize a variable as result and this variable cannot be zero but something comparable with date/time format from above. I considered that formatting the current day via Now function will solve this initialization issue but as long as Format is designed for strings ... Considering that my system settings are dd/mm/yyy, the question is what is properly to compare: strings, dates or double values ?
Code:
Dim dbArray() as Variant
Dim i as Long, minDate as Date
'dbArray is loaded in ADO via getRows method and dates are stored on 8th element of first dimension
'example of dates: 09/13/2017 12:58:21 AM , 09/13/2017 2:08:30 PM , 09/13/2017 8:17:17 PM
minDate = Format(Now, "mm/dd/yyyy hh:mm:ss")
For i=0 to UBound(dbArray,2)
If dbArray(7,i) < minDate Then minDate=dbArray(7,i)
Next i
Last edited by Daniel Duta; Sep 14th, 2017 at 02:23 AM.
"VB code is practically pseudocode" - Tanner Helland
"When you do things right, people won't be sure you've done anything at all" - Matt Groening
"If you wait until you are ready, it is almost certainly too late" - Seth Godin
"Believe nothing you hear, and only one half that you see" - Edgar Allan Poe
-
Sep 14th, 2017, 04:10 AM
#15
Re: Format Function in date array
no reason to be confused:some examples
Code:
Private Sub Command1_Click()
Dim TheArray(1 To 12) As Variant
TheArray(1) = 1234.25
TheArray(2) = Date
TheArray(3) = Now
TheArray(4) = #7/3/1964#
TheArray(5) = "Your grandma told my grandma"
TheArray(6) = TheArray(2) + TheArray(4)
TheArray(7) = TheArray(2) + 7
TheArray(8) = DateAdd("ww", 1, TheArray(7))
TheArray(9) = CDbl(Now)
TheArray(10) = CDbl(TheArray(4))
TheArray(11) = CDate("2016-09-23")
TheArray(12) = CStr(TheArray(11))
For i = 1 To 12
Print i, TheArray(i)
Next
End Sub
so your
minDate = Format(Now, "mm/dd/yyyy hh:mm:ss")
simply becomes
minDate = now()
and now mindate will be a date and not a string
so you can compare dates with dates (and pears with pears)
(wanted te say apples with apples, but i think that advertising is considered a 'bad' thing)
do not put off till tomorrow what you can put off forever
-
Sep 14th, 2017, 04:26 AM
#16
Re: Format Function in date array
or:
dim V as variant
V=#2017-09-14 22:10:15#
? V
and vb will automagicaly change V in your systems format
do not put off till tomorrow what you can put off forever
-
Sep 14th, 2017, 04:27 AM
#17
Re: Format Function in date array
Ikke
Tutti-fruity to you, mate ..
I think you've got it.
BTW, that was for post #15
Spoo
Last edited by Spooman; Sep 14th, 2017 at 04:31 AM.
-
Sep 14th, 2017, 04:55 AM
#18
Re: Format Function in date array
Date values do not contain any values for year, month, hour, minute, or second. There is only a day value. This value is defined as a time with no date for values less than 1, otherwise the number of days since December 30 1899 which can be fractional (part of a day):
Code:
Debug.Print CDate(0.5)
Debug.Print CDate(0.75)
Debug.Print CDate(1#)
Debug.Print CDate(1.5)
Code:
12:00:00 PM
6:00:00 PM
12/31/1899
12/31/1899 12:00:00 PM
So stop getting hung up on textual representations of dates, and don't assign String values to Date variables willy-nilly.
Come on folks, 2017 is very late in the day to be stumbling over a concept this ancient.
Last edited by dilettante; Sep 14th, 2017 at 05:02 AM.
-
Sep 14th, 2017, 05:11 AM
#19
Re: Format Function in date array
I agree with dilettante, but the hours, minutes and seconds are easily calculated from the date value.
The number of seconds passed since midnight is stored as: seconds/86400
If often do direct calculations on the date value:
Code:
Private Sub Form_Load()
Dim dtData As Date
Dim dData As Double
Dim dTime As Double, dSeconds As Double
Dim lDate As Long, lHours As Long, lMinutes As Long, lSeconds As Long
dtData = Now
dData = dtData
Debug.Print dtData
Debug.Print "Double value:", dData
lDate = Int(dData)
dTime = dData - lDate
Debug.Print "Date value:", lDate
Debug.Print "Time value:", dTime
dSeconds = dTime * 86400
Debug.Print "Total seconds:", dSeconds
lHours = dSeconds \ 3600
lMinutes = (dSeconds - lHours * 3600) \ 60
lSeconds = dSeconds - lHours * 3600 - lMinutes * 60
Debug.Print "HH:MM:SS", Format(lHours, "00") & ":" & Format(lMinutes, "00") & ":" & Format(lSeconds, "00")
End Sub
-
Sep 14th, 2017, 06:54 AM
#20
Re: Format Function in date array
do not put off till tomorrow what you can put off forever
-
Sep 14th, 2017, 08:07 AM
#21
Re: Format Function in date array
Originally Posted by Daniel Duta
Strings versus dates, storage as double and conversions considering the format from system settings...I am a bit more confused than I was before. The idea is I have to find the minimum date/time in a day. I have a variant array (2 dimensional) that comes via ADO with many values from a database. On a certain element of this array are stored these date values I am interested in and they keep the format from database (date/time as mm/dd/yy hh:mm:ss). Mainly I have to loop this array searching for minimum date/time value but for this purpose I need to initialize a variable as result and this variable cannot be zero but something comparable with date/time format from above. I considered that formatting the current day via Now function will solve this initialization issue but as long as Format is designed for strings ... Considering that my system settings are dd/mm/yyy, the question is what is properly to compare: strings, dates or double values ?
Code:
Dim dbArray() as Variant
Dim i as Long, minDate as Date
'dbArray is loaded in ADO via getRows method and dates are stored on 8th element of first dimension
'example of dates: 09/13/2017 12:58:21 AM , 09/13/2017 2:08:30 PM , 09/13/2017 8:17:17 PM
minDate = Format(Now, "mm/dd/yyyy hh:mm:ss")
For i=0 to UBound(dbArray,2)
If dbArray(7,i) < minDate Then minDate=dbArray(7,i)
Next i
Wait... hold on.,.. so the dates come from a database? That means you have them in a recordset, right? A couple of stupid questions: 1) couldn't you ask for the Min(datefield) from the database? or 2) sort the recordset by the date field and get the date from the first (or last, depending on how you sort) record?
Meanwhile, if all you want is the min date from an array, as long as the values in the array are dates, that should be simple:
(shooting from the hip here)
Code:
Private Function GetMinDate(Dates() as Date) as Date
Dim currDate as Date
Dim a as Integer
currDate - Dates(0) ' Start with the first date
For a = 1 to UBOUND(Dates)
If Dates(a) < currDate Then 'If the next date is less than the current date
currDate = Dates(a) 'Store the current date
next
GetMinDate = currDate ' return the stored current (min) date
End Funciton
-tg
-
Sep 14th, 2017, 08:43 AM
#22
Re: Format Function in date array
Originally Posted by Daniel Duta
The idea is I have to find the minimum date/time in a day.
Now that's a bit of a fun problem, and I'll accept that as a challenge. Also, I'm going to do it without using any of the built-in date functions, as they'd just make it more complex.
Building on what I said above about a Date just being a special case of a Double, we can do the following:
Code:
Option Explicit
Public Function MinTimeForDay(ByVal TheFocusDay As Date, TheDateArray() As Date) As Date
' Be sure TheDateArray() is dimensioned, or this will crash.
' Returns midnight for TheFocusDay (actually the day prior) if nothing is found.
'
Dim i As Long
'
TheFocusDay = Int(TheFocusDay) ' Strip portion of a day from our focus day.
MinTimeForDay = 999999# ' Set a very large min to start.
'
For i = LBound(TheDateArray) To UBound(TheDateArray)
If Int(TheDateArray(i)) = TheFocusDay Then ' It's the same day, so check it.
If TheDateArray(i) < MinTimeForDay Then MinTimeForDay = TheDateArray(i)
End If
Next i
If MinTimeForDay = 999999# Then MinTimeForDay = TheFocusDay
End Function
Private Sub Form_Load()
' Just a test to see if it works.
Dim dtArray(1 To 5) As Date
dtArray(1) = #5/5/2017 12:04:02 AM#
dtArray(2) = #5/6/2017 12:01:02 AM#
dtArray(3) = #5/5/2017 12:23:02 AM#
dtArray(4) = #5/5/2017 12:01:02 AM#
dtArray(5) = #5/5/2017 12:01:24 AM#
Dim dtAnswer As Date
dtAnswer = MinTimeForDay(#5/5/2017#, dtArray())
MsgBox Format$(dtAnswer, "mm/dd/yy hh:nn:ss AM/PM")
Unload Me
End Sub
So, see? If you state what you're after, you'll often get it.
However, after looking at that code, I'm still not sure that's what you're really after. But hey ho. That's what you said you were after.
Good Luck,
Elroy
Last edited by Elroy; Sep 14th, 2017 at 08:53 AM.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Sep 14th, 2017, 08:54 AM
#23
Re: Format Function in date array
Originally Posted by IkkeEnGij
Hihi, proofpositive that december 30 1899 never existed
Code:
Private Sub Command1_Click()
Dim D As Date
For D = #12/28/1899# To #1/1/1900# Step 0.125
Print D
Next
End Sub
Hi IkkeEnGij,
I'm not sure what you're trying to say there, but maybe try the following code:
Code:
Dim D As Date
For D = #12/28/1899# To #1/1/1900# Step 0.125
Debug.Print Format$(D, "mm/dd/yyyy hh:nn:ss AM/PM")
Next
Best Regards,
Elroy
EDIT1: The results:
Code:
12/28/1899 12:00:00 AM
12/29/1899 09:00:00 PM
12/29/1899 06:00:00 PM
12/29/1899 03:00:00 PM
12/29/1899 12:00:00 PM
12/29/1899 09:00:00 AM
12/29/1899 06:00:00 AM
12/29/1899 03:00:00 AM
12/29/1899 12:00:00 AM
12/30/1899 09:00:00 PM
12/30/1899 06:00:00 PM
12/30/1899 03:00:00 PM
12/30/1899 12:00:00 PM
12/30/1899 09:00:00 AM
12/30/1899 06:00:00 AM
12/30/1899 03:00:00 AM
12/30/1899 12:00:00 AM
12/30/1899 03:00:00 AM
12/30/1899 06:00:00 AM
12/30/1899 09:00:00 AM
12/30/1899 12:00:00 PM
12/30/1899 03:00:00 PM
12/30/1899 06:00:00 PM
12/30/1899 09:00:00 PM
12/31/1899 12:00:00 AM
12/31/1899 03:00:00 AM
12/31/1899 06:00:00 AM
12/31/1899 09:00:00 AM
12/31/1899 12:00:00 PM
12/31/1899 03:00:00 PM
12/31/1899 06:00:00 PM
12/31/1899 09:00:00 PM
01/01/1900 12:00:00 AM
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Sep 14th, 2017, 08:58 AM
#24
Re: Format Function in date array
Originally Posted by dilettante
This value is defined as a time with no date for values less than 1
Hi Dilettante,
Actually, you can go less than one. It just starts counting the days backwards from 12/30/1899.
Take Care,
Elroy
Code:
debug.Print format$(-5, "mm/dd/yyyy") ' <--- prints: 12/25/1899
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Sep 15th, 2017, 02:43 AM
#25
Re: Format Function in date array
proof that the date "december 30 1899" never existed keeps piling up
or is the above statement a contradiction in terms ?
Code:
Private Sub Command1_Click()
Dim D as Date
D=#12-30-1899#
? D
D=#1899-12-30#
? D
End Sub
Private Sub Command2_Click()
Dim D As Date
For D = #12/28/1899# To #1/1/1900#
Print D,CDbl(D)
Next
End Sub
just copy and paste
of cource the string "december 30 1899" clearly exists
proof: i have just written it down twice
so for everyone who ever wondered if there ever was a year 0 (zero)
proof positive that there ever was a day 0 (zero)
so if the year 1899 did have a day 0 (zero)
maybe the year 0 (zero) did have 365 days 0 (zero) ?
do not put off till tomorrow what you can put off forever
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|