Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Nov 2016
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts

    excel copy paste dates wrong numbers

    Hello,

    I've been having this issue for a while and its become so annoying that I'm reaching out to see if anyone can help. I'm using Office 365 ProPlus(excel 2016) version of excel and when copying dates from spreadsheets into xml files the date will start like this 2012-07-08 but when copied into xml it turns into this 41098.

    If I were dealing with just one or two items that didn't take long to reconvert or not copy paste in the first place it wouldn't be a big deal but I'm dealing with over 200+ of these files daily with multiple dates as well. Any insight would be so very welcomed, Thanks.
    Last edited by ForjGuy; 2016-11-03 at 11:51.

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,176
    Thanks
    47
    Thanked 982 Times in 912 Posts
    41098 is the value that Excel shows as 2012-07-08 if you format it as a date.
    In XML you can have dates shown bare, with time zone or as a date/time. What do you want it to be?

    cheers, Paul

  3. #3
    New Lounger
    Join Date
    Nov 2016
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Paul T View Post
    41098 is the value that Excel shows as 2012-07-08 if you format it as a date.
    In XML you can have dates shown bare, with time zone or as a date/time. What do you want it to be?

    cheers, Paul
    I want for it to be the bare date that originally was in excel, how can I accomplish this? Thanks

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,176
    Thanks
    47
    Thanked 982 Times in 912 Posts
    You need to change the data in Excel before the copy.
    Assuming the date is in A1 you can use this formula.
    =TEXT(A1,"yyyy-mm-dd")

    cheers, Paul

  5. #5
    New Lounger
    Join Date
    Nov 2016
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Paul T View Post
    You need to change the data in Excel before the copy.
    Assuming the date is in A1 you can use this formula.
    =TEXT(A1,"yyyy-mm-dd")

    cheers, Paul
    unfortunately I don't think this would work for me as I'm already copying from formula. Also the date is always already changed before the copy as they were all cell formatted to display like that.

  6. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,176
    Thanks
    47
    Thanked 982 Times in 912 Posts
    To use my formula you need to add a row and copy that data instead of the original.
    If you'd post a sample of your data we can suggest a layout / alternative that would work for you.

    cheers, Paul

  7. #7
    New Lounger
    Join Date
    Nov 2016
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Paul T View Post
    To use my formula you need to add a row and copy that data instead of the original.
    If you'd post a sample of your data we can suggest a layout / alternative that would work for you.

    cheers, Paul
    input

    ="<royalty>FORJ DIGITAL</royalty><cat>"&D242&"</cat><subcat>"&E242&"</subcat><code></code><action>Insert</action><providercode>"&J242&"</providercode><searchkeywords>"&G242&"</searchkeywords><musiclabel>"&F242&"</musiclabel><movie/><album>"&B242&"</album><artist>"&C242&"</artist><duration>00:00:25</duration><drm>drm only</drm><activatedate>"&H242&"</activatedate><releasedate>"&H242&"</releasedate><expirydate>"&I242&"</expirydate><metadata name=""restrictionclass"" value="&""""&L242&"""/><metadata name=""Language"" value="&""""&M242&"""/><metadata name=""Mood"" value="&""""&N242&"""/><metadata name=""Genre"" value="&""""&O242&"""/><metadata name=""YearOfRelease"" value="&""""&P242&"""/><metadata name=""MusicDirector"" value="&""""&Q242&"""/><wappreview><file>"&J242&"_500x500.jpg"&"</file></wappreview><webpreview><file>"&J242&"_500x500.jpg" &"</file></webpreview><objects><base><file>"&J242&".wma"&"</file></base></objects></true>"

    output

    ="<royalty>FORJ DIGITAL</royalty><cat>Music and Dance</cat><subcat>Rap</subcat><code></code><action>Insert</action><providercode>USUM20150898</providercode><searchkeywords>Trae Tha Truth - All Good (feat. T.I., Rick Ross amp; Audio Push ), Trae Tha Truth, Music and Dance, Rap</searchkeywords><musiclabel>Subway Records</musiclabel><movie/><album>All Good</album><artist>Trae Tha Truth</artist><duration>00:00:25</duration><drm>drm only</drm><activatedate>42376</activatedate><releasedate>42376</releasedate><expirydate>42932</expirydate><metadata name="restrictionclass" value="G"/><metadata name="Language" value="English"/><metadata name="Mood" value=""/><metadata name="Genre" value="Rap"/><metadata name="YearOfRelease" value="42376"/><metadata name="MusicDirector" value=""/><wappreview><file>USUM20150898_500x500.jpg</file></wappreview><webpreview><file>USUYG1090898_500x500. jpg</file></webpreview><objects><base><file>USUM20150898.wma</file></base></objects></true>"

    Each value is pulled from a cell, then I use this formula to input into xml, as you can probably imagine my frustration having to change those dates manually 200+ times daily

    Thanks.
    Last edited by ForjGuy; 2016-11-04 at 12:25.

  8. #8
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,176
    Thanks
    47
    Thanked 982 Times in 912 Posts
    Try this. I've surrounded your date cell references with the text function.

    cheers, Paul

    input

    ="<royalty>FORJ DIGITAL</royalty><cat>"&D242&"</cat><subcat>"&E242&"</subcat><code></code><action>Insert</action><providercode>"&J242&"</providercode><searchkeywords>"&G242&"</searchkeywords><musiclabel>"&F242&"</musiclabel><movie/><album>"&B242&"</album><artist>"&C242&"</artist><duration>00:00:25</duration><drm>drm only</drm><activatedate>"&TEXT(H242,"yyyy-mm-dd")&"</activatedate><releasedate>"&TEXT(H242,"yyyy-mm-dd")&"</releasedate><expirydate>"&TEXT(I242,"yyyy-mm-dd")&"</expirydate><metadata name=""restrictionclass"" value="&""""&L242&"""/><metadata name=""Language"" value="&""""&M242&"""/><metadata name=""Mood"" value="&""""&N242&"""/><metadata name=""Genre"" value="&""""&O242&"""/><metadata name=""YearOfRelease"" value="&""""&TEXT(P242,"yyyy-mm-dd")&"""/><metadata name=""MusicDirector"" value="&""""&Q242&"""/><wappreview><file>"&J242&"_500x500.jpg"&"</file></wappreview><webpreview><file>"&J242&"_500x500.jpg" &"</file></webpreview><objects><base><file>"&J242&".wma"&"</file></base></objects></true>"

  9. The Following User Says Thank You to Paul T For This Useful Post:

    ForjGuy (2016-11-07)

  10. #9
    New Lounger
    Join Date
    Nov 2016
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Paul T View Post
    Try this. I've surrounded your date cell references with the text function.

    cheers, Paul

    input

    ="<royalty>FORJ DIGITAL</royalty><cat>"&D242&"</cat><subcat>"&E242&"</subcat><code></code><action>Insert</action><providercode>"&J242&"</providercode><searchkeywords>"&G242&"</searchkeywords><musiclabel>"&F242&"</musiclabel><movie/><album>"&B242&"</album><artist>"&C242&"</artist><duration>00:00:25</duration><drm>drm only</drm><activatedate>"&TEXT(H242,"yyyy-mm-dd")&"</activatedate><releasedate>"&TEXT(H242,"yyyy-mm-dd")&"</releasedate><expirydate>"&TEXT(I242,"yyyy-mm-dd")&"</expirydate><metadata name=""restrictionclass"" value="&""""&L242&"""/><metadata name=""Language"" value="&""""&M242&"""/><metadata name=""Mood"" value="&""""&N242&"""/><metadata name=""Genre"" value="&""""&O242&"""/><metadata name=""YearOfRelease"" value="&""""&TEXT(P242,"yyyy-mm-dd")&"""/><metadata name=""MusicDirector"" value="&""""&Q242&"""/><wappreview><file>"&J242&"_500x500.jpg"&"</file></wappreview><webpreview><file>"&J242&"_500x500.jpg" &"</file></webpreview><objects><base><file>"&J242&".wma"&"</file></base></objects></true>"
    It works!! thank you so much!!

  11. #10
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,176
    Thanks
    47
    Thanked 982 Times in 912 Posts
    How much time does it save you?

    cheers, Paul

  12. The Following User Says Thank You to Paul T For This Useful Post:

    Maudibe (2016-11-04)

  13. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Hey Paul,

    Wouldn't have thought that xml could parse the Excel function. I work frequently with xml files and will place that one in my bag of tricks!

    Maud

  14. #12
    New Lounger
    Join Date
    Nov 2016
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Paul T View Post
    How much time does it save you?

    cheers, Paul
    Easily 4-6 hours daily thanks again!

Posting Permissions

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