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











up vote
0
down vote

favorite












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







New contributor




Amit Patel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
















  • 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
    yesterday















up vote
0
down vote

favorite












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







New contributor




Amit Patel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
















  • 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
    yesterday













up vote
0
down vote

favorite









up vote
0
down vote

favorite











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







New contributor




Amit Patel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











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







New contributor




Amit Patel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question







New contributor




Amit Patel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question






New contributor




Amit Patel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked yesterday









Amit Patel

1063




1063




New contributor




Amit Patel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





Amit Patel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






Amit Patel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.








  • 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
    yesterday














  • 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
    yesterday








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
yesterday




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
yesterday










1 Answer
1






active

oldest

votes

















up vote
1
down vote



accepted










=(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








New contributor




Dhaval is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.


















    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',
    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
    });


    }
    });






    Amit Patel is a new contributor. Be nice, and check out our Code of Conduct.










    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








    up vote
    1
    down vote



    accepted










    =(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








    New contributor




    Dhaval is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.






















      up vote
      1
      down vote



      accepted










      =(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








      New contributor




      Dhaval is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.




















        up vote
        1
        down vote



        accepted







        up vote
        1
        down vote



        accepted






        =(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








        New contributor




        Dhaval is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.









        =(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








        New contributor




        Dhaval is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.









        share|improve this answer



        share|improve this answer






        New contributor




        Dhaval is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.









        answered yesterday









        Dhaval

        261




        261




        New contributor




        Dhaval is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.





        New contributor





        Dhaval is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.






        Dhaval is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.






















            Amit Patel is a new contributor. Be nice, and check out our Code of Conduct.










            draft saved

            draft discarded


















            Amit Patel is a new contributor. Be nice, and check out our Code of Conduct.













            Amit Patel is a new contributor. Be nice, and check out our Code of Conduct.












            Amit Patel is a new contributor. Be nice, and check out our Code of Conduct.
















            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

            Accessing regular linux commands in Huawei's Dopra Linux

            Can't connect RFCOMM socket: Host is down

            Kernel panic - not syncing: Fatal Exception in Interrupt