calling a subroutine to format date(s)

[Guest]

How to complete this sub line to include datInitDate.

Private Sub datInitDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Dt, Dt2 As Date

I want to pass the value datInitDate--it is coming up as "" (empty) in the
2nd sub below. Also, the Format method is defined as setting values as String
types; the XLS spreadsheet allows me to format cells as date (dd-MMM-yyyy)
but I'm not succeeding in doing this with VBA in the form's code.

When F8-ing thru the code, I'm getting values in the Dim statement (on mouse
over) showing Dt as empty and results of Dt2 as 12:00:00 AM. And very strange
offset dates. If I include dtInitDate in the dim statment, it is empty, too.
Why is Dt showing up as empty in the Dim, but Dt2 has a time value?


Here's the code for the two subroutines:

Private Sub UserForm_Initialize() ' Initialize the form (prefill certain
fields)
Dim datInitDate As Date
Dim datComplDate As Date

datInitDate = Date$
datComplDate = DateAdd("d", 90, datInitDate)
txtRequestor.Value = Environ("username") 'autofills with user's login ID

End Sub

' Check the value of the Initialization Date and calculate ComplDate 90 days
later
Private Sub datInitDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Dt, Dt2 As Date
On Error Resume Next
Dt = datInitDate
If Dt > DateSerial(1950, 1, 1) Then
datInitDate = Format$(Dt, "dd-mmm-yyyy")
Dt2 = DateAdd("m", 3, datInitDate)
datComplDate = Format$(Dt2, "dd-mmm-yyyy")
Else
datInitDate = InputBox("Please use a valid format for date this
corrective action was reported (fmm-dd-yyyy).")
End If
End Sub

Also, the Format statement sets variables as STRING values.

This is all for a form. Inside the spreadsheet, I can call the format as
10-Jan-2005, but even setting the custom format for Dt and Dt2 as dd-mmm-yyy,
I still get 01/10/2005

 

[Guest]

does any of this help?

Change: Dim Dt, Dt2 as Date ''' (Here Dt is a variant)
To: Dim Dt as Date, Dt2 as Date

and
Dt =Me.datInitDate.Value

and
datComplDate=Dt2 'you have just formated it in line above

 

[Guest]

When enabling the Dim statements, my dates don't work. When disabled, I get a
date, no problem. Wierd thing is that one date is mm/dd/yyyy and the other is
mm-dd-yyyy AND, when they get ported to spread sheet (both formatted as
mm/dd/yyyy date columns), they display in the format 11-Jan-2005.

I added a second line after datInitDate = Date$ as
datInitDate.Value = Format$(datInitDate, "mm/dd/yyyy")
and that took care of the / vs - issue in the form.

The dates display correctly in the formula bar (mm/dd/yyyy), but in the
cells, they are
dd-mmm-yyyy, even though the date columns are ALSO formatted as
mm/dd/yyyy...when I check the format of the added cells they are in the
10-Feb-2001 format.

I'm really lost...any ideas?

 

[Dave Peterson]

Are you positive that the format is mm/dd/yyyy?

If you are, could you try a different format for that column: mm.dd.yyyy

You may have been hit by a coincidence.

When you do Format|cells|Number tab and look at that cell's format, I'm betting
you see something like:

*14/03/2001

That asterisk means that you chose a short date format that is picked up from
the Windows Regional settings (Date tab).

xl2002 is more honest with the way it deals with dates.

At the bottom of that dialog (xl2002):

Date formats display date and time serial numbers as date values. Except for
items that have an asterisk (*), applied formats do not switch date orders with
the operating system.

Since yours is marked with an asterisk, your date will switch formats with the
setting in the OS.

======
If the mm.dd.yyyy worked, try changing your short date in your regional
settings.

=====
You could also give the cells a custom format (just slightly different from the
short date format in regional settings):

"mm/dd/yyyy "
(w/o the quotes, but with the trailing space.)

 

[Guest]

Dave,

You've got it! This phrase contains 2 concepts, which confounds me, and
I've been a software writer/user for 25 years:

Date formats display date and time serial numbers as date values. Except for
items that have an asterisk (*), applied formats do not switch date orders
with
the operating system.

I never understood the serial thing; some columns in my form that have
numbers automatically converted to this serial date concept; for example, an
entry 105 (for a regulation) would display as June 10, 1935. I'm challenged
to overcome that as a separate issue.

The 2nd piece of information (the double negative) could be expressed as:

For items with an asterisk (*), date orders established by the operating
system (see XYZ tab of ABC dialog box) take precedence over Excel settings.

 

[Dave Peterson]

You may want to read about how excel treats dates (and times, too) at Chip
Pearson's site:

http://www.cpearson.com/excel/datetime.htm

I don't understand how 105 could be change to a date (june 10, 1935) without
some kind of "help".

Maybe you could use a custom date format that:

dddd* mmmm dd, yyyy

I think it's unique enough to never be touched by the OS/excel.

 

[Guest]

does anyone know a quick way to change a positive number into a negative
number?

 

[Dave Peterson]

Put -1 in an unused helper cell.
copy that cell

Select your positive numbers
Edit|paste special|check multiply.

clear that helper cell

does anyone know a quick way to change a positive number into a negative
number?