Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Separating Date/Time (97 SR2)

    I just cant' get my head around this one...

    A lovely export process for a proprietary program decides to clump the date and time together, like this: 3/1/02 5:00:00AM

    I've tried to return just the timevalue, but it doesn't seem to like it. (The cells, when formatted as TIME, display only the time, but the raw value in the formula looks as above.)

    How can I programatically replace all of these instances with their true time value?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Separating Date/Time (97 SR2)

    The timevalue is the DECIMAL part of the date time.

    Use the formula:
    =datetime - Int(datetime)

    Format the date as desired
    Steve

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Separating Date/Time (97 SR2)

    If you date/time value is in A1, using =A1-INT(A1) should return just the time value, as the fractional part of a date value represents the time element. To change the actual cell value via code use something like :<pre>Dim oCell As Range
    For Each oCell In Selection
    With oCell
    .Value = oCell - Int(oCell)
    .NumberFormat = "h:mm:ss"
    End With
    Next</pre>


    Andrew C

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Separating Date/Time (97 SR2)

    [Edited to correct a brain-to-keyboard relay failure]

    I -think- Excel's TIMEVALUE function is getting all huffy that there is no space between the numeric part of the time and the "AM". Assuming the number is being treated as text, try:

    =TIMEVALUE(SUBSTITUTE(SUBSTITUTE(RIGHT(B5,LEN(B5)-FIND(" ",B5)),"AM"," AM"),"PM"," PM"))
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Separating Date/Time (97 SR2)

    Kel, if Andrew's code errors on type mismatch, the import is formatted as text, so just throw these two lines in before the "For Each" line:

    Selection.Replace What:="AM", Replacement:=" AM"
    Selection.Replace What:="PM", Replacement:=" PM"
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating Date/Time (97 SR2)

    Did anyone ever tell you guys that you are brilliant? I knew it was simple... <img src=/S/doh.gif border=0 alt=doh width=15 height=15> Thanks, and Thanks again! <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating Date/Time (97 SR2)

    Any idea how I might change this to extract the Date? [img]/forums/images/smilies/smile.gif[/img]

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Separating Date/Time (97 SR2)

    To use Andrew's Code. This would extract JUST the date portion.

    <pre>Dim oCell As Range
    For Each oCell In Selection
    With oCell
    .Value = Int(oCell)
    .NumberFormat = "mm/dd/yyyy"
    End With
    Next
    </pre>


    Steve

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating Date/Time (97 SR2)

    <img src=/S/nope.gif border=0 alt=nope width=15 height=15>

    It just changes all of the date entries to 01/00/1900...
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating Date/Time (97 SR2)

    Andrews Code effectively removes the integer from the cell. Date/Time formatted cells use a format based on the number of days since January 1st, 1900, in General format, this number looks like this:

    37497.25 Which represents 8/29/2002 6:00AM.

    I need to remove the decimal point to obtain just the date, but I'm not sure which function does it...

    Thanks though!

    -K
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating Date/Time (97 SR2)

    GOT IT! Alas...

    While researching what the heck INT is, I found the following works to extract the date:

    <pre>Dim oCell As Range
    Selection.Replace What:="AM", Replacement:=" AM"
    Selection.Replace What:="PM", Replacement:=" PM"
    For Each oCell In Selection
    With oCell
    .Value = Int(oCell)
    .NumberFormat = "mm/dd/yyyy"
    End With
    Next
    </pre>

    Thanks though! Self enlightenment is a good thing... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    -K
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Separating Date/Time (97 SR2)

    Talking to yourself again? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Did you confirm that the initial input data was being formatted and treated as text? My point earlier in the thread is that Excel is monumentally inflexible in not recognizing "3/29/01 5:00:00AM" as a valid date OR time simply and only because there's no space before the "AM". So if your imported data was being treated in this operation as text by Excel (which you didn't confirm), all you really needed to do is the find-and-replace "AM">" AM", "PM">" PM" step, and after that any conversion and separation to dates becomes dead simple.

    (These comments may apply only to US date formats.)

    F'rinstance, if you take your imported date-time data and only run the two search-and-replaces, the text turns into valid date/time values in Excels' date/time format conventions. Then you can even just use a formula where:

    =INT(cell) formatted as date returns just the date

    and

    =MOD(cell,1) formatted as time returns just the time

    But your code routines are keepers if you have to run these conversions routinely.

    Another Excel Annoyance created by the absence of a single space! What would Zippy the Pinhead say?
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating Date/Time (97 SR2)

    Well, the beauty, (Yes, we're using the term 'beauty' loosly here) of the program that exports the original file, is that it's quite archaic, and saves the file as excel 5.0. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    Given that I was dealing with over 5,000 records, I figured the coded approach was the best way to go! The only way to separate the two then, was to duplicate the date/time column, and run these two modules to obtain either the date, or the time, respectively. Great catch though, it didn't work until that extra space was in there. Next thing you know, we'll be associating 'Excel Functionality' with other more popular oxymorons, such as Military Intelligence, Democratic Leadership, or Religious Tolerance... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Separating Date/Time (97 SR2)

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> Like "Microsoft Flexibility"? Or "Microsoft International Multilingual Extensibility?"
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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