Date Formatting Woes

[Minitman]

Greetings again,

A new problem popped up when I tried to change my date format in a
TextBox from mmm dd, yyyy to mmm dd, yyyy ddd. The old format
allowed the "date1" to work but the new formant stops the code from
going past the date1 segment of code. The format itself works as far
as displaying the date in that TextBox, but not in the date1 code
segment.
Here is the code:

date1 = TB1.Value
If date1 >= "9/15/97" Then TB11.Value = ".0825"
If date1 < "9/15/97" Then TB11.Value = ".08"

I know it has something to do with formatting, but I don't know how.

Any help would be appreciated.

TIA

-Minitman

 

[Frank Kabel]

Hi
you first have to convert your textbox value to a real date. Excel probably
does not recognise it as a valid date.

Also I'd suggest to use DateSerial for the comparison instead of a string
value

 

[Minitman]

Hey Frank,

How do I do that?

-Minitman

Hi
you first have to convert your textbox value to a real date. Excel probably
does not recognise it as a valid date.

Also I'd suggest to use DateSerial for the comparison instead of a string
value

 

[Jake Marx]

Minitman,

What is putting the text in the textbox? Code, a default value, or the
user? The problem here is that VBA does not recognize a date in that
format:

?IsDate("Dec 15, 2004 Wed")
False

So you'll either have to parse out the date or use a different date format
that is recognizable by VBA.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Minitman]

Hey Jake,

The date is imputed from two sources. The first is on the spreadsheet
as a starting point. The second is on a UserForm.

I formatted the cell with a custom format (mmm dd, yyyy ddd) so that
I could see what day of the week I was working on. The old format was
(mmm dd,yyyy) and the code worked with it.

The UserForm is designed to cycle after each Save event (you hit Save
and the entries are saved and the UserForm is unloaded then reloaded).
The first thing that pops up are three buttons (everything else is
hidden), you choose one and the buttons hide and the date TextBox pops
up. You either change the date or hit enter to continue. This date
gets it's default value from the date on the sheet each time I hit the
Save button. When you exit this date, it then changes the value on
the spreadsheet cell to what ever is in the TextBox.

This is working fine, even with my custom format.

The problem occurs when I try to use the TextBox date to determine if
a variable should equal one constant or another, there being a cutoff
date involved (9/15/97 or in MS, 35690).

What I am looking for is a way to convert/varse or somehow make the
TextBox entry readable as a date.

How can I do this?

TIA

-Minitman

 

[Jake Marx]

Hi Minitman,

Something like this should do the trick:


Public Function gsConvertDate(rsOldDate As String) As String
Dim nPos As Integer

nPos = InStrRev(rsOldDate, " ")

If nPos Then
gsConvertDate = Left$(rsOldDate, nPos - 1)
Else
gsConvertDate = rsOldDate
End If
End Function

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Lonnie M.]

Hi,
I often use a calendar to update dates. It is fairly easy with the
built in one, and it allows you to see the day of the week.
In the VBA editor add a user form, right click on the toolbox, click
the additional controls option, and you will see an option called
calendar control. When you select that and click O.K. it will add a
calander control.
This is an example of the code I have for my calander form:
'########################################################
Public Sub Calendar1_Click()
'Change target cells as necessary
Range(TargetCellDate).Value = Calendar1.Value
Range(TargetCellDate).Offset(rowoffset:=1, columnoffset:=0).Select
ActiveSheet.Calculate
Unload Me
End Sub
'########################################################
Public Sub UserForm_Initialize()
' Set starting conditions.
With frmCalendar
..Calendar1.Value = Range(TargetCellDate)
..Calendar1.SetFocus
End With
End Sub
'########################################################

Here is the code I have in a worksheet to call the calendar function:
'########################################################
Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range,
Cancel As Boolean)
Dim C%
Cancel = True
TargetCellDate = Target.Address(False, False)
Select Case TargetCellDate
Case "B2"
C = 2
Case "B3"
C = 3
Case "B4"
C = 4
Case "B5"
C = 5
Case Else
C = 0
End Select
If C >= 1 Then
frmCalendar.Show
'MsgBox "Cell " & Target.Address(False, False) & " Worked."
End If
End Sub
'########################################################

This isn't exactly what you are doing, but it should get you in the
ball park.
I hope I didn't open a can of worms. It works nicely once you get it
setup.
HTH--Lonnie M.

 

[Lonnie M.]

In my example, TargetCellDate was a global variable.