Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Date Format Problem (Excel 2K)

    Hi,

    The following code is provided with an Australian date formatted 'd/m/yy' - 1/2/03 from the range "TSMonth". Whether the date is amended or not the result, whatever I have tried, always comes back as formatted 'm/d/yy' - 2/1/03.

    <pre>' Check the correct date has been proposed for Timesheets
    MyValue = ""
    Do Until IsDate(MyValue) = True
    Message = "Please Confirm or Amend the Start Date for the Timesheet Month"
    Title = "Setting Timesheet Period Start Date"
    Default = TSMonth.Value
    MyValue = Format(InputBox(Message, Title, Default), "d/m/yy")
    ' MyValue = DateValue(InputBox(Message, Title, Default)) - unsuccessful attempt
    ' MyValue = InputBox(Message, Title, Default) - unsuccessful attempt
    ' Worksheets("Daily").Range("I36").Value = MyValue
    ' Comment for line above - Serial No indicates 'm/d/yy'
    If IsDate(MyValue) = True Then
    TSMonth.Value = Format(MyValue, "d/m/yy")
    End If
    Loop
    </pre>


    Any suggestions would be greatly appreciated as to what I need to do to return a date in the desired format 'd/m/yy' given that I am in Australia with my PC setup with the correct Regional Settings.

    Peter Moran
    Two heads are always better than one!!

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Date Format Problem (Excel 2K)

    Let me start by saying I don't know Excel VBA, so this is coming more from a Word VBA background.

    First, make sure you have ruled out the cell formatting as the culprit, or we will be here all night!

    Second, InputBox returns a string, so you likely will need to convert it into a date before formatting it back into a string.

    Does code like this work for your needs:

    <pre>Sub DateTest()
    Dim MyValue As String, Message As String, Title As String, Default As String
    ' Check the correct date has been proposed for Timesheets
    MyValue = ""
    Do Until IsDate(MyValue) = True
    Message = "Please Confirm or Amend the Start Date for the Timesheet Month"
    Title = "Setting Timesheet Period Start Date"
    'Default = "12/01/2002" 'U.S. Style
    Default = TSMonth.Value
    MyValue = InputBox(Message, Title, Default)
    If IsDate(MyValue) = True Then
    'Debug.Print Format(CDate(MyValue), "d/m/yy")
    TSMonth.Value = Format(CDate(MyValue), "d/m/yy")
    End If
    Loop
    End Sub</pre>

    Hope this helps.

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

    Re: Date Format Problem (Excel 2K)

    Jefferson has hit the nail on the head by suggesting the use of CDate, but there is no need to apply the Format function to the result. If you set the number format of the cell to the desired date format, the date will display the way you want. So you can use

    TSMonth.Value = CDate(myValue)

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Date Format Problem (Excel 2K)

    Hi,

    Thanks for your responses Jefferson and Hans - We have liftoff!

    I tried Jefferson's solution first - no difference to previous efforts(with format included!!)

    Then tried Hans suggestion that format was not needed - that did the trick! I don't understand - but it works!!

    Thanks again guys!

    Peter Moran
    Two heads are always better than one!!

Posting Permissions

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