Complications changing excel string to date value in VBA - American vs English Dates












0














I'm currently trying to code a macro to open a set of reports in a folder and create some summary reporting over all the reports. Part of this involves doing a WorksheetFunction.SumIfs using a set of dates. The dates in column M of the .csv report I am using are stored as text, so I believe I need to convert them first. However, when I try and convert the dates as per the below:



Dim MyBook As ActiveWorkbook

Dim Cel As Range

For Each Cel In MyBook.Sheets(1).UsedRange.Columns("M").Cells

     If Cel.Row <> 1 Then 'this ensures it does not try and convert the header

          Cel.Value = DateValue(Cel.Value)

     End If

Next


It actually converts one of the dates (12/11/2018, i.e. 12th November) into it's U.S. format (11/12/2018, i.e. 11th December). This only happens in a few instances. Is there a way that anyone knows of that I can use to stop this happening? Many thanks in advance.






excel vba date format







share|improve this question






















  • Open the CSV with OpenText and provide xlDMYFormat for the relevant columns in the FieldInfo argument.
    – GSerg
    Nov 19 at 11:40
















0














I'm currently trying to code a macro to open a set of reports in a folder and create some summary reporting over all the reports. Part of this involves doing a WorksheetFunction.SumIfs using a set of dates. The dates in column M of the .csv report I am using are stored as text, so I believe I need to convert them first. However, when I try and convert the dates as per the below:



Dim MyBook As ActiveWorkbook

Dim Cel As Range

For Each Cel In MyBook.Sheets(1).UsedRange.Columns("M").Cells

     If Cel.Row <> 1 Then 'this ensures it does not try and convert the header

          Cel.Value = DateValue(Cel.Value)

     End If

Next


It actually converts one of the dates (12/11/2018, i.e. 12th November) into it's U.S. format (11/12/2018, i.e. 11th December). This only happens in a few instances. Is there a way that anyone knows of that I can use to stop this happening? Many thanks in advance.






excel vba date format







share|improve this question






















  • Open the CSV with OpenText and provide xlDMYFormat for the relevant columns in the FieldInfo argument.
    – GSerg
    Nov 19 at 11:40














0












0








0







I'm currently trying to code a macro to open a set of reports in a folder and create some summary reporting over all the reports. Part of this involves doing a WorksheetFunction.SumIfs using a set of dates. The dates in column M of the .csv report I am using are stored as text, so I believe I need to convert them first. However, when I try and convert the dates as per the below:



Dim MyBook As ActiveWorkbook

Dim Cel As Range

For Each Cel In MyBook.Sheets(1).UsedRange.Columns("M").Cells

     If Cel.Row <> 1 Then 'this ensures it does not try and convert the header

          Cel.Value = DateValue(Cel.Value)

     End If

Next


It actually converts one of the dates (12/11/2018, i.e. 12th November) into it's U.S. format (11/12/2018, i.e. 11th December). This only happens in a few instances. Is there a way that anyone knows of that I can use to stop this happening? Many thanks in advance.






excel vba date format







share|improve this question













I'm currently trying to code a macro to open a set of reports in a folder and create some summary reporting over all the reports. Part of this involves doing a WorksheetFunction.SumIfs using a set of dates. The dates in column M of the .csv report I am using are stored as text, so I believe I need to convert them first. However, when I try and convert the dates as per the below:



Dim MyBook As ActiveWorkbook

Dim Cel As Range

For Each Cel In MyBook.Sheets(1).UsedRange.Columns("M").Cells

     If Cel.Row <> 1 Then 'this ensures it does not try and convert the header

          Cel.Value = DateValue(Cel.Value)

     End If

Next


It actually converts one of the dates (12/11/2018, i.e. 12th November) into it's U.S. format (11/12/2018, i.e. 11th December). This only happens in a few instances. Is there a way that anyone knows of that I can use to stop this happening? Many thanks in advance.






excel vba date format




excel vba date format






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 19 at 11:32









JaxHax

62




62












  • Open the CSV with OpenText and provide xlDMYFormat for the relevant columns in the FieldInfo argument.
    – GSerg
    Nov 19 at 11:40


















  • Open the CSV with OpenText and provide xlDMYFormat for the relevant columns in the FieldInfo argument.
    – GSerg
    Nov 19 at 11:40
















Open the CSV with OpenText and provide xlDMYFormat for the relevant columns in the FieldInfo argument.
– GSerg
Nov 19 at 11:40




Open the CSV with OpenText and provide xlDMYFormat for the relevant columns in the FieldInfo argument.
– GSerg
Nov 19 at 11:40












2 Answers
2






active

oldest

votes


















0














Instead of Cel.Value = DateValue(Cel.Value)



try 



Cel.Value = CDate(Cel.Value)





share|improve this answer

















  • 1




    Both DateValue and CDate respect the system date format.
    – GSerg
    Nov 19 at 11:42



















0














CDate didn't work because the date had already been Americanised - however by opening the csv file using Local=:True I was able to get the date in the correct format. Excel VBA date formats/values change when file is opened programatically



Thanks for your help






share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53373738%2fcomplications-changing-excel-string-to-date-value-in-vba-american-vs-english-d%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    Instead of Cel.Value = DateValue(Cel.Value)



    try 



    Cel.Value = CDate(Cel.Value)





    share|improve this answer

















    • 1




      Both DateValue and CDate respect the system date format.
      – GSerg
      Nov 19 at 11:42
















    0














    Instead of Cel.Value = DateValue(Cel.Value)



    try 



    Cel.Value = CDate(Cel.Value)





    share|improve this answer

















    • 1




      Both DateValue and CDate respect the system date format.
      – GSerg
      Nov 19 at 11:42














    0












    0








    0






    Instead of Cel.Value = DateValue(Cel.Value)



    try 



    Cel.Value = CDate(Cel.Value)





    share|improve this answer












    Instead of Cel.Value = DateValue(Cel.Value)



    try 



    Cel.Value = CDate(Cel.Value)






    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Nov 19 at 11:38









    Nick

    77711131




    77711131








    • 1




      Both DateValue and CDate respect the system date format.
      – GSerg
      Nov 19 at 11:42














    • 1




      Both DateValue and CDate respect the system date format.
      – GSerg
      Nov 19 at 11:42








    1




    1




    Both DateValue and CDate respect the system date format.
    – GSerg
    Nov 19 at 11:42




    Both DateValue and CDate respect the system date format.
    – GSerg
    Nov 19 at 11:42













    0














    CDate didn't work because the date had already been Americanised - however by opening the csv file using Local=:True I was able to get the date in the correct format. Excel VBA date formats/values change when file is opened programatically



    Thanks for your help






    share|improve this answer


























      0














      CDate didn't work because the date had already been Americanised - however by opening the csv file using Local=:True I was able to get the date in the correct format. Excel VBA date formats/values change when file is opened programatically



      Thanks for your help






      share|improve this answer
























        0












        0








        0






        CDate didn't work because the date had already been Americanised - however by opening the csv file using Local=:True I was able to get the date in the correct format. Excel VBA date formats/values change when file is opened programatically



        Thanks for your help






        share|improve this answer












        CDate didn't work because the date had already been Americanised - however by opening the csv file using Local=:True I was able to get the date in the correct format. Excel VBA date formats/values change when file is opened programatically



        Thanks for your help







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 26 at 12:29









        JaxHax

        62




        62






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid …



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid …



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53373738%2fcomplications-changing-excel-string-to-date-value-in-vba-american-vs-english-d%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            -

            Popular posts from this blog

            Volksrepublik China

            Image
            Dieser Artikel befasst sich mit der Volksrepublik China und ist nicht zu verwechseln mit der Republik China (Taiwan). Siehe auch China (Begriffsklärung). Dieser Artikel wurde aufgrund von inhaltlichen Mängeln auf der Qualitätssicherungsseite der Redaktion Ostasien eingetragen. Dies geschieht, um die Qualität der Artikel aus dem Themengebiet Ostasien auf ein akzeptables Niveau zu bringen. Dabei werden Artikel gelöscht, die nicht signifikant verbessert werden können. Bitte hilf mit, die inhaltlichen Mängel dieses Artikels zu beseitigen, und beteilige dich bitte an der Diskussion ! 中华人民共和国 Zhōnghuá Rénmín Gònghéguó Volksrepublik China Flagge Emblem Amtssprache Chinesisch (Hochchinesisch) und anerkannte Regionalsprachen 1 Hauptstadt Peking Staatsform Republik Regierungssystem Sozialistisches Einparteiensystem Staatsoberhaupt Staatspräsident Xi Jinping Regierungschef Ministerpräsident Li Keqiang Fläc...
            Read more

            How to test boost logger output in unit testing?

            Image
            up vote 1 down vote favorite My application uses BOOST_LOG_TRIVIAL to log messages. I thought it's good to check in test (gtest) that correct messages are written there in given scenarios. Is there a way to somehow access what was written there after code was called? Or does it first have to be mocked in some way? I have googled a lot for this and haven't found any instructions, so either I am asking wrong questions or no one thinks it should be done. c++ logging boost googletest boost-log share | improve this question edited yesterday ...
            Read more

            Write to the output between two pipeline

            Image
            up vote 1 down vote favorite I'm trying to write to the output (echo) within two pipe to have a trace of what I'm doing but I have an error. Get-ChildItem $path -Recurse | Where-Object { $_.Name -match '.+?.log.(d{4})-(d{2})-(d{2})$' -and $_.LastWriteTime -lt $deleteDate } | Write-Output "deleting file" -PassThru | Remove-Item but I have this error : Write-Output : The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input. I also tested in the Foreach-Object function Get-ChildItem $path -Recurse | Where-Object { $_.Name -match '.+?.log.(d{4})-(d{2})-(d{2})$' -and $_.LastWriteTime -lt $deleteDate } | Foreach-Object...
            Read more