none
Fault - changing date value when pasting from VBA matrix into spreadsheet RRS feed

  • Question

  • Hi,

    Has anybody experienced the issue when pasting dates from VBA created matrix into spreadsheet.
    Even when the dates are stored as numeric double values (transformed using CDbl(Date)) after pasting into spreadsheet formatted as date the VALUE is changed into date from US date format, i.e.:

    Matrix Value:

    43070 (UK format 01/12/2017 = 1 December 2017) is transformed into

    42747, which is US equivalent 12 January 2017 (notation 1/12/2017)

    ??

    The only solution I have found at the moment is to format cells as text, then paste values from VBA matrix and then format cells back to date format.

    Thanks & Rgrds,

    Tuesday, January 2, 2018 5:50 PM

All replies

  • Hi exinoque,

    Thanks for visiting our forum.

    Then here we mainly focus on general issues about Excel user interface. Since your query is about pasting dates from VBA created matrix into spreadsheet, I'll move your thread to the following dedicated MSDN forum for better response:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev 

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Yuki Sun


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, January 3, 2018 5:30 AM
  • Hello exinoque,

    What do you mean Matrix Value? Which operation make 43070 transformed into 42747? Could you please show us some simply VBA code so we could try to reproduce your issue?

    Best Regards,

    Terry 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 3, 2018 6:15 AM
  • Hi exinoque,

    I try to make a test and get the result like you mentioned in above post.

    you can also reproduce the issue directly using Excel Interface, it is not specifically related with VBA.

    I made a test with DateValue() and Value() Functions.

    you can refer article below to understand how it works.

    Excel DATEVALUE function - change text to date


    The DATEVALUE function in Excel converts a date in the text format to a serial number that Excel recognizes as a date.


    The syntax of Excel's DATEVALUE is very straightforward:


    =DATEVALUE(date_text)


    So, the formula to convert a text value to date is as simple as =DATEVALUE(A1), where A1 is a cell with a date stored as a text string.


    Because the Excel DATEVALUE function converts a text date to a serial number, you will have to make that number look like a date by applying the Date format to it, as we discussed a moment ago.


    The following screenshots demonstrates a few Excel DATEVALUE formulas in action:

    Convert text to date using the Excel DATEVALUE function.


    Excel DATEVALUE function - things to remember


    When converting a text string to a date using the DATEVALUE function, please keep in mind that:


    • Time information in text strings is ignored, as you can see in rows 6 and 8 above. To convert text values containing both dates and times, use the VALUE function.
    • If the year is omitted in a text date, Excel's DATEVALUE will pick the current year from your computer's system clock, as demonstrated in row 4 above.
    • Since Microsoft Excel stores dates since January 1, 1900 , the use of the Excel DATEVALUE function on earlier dates will result in the #VALUE! error.
    • The DATEVALUE function cannot convert a numeric value to date, nor can it process a text string that looks like a number, for that you will need to use the Excel VALUE function, and this is exactly what we are going to discuss next.

    Excel VALUE function - convert a text string to date


    Compared to DATEVALUE, the Excel VALUE function is more versatile. It can convert any text string that looks like a date or number into a number, which you can easily change to a date format of your choosing.


    The syntax of the VALUE function is as follows:


    =VALUE(text)


    Where text is a text string or reference to a cell containing the text you want to convert to number.


    The Excel VALUE function can process both date and time, the latter is converted to a decimal portion, as you can see in row 6 in the following screenshot:

    Using the Excel VALUE function to convert a text string to date

    Reference:

    How to convert text to date and number to date in Excel

    I hope from above article you get the idea about processing the dates using DateValue() and Value() functions.

    so currently, I think that your approach to handle the issue is correct because no any built in solution is available for this kind of issue and you can use that approach as a solution, other solution is to change the date format of your machine then that format will be applied to all the applications on the machines.

    which can solve the issue in Excel but it is possible that it may produce the date related issue in other Applications.

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Thursday, January 4, 2018 2:58 AM
    Moderator
  • Hi exinoque,

    Is your issue resolved?

    I find that, After creating this thread, You did not done any follow up on this thread.

    If your issue is solved then I suggest you to share your solution and mark it as an answer.

    If your issue is still persist then I suggest you to refer the suggestions given by the community members may help you to solve your issue.

    If then after you have any further questions then you can let us know about it.

    We will try to provide further suggestions to solve it.

    I suggest you to update the status of this thread. This thread is still open and will remain open until you mark the answer.

    So take appropriate steps to close this thread , If your issue is resolved.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 18, 2018 8:43 AM
    Moderator