# Thread: Using USA dates (2003 SP3)

1. ## Using USA dates (2003 SP3)

Hi

In cell I1 I have a date, the cell is configured to dd-mmm-yyyy, in cell I6 I have =DATE(YEAR(I1),MONTH(I1),1), in I7 I have = I6+1 etc. up to AL6+1 to reflect the 31 days of January.

I then have a grid of 60 cells where leave is updated; the formatting of these cells is General

At the bottom of the grid and in line with the 28th day of the month I have =IF(MONTH(AJ6)=MONTH(\$I\$6),"Y","N") and I then fill in Y or N in the 29th, 30th and 31st to show how many days are in that particular month

February 1st takes its date by =DATE(YEAR(I1),MONTH(I1),1) and this repeated all the way down to December.

I have been asked to ‘Americanise’ the worksheet so that the date format shows mm/dd/yy, I have changed the date codes on my user form - Me.txtDateFrom = Format(Me.Calendar1, "mm/dd/yyyy") (the same in the txtDateTo code) and the format of the cells that they update, I have tried both custom and using Date – United States and selecting the example 3/14/01.

If I then allocate leave from say Jan 2nd 08 to Jan 8th 08 it correctly shows on the user form and in the cells in the worksheet as 01/02/08 to 08/08/08 but the calendar cells are then showing an H (for holiday) from the 1st of Feb through to the 8th of August.

I have tried this by changing the date format in I1 but it does not seem to make a difference.

Can anybody offer a suggestion please

Cheers

Steve

2. ## Re: Using USA dates (2003 SP3)

It would help if you told us how the calendar cells are populated.

3. ## Re: Using USA dates (2003 SP3)

Hi Hans

all of the 720 cells (each month x 60) have this formula =IF(WEEKDAY(I\$6,2)>5,"",IF(ISNUMBER(MATCH(I\$6,Publ icHoliday,0)),"PH",INDEX(LeaveTypes,SUMPRODUCT((\$H 7=SNames)*(SFrom<=I\$6)*(STo>=I\$6)*SType))))

I have tried to scale it down to post but even the zipped version is too large, I have noticed though that somebody else has downloaded my original version and is now working on it, they have attached a copy to post <post#=697,175>post 697,175</post#> which does show the main components of the original workbook

Cheers

Steve

4. ## Re: Using USA dates (2003 SP3)

The lines that copy the From and To dates to the worksheet should explicitly take the date format into account if the display format is different from the system date format. Use these lines in cmdAddLeave_Click:

.Cells(Irow, 2).Value = DateSerial(Right(Me.txtDateFrom, 4), Left(Me.txtDateFrom, 2), Mid(Me.txtDateFrom, 4, 2))
.Cells(Irow, 3).Value = DateSerial(Right(Me.txtDateTo, 4), Left(Me.txtDateTo, 2), Mid(Me.txtDateTo, 4, 2))

You must format columns B and C as mm/dd/yyyy too, if you haven't done that already.

5. ## Re: Using USA dates (2003 SP3)

Hi Hans

Thanks for the response, I have added the code as instructed but it gives me a compile error, invalid or unqualified reference on the first .cells, this is how the code looks

Dim Irow As Long
Dim Iname As Long
Dim ws As Worksheet
Set ws = Worksheets("2008")

<font color=448800>'Unprotect worksheet</font color=448800>

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

<font color=448800>'USA date structure</font color=448800>

.Cells(Irow, 2).Value = DateSerial(Right(Me.txtDateFrom, 4), Left(Me.txtDateFrom, 2), Mid(Me.txtDateFrom, 4, 2))
.Cells(Irow, 3).Value = DateSerial(Right(Me.txtDateTo, 4), Left(Me.txtDateTo, 2), Mid(Me.txtDateTo, 4, 2))

<font color=448800>'find first empty row in db</font color=448800>

Irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Iname = Me.cboName.ListIndex

<font color=448800>'Check for a name</font color=448800>

If Trim(Me.cboName.Value) = "" Then
MsgBox "Please enter an employees name"
Exit Sub
End If

<font color=448800>'Confirm Leave type</font color=448800>

If Trim(Me.cboLeaveType.Value) = "" Then
MsgBox "Please enter the purpose of the leave"
Exit Sub
End If

<font color=448800>'Copy the data to the database</font color=448800>

With ws
.Cells(Irow, 1).Value = Me.cboName
.Cells(Irow, 2).Value = CDate(Me.txtDateFrom.Value)
.Cells(Irow, 3).Value = CDate(Me.txtDateTo.Value)
.Cells(Irow, 4).Value = Me.cboLeaveType.Value
.Cells(Irow, 6).Value = Me.TxtAuthorised
End With

<font color=448800>'Clear the data from the form</font color=448800>

Me.cboName.Value = ""
Me.txtDateFrom.Value = ""
Me.txtDateTo.Value = ""
Me.TxtAuthorised.Value = ""
Me.cboLeaveType.Value = ""
Me.cboName.SetFocus

<font color=448800>'Relock the worksheet</font color=448800>

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Cheers

Steve

6. ## Re: Using USA dates (2003 SP3)

I took the code from the workbook attached to the post you referred to.

An unqualified reference to .Cells will fail, of course. The lines were supposed to replace the two lines indicated in bold below, not to be added at random.

With ws
.Cells(Irow, 1).Value = Me.cboName
.Cells(Irow, 2).Value = CDate(Me.txtDateFrom.Value)
.Cells(Irow, 3).Value = CDate(Me.txtDateTo.Value)

.Cells(Irow, 4).Value = Me.cboLeaveType.Value
.Cells(Irow, 6).Value = Me.TxtAuthorised
End With

7. ## Re: Using USA dates (2003 SP3)

Spot on Hans Thanks

Cheers

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•