Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using USA dates (2003 SP3)

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

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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

    Private Sub cmdAddLeave_Click()
    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

    Worksheets("2008").Unprotect Password:="lockmeup"

    <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

    Worksheets("2008").Protect Password:="lockmeup"

    End Sub

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using USA dates (2003 SP3)

    Spot on Hans Thanks

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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