Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: Date Inconsitency

    HI:

    WHEN I USE THE LEFT FUNCTION IN EXCEL TO RETURN THE FIRST TWO CHARACTERS I GET AN DIFFERENT RESULT IF I USE DATE OR THE VALUE FROM A CELL.


    Dim myleft1, myleft2 As Date

    '************************************************
    myleft1 = Left(Format(Range("b" & z).Value, "MM/DD/YYYY"), 2)
    'HERE THE RESULT IS '07', WHICH IS WHAT I WANT

    myleft2 = Left(Format(Date, "MM/DD/YY"), 2)
    HERE THE RESULT IS '1/8/1900' AND I DON'T UNDERTAND WHY I GET THIS AND NOT 'O7'
    '************************************************
    .
    .
    .
    .
    .
    If myleft1 = myleft2 Then
    mycount = mycount + Sheet5.Range("C" & z).Value
    Select Case mycount
    Case Is = "8/"
    Sheet7.Range("b9").Value = mycount
    Case Is = "9/"
    Sheet7.Range("b10").Value = mycount
    End Select
    eNd If

    IS THERE A WAY TO FIX THIS??

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    I think it has to do with how Excel stores the date as a number (i.e. 37821).

    In your second one, it doesn't matter what you put int the cell. It really isn't referencing the cell at all.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by Alexxx12
    WHEN I USE THE LEFT FUNCTION IN EXCEL TO RETURN THE FIRST TWO CHARACTERS I GET AN DIFFERENT RESULT IF I USE DATE OR THE VALUE FROM A CELL.


    Dim myleft1, myleft2 As Date

    '************************************************
    myleft1 = Left(Format(Range("b" & z).Value, "MM/DD/YYYY"), 2)
    'HERE THE RESULT IS '07', WHICH IS WHAT I WANT

    myleft2 = Left(Format(Date, "MM/DD/YY"), 2)
    HERE THE RESULT IS '1/8/1900' AND I DON'T UNDERTAND WHY I GET THIS AND NOT 'O7'
    '************************************************

    IS THERE A WAY TO FIX THIS??
    This is because you have dimensioned your variables 'myleft1' as an implied Variant type, and 'myleft2' as a date type, So with myleft2 you are saying take '9' ( or whatever month number in 'Date') and make it a date which is going to show however many days from 1/0/1900 as a date format. With myleft1 excel is setting the Variable type per the context of its use, a string in this case. To resolve it you need to set explicit variables like this.

    '********************
    Dim myleft1 As String, myleft2 As String
    OR
    Dim myleft1 As String
    Dim myleft2 As String

    '********************

    You could also type these as an integer or long variable type, if you don't care about the leading '0'. The numeric types use a smaller bit size requirement.

    * Not trying to be the Excel Code Nazi, but I have seen you frequently refer to range objects using a concatenated string notation as the range name ie. "myleft1 = Left(Format(Range("b" & z).Value, "MM/DD/YYYY"), 2)" This will certianly work, but is not good Syntax. For cleaner easier to follow code that uses less overhead, use the Cells Property to refer to a cell where a Variable Column / Row is needed. The Cells property can use a number or numeric value in a variable for the Row and Col coordinants.

    If you want wave the big red NEWBIE flag then keep doing what you've been doing, otherwise use the Cells property where appropriate

    From Microsoft Visual Basic Help Example
    This example sets the font size for cell C5 on Sheet1 to 14 points.

    Worksheets("Sheet1").Cells(5, 3).Font.Size = 14

    This example clears the formula in cell one on Sheet1.

    Worksheets("Sheet1").Cells(1).ClearContents

    This example sets the font and font size for every cell on Sheet1 to 8-point Arial.

    With Worksheets("Sheet1").Cells.Font
    .Name = "Arial"
    .Size = 8
    End With

    This example loops through cells A1:J4 on Sheet1. If a cell contains a value less than 0.001, the example replaces that value with 0 (zero).

    For rwIndex = 1 to 4
    For colIndex = 1 to 10
    With Worksheets("Sheet1").Cells(rwIndex, colIndex)
    If .Value < .001 Then .Value = 0
    End With
    Next colIndex
    Next rwIndex

    This example sets the font style for cells A1:C5 on Sheet1 to italic.

    Worksheets("Sheet1").Activate
    Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True
    Last edited by savbill; 09-04-04 at 08:40.
    ~

    Bill

  4. #4
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    date format

    Thank you for your great advise. Once the application is functional, i will review the code and comply with your standards. I need to pay attention to them in my code not only to make it better but more readable and understandable.

Posting Permissions

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