none
MSP Actual Work Update (Date Format Issue) RRS feed

  • Question

  • Hi All,

    I have database, In which I have day wise Actual Work which i am updating in MSP.  Code seems right but it is having a date format issue with the client machine where Date format is different than my database.

    What i need to do  (VBA Code) which will ensure that my code will run properly. is there a way to change the date format in MSP before i run my code?

    Please suggest.


    Thanks, Parth

    Monday, January 16, 2017 5:23 PM

Answers

  • Parth,

    You don't need to if you use the DateSerial function. However, this simple routine will get the information you want. Note, this assumes Project is open on the user's system even though no Project file may be active.

    Sub ChkDateFormat()
    If Application.DateOrder <> pjMonthDayYear Then
        MsgBox "Date format is DD/MM/YY"
    Else
        MsgBox "Date format is MM/DD/YY"
    End If
    End Sub

    John





    • Edited by John - Project Wednesday, January 18, 2017 5:11 PM update
    • Marked as answer by Parth Rawal Friday, February 3, 2017 5:13 AM
    Wednesday, January 18, 2017 1:57 PM

All replies

  • Parth,

    Is there a way to change the date before you run you code? Yes, change the Windows date format to match that of your database.

    However, I'm guessing that's not what you want to do. So what is the date format of your database? What is the current date format for your installation of Project?

    You mention VBA code. Is "my code" a VBA procedure or something else?

    If your code is VBA then depending on the two date formats, it is fairly easy to decode and convert one date format to another.

    John

    Monday, January 16, 2017 6:41 PM
  • Hey john,

    Thanks for Reply. I have 2 approaches. 

    1. A scheduler which runs on server and then runs the Macro, to sync the data with MSP. 

    2. In case Scheduler is unable to update the same. We are also giving end users to manually run the macro and sync data.

    Now, Let's say on server i can make sure that the date format on the sever's MSP is correct and matches with database's date format (which is required to run code) But if End user while running macro manually changes his system's date format (This is unlikely as most organization does not allow end users to change the date format, but it is also possible that date format in different in different regions)

    Can you suggest some approach on this?


    Thanks, Parth

    Tuesday, January 17, 2017 5:47 AM
  • Parth,

    So if I understand correctly your database has a fixed date format but MSP may have different date formats depending on what a user sets as the system date format. There are a couple of ways to address this issue, here's one.

    Use the DateSerial function to convert the incoming database date into whatever date format is set for the user's system. For example, assuming the database format is DD/MM/YY, the DateSerial function will convert June 1, 2017 into 1/6/2017 for a system setting of DD/MM/YY or 6/1/2017 for a system setting of MM/DD/YY.

    Hope this helps.

    John

    Tuesday, January 17, 2017 4:54 PM
  • Thanks for the reply.

    How to identify Date format of end user's system.?


    Thanks, Parth

    Wednesday, January 18, 2017 5:54 AM
  • Parth,

    You don't need to if you use the DateSerial function. However, this simple routine will get the information you want. Note, this assumes Project is open on the user's system even though no Project file may be active.

    Sub ChkDateFormat()
    If Application.DateOrder <> pjMonthDayYear Then
        MsgBox "Date format is DD/MM/YY"
    Else
        MsgBox "Date format is MM/DD/YY"
    End If
    End Sub

    John





    • Edited by John - Project Wednesday, January 18, 2017 5:11 PM update
    • Marked as answer by Parth Rawal Friday, February 3, 2017 5:13 AM
    Wednesday, January 18, 2017 1:57 PM