+ Reply to Thread
Results 1 to 5 of 5

How to separate given date and time

  1. 07-07-2015, 05:43 AM #1
    srinivasd
    srinivasd is offline
    Registered User
    Join Date
    07-07-2015
    Location
    Goa
    MS-Off Ver
    2007
    Posts
    6

    How to separate given date and time

    Hi all,

    I want to separate 2007/296-09:33:45.280329 into separate date and time columns in excel.
    the no. 296 is referred as julian day in my data. So, i need some help.

    Cheers,
    Srinivas
    Reply With QuoteRegister To Reply
  2. 07-07-2015, 06:17 AM #2
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: How to separate given date and time

    What's the Julian format for Jan 1st?
    Is it 2007/001 or 2007/1 ?

    Or Jan 10th?
    Is it 2007/10 or 2007/010 ?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
    Reply With QuoteRegister To Reply
  3. 07-07-2015, 06:23 AM #3
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: How to separate given date and time

    Doesn't matter.
    This will extract the year

    =LEFT(A1,4)

    this, the Julian number

    =MID(A1,FIND("/",A1)+1,FIND("-",A1)-FIND("/",A1)-1)+0

    and this the value to the right of it

    =RIGHT(A1,LEN(A1)-FIND("-",A1))
    Reply With QuoteRegister To Reply
  4. 07-07-2015, 06:42 AM #4
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,202

    Re: How to separate given date and time

    See attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 07-13-2015, 12:31 AM #5
    srinivasd
    srinivasd is offline
    Registered User
    Join Date
    07-07-2015
    Location
    Goa
    MS-Off Ver
    2007
    Posts
    6

    Smile Re: How to separate given date and time

    That was so easy and very helpful.. Thanks a lot...
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Separate Date & Time data into 2 different cells
    By edwinkbell in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-28-2014, 09:24 AM
  2. Need to separate date/time from a single cell and create a date/time chart
    By slicksilver79 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 06-19-2014, 09:48 PM
  3. How to separate date and time into different cells
    By MDNYC in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-30-2013, 12:10 PM
  4. Combining separate Date and Time Fields
    By thegreenape in forum Excel General
    Replies: 4
    Last Post: 11-04-2006, 10:44 AM
  5. Merging separate date and time columns into one
    By Gadgets in forum Excel General
    Replies: 5
    Last Post: 07-26-2006, 04:24 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1