+ Reply to Thread
Results 1 to 7 of 7

Convert JSON Date to Excel Date

  1. #1
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,865

    Convert JSON Date to Excel Date

    Hi,

    I have a Date in this format /Date(1516645800000+0530)/ which I would like to convert to Excel date?

    Can anyone help me with this?

    Thank you

    Deep Dave
    Cheers!
    Deep Dave

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,631

    Re: Convert JSON Date to Excel Date

    HAve you checked: https://www.mrexcel.com/forum/excel-...me-format.html
    Best Regards,

    Kaper

  3. #3
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,865

    Re: Convert JSON Date to Excel Date

    Hi,

    Thank you for the reply.. Yes, I did check that. However, I am not sure how to handle the +0530 part.

    Any ideas?

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,525

    Re: Convert JSON Date to Excel Date

    what is the +0530 ?

    the first part converts to a date 22/1/2018 18:30 but what is the second part? and offset of that number of hours?

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,631

    Re: Convert JSON Date to Excel Date

    You can extract parts of the string using/combinimg MID, LEN, LEFT, SEARCH etc.

    No sample file with at least few representative samples - I probably overestimated possible complications, but:

    date and time before + sign:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    time after + sign
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    or
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and combined:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    or
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,865

    Re: Convert JSON Date to Excel Date

    Quote Originally Posted by davsth View Post
    what is the +0530 ?

    the first part converts to a date 22/1/2018 18:30 but what is the second part? and offset of that number of hours?
    It is the value of the time zone..

  7. #7
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,865

    Re: Convert JSON Date to Excel Date

    @Kaper -

    Thank you for the reply..

    Your insights ere really helpful..

    Marking the thread solved and adding reps..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Convert text date in to a date format excel can read
    By BC TIPPING in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 12-03-2017, 02:49 AM
  2. [SOLVED] Convert Date into workable excel date format
    By SHUTTEHFACE in forum Excel General
    Replies: 7
    Last Post: 02-04-2015, 03:53 AM
  3. [SOLVED] How to convert exported text date to desired date format with excel vba.
    By sktneer in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 10-03-2013, 01:08 AM
  4. [SOLVED] How to convert Julian Date to regular excel date
    By Drehb4life in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-19-2013, 10:25 AM
  5. Convert Long Written Excel Date to Proper Date
    By DavidMichaelangelo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-19-2013, 04:47 PM
  6. [SOLVED] Convert date text to Excel date
    By kborgers in forum Excel General
    Replies: 6
    Last Post: 06-19-2012, 01:35 PM
  7. [SOLVED] Convert an Excel date to a date of my format in VBA
    By Sunnous in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-15-2005, 12:45 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1