EXCEL Macro to convert DATE format correctly for EVENT script file

Posted on 2007-07-29
Last Modified: 2008-01-09
In creating a javascript calendar, that works very well, I am trying to make it easier for my customer as a missing comma or quotation mark can cause an error resulting in a truncated or even missing calendar display.  To reduce the chance for such errors, I created a macro to convert the event data between the event script file format and the spreadsheet table display.

I have set up the spreadsheet to calculate the day of the week automatically from the date entered to help prevent entry errors. I have also use the conditional fomatting capability of Excel to alternate the background color on even and odd months and turn it red if a date is out of order to help with event entry.

THE PROBLEM IS THAT WHEN IT CONVERTS THE DATE into the events.js file, some dates are correctly entered as YYYYMMDD and others are shown as DD/MM/YYYY.

I can't work out where the problem is.
You can view the EXCEL FILE at
You can view the *.BAS file at
You can view the events script file at
Question by:Worldsplayground
    LVL 17

    Accepted Solution

    I think this could be an OS config related issue.

    As it will use the system settings to convert the date, which may not be as you expect.

    Your example seemded to work fine on my machine, but I have revise the cal_cmd code as follows.

    I would also suggest that you need some code like this

    ' for systems in the Europe

    If DateValue("1/2/2007") <> DateSerial(2007,2,1) Then
        MsgBox "Your machine date settings incorrect there may be errors with some date conversions"
    End If

    ' for systems in the USA

    If DateValue("1/2/2007") <> DateSerial(2007,1,2) Then
        MsgBox "Your machine date settings incorrect there may be errors with some date conversions"
    End If

    Hope this helps :~)

    Private Function cal_cmd(calEvent) As String

    'Purpose: Convert event properties array into event command string
        ' convert date to yyyymmdd date code
        calEvent(0) = Format(calEvent(0), "yyyymmdd")
        ' set undefined image sizes to zero (29-Apr-2004: image size parameters now optional)
        'If Not IsNumeric(calEvent(6)) Then calEvent(6) = QUOTE & QUOTE
        'If Not IsNumeric(calEvent(7)) Then calEvent(7) = QUOTE & QUOTE
        cal_cmd = CMDBEG2 & calEvent(0) & DELIM
        For i = 1 To EVENTCOLUMNS - 1
            cal_cmd = cal_cmd & QuoteWord(calEvent(i)) & DELIM
        Next i
        cal_cmd = cal_cmd & QuoteWord(calEvent(EVENTCOLUMNS)) & CMDEND
    End Function

    Author Comment

    My customer is located in Australia, could this have been the problem.
    I will try your revised edition and send it to him to see if it works.

    LVL 17

    Expert Comment

    Don't forget to put the check bit into the script because in 10 yeras time when he gets a new PC, the guy that sets it up may forget to set the correct date settings for his region; it is so easy to get the default US settings.  Also he may be using other PCs in the same office, so the check is VITAL!!!!!!!!!!!!!!!!!

    Author Comment

    Thanks, add your suggested code and IF commands and they worked a charm.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now