Formula to convert UTC date time string to date/string in different timezone

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP












0














I am getting date with time in UTC format.



2018-12-13T03:36:11+00:00
2018-12-10T19:26:48+00:00


I want to convert it to date in different timezone. So take above example and the target timezone is IST(GMT+5:30) then I should get date for each as follows.



2018-12-13
2018-12-11









share|improve this question

















  • 2




    Do you need to do this in openoffice? In Excel? Can you just use the date command instead? Please edit your question and give us some more context.
    – terdon
    Dec 13 at 9:42















0














I am getting date with time in UTC format.



2018-12-13T03:36:11+00:00
2018-12-10T19:26:48+00:00


I want to convert it to date in different timezone. So take above example and the target timezone is IST(GMT+5:30) then I should get date for each as follows.



2018-12-13
2018-12-11









share|improve this question

















  • 2




    Do you need to do this in openoffice? In Excel? Can you just use the date command instead? Please edit your question and give us some more context.
    – terdon
    Dec 13 at 9:42













0












0








0







I am getting date with time in UTC format.



2018-12-13T03:36:11+00:00
2018-12-10T19:26:48+00:00


I want to convert it to date in different timezone. So take above example and the target timezone is IST(GMT+5:30) then I should get date for each as follows.



2018-12-13
2018-12-11









share|improve this question













I am getting date with time in UTC format.



2018-12-13T03:36:11+00:00
2018-12-10T19:26:48+00:00


I want to convert it to date in different timezone. So take above example and the target timezone is IST(GMT+5:30) then I should get date for each as follows.



2018-12-13
2018-12-11






openoffice excel






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Dec 13 at 9:35









Amit Patel

1063




1063







  • 2




    Do you need to do this in openoffice? In Excel? Can you just use the date command instead? Please edit your question and give us some more context.
    – terdon
    Dec 13 at 9:42












  • 2




    Do you need to do this in openoffice? In Excel? Can you just use the date command instead? Please edit your question and give us some more context.
    – terdon
    Dec 13 at 9:42







2




2




Do you need to do this in openoffice? In Excel? Can you just use the date command instead? Please edit your question and give us some more context.
– terdon
Dec 13 at 9:42




Do you need to do this in openoffice? In Excel? Can you just use the date command instead? Please edit your question and give us some more context.
– terdon
Dec 13 at 9:42










1 Answer
1






active

oldest

votes


















1














=(DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2)))+5.5/24



Just select a your cell in place of "A1" in formula
and format ans cell as you want






share|improve this answer




















    Your Answer








    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "106"
    ;
    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: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    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%2funix.stackexchange.com%2fquestions%2f487721%2fformula-to-convert-utc-date-time-string-to-date-string-in-different-timezone%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    1














    =(DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2)))+5.5/24



    Just select a your cell in place of "A1" in formula
    and format ans cell as you want






    share|improve this answer

























      1














      =(DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2)))+5.5/24



      Just select a your cell in place of "A1" in formula
      and format ans cell as you want






      share|improve this answer























        1












        1








        1






        =(DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2)))+5.5/24



        Just select a your cell in place of "A1" in formula
        and format ans cell as you want






        share|improve this answer












        =(DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2)))+5.5/24



        Just select a your cell in place of "A1" in formula
        and format ans cell as you want







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 13 at 9:44









        Dhaval

        261




        261



























            draft saved

            draft discarded
















































            Thanks for contributing an answer to Unix & Linux Stack Exchange!


            • 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%2funix.stackexchange.com%2fquestions%2f487721%2fformula-to-convert-utc-date-time-string-to-date-string-in-different-timezone%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

            How to check contact read email or not when send email to Individual?

            Displaying single band from multi-band raster using QGIS

            How many registers does an x86_64 CPU actually have?