+ Reply to Thread
Results 1 to 5 of 5

extracting the number on the right of decimal of a date & time formated cell

  1. #1
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    679

    extracting the number on the right of decimal of a date & time formated cell

    I have a number in a cell (42340.6340277778) formatted like this: mm/dd/yy hh:mm

    I would like to keep the number in the date&time format that it's in, and output just 0.6340277778 into another cell. How can this be done with VBA?

    Thanks
    Last edited by luv2glyd; 12-03-2015 at 03:18 AM.
    You either quit or become really good at it. There are no other choices.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: extracting the number on the right of decimal of a date & time formated cell

    Not sure of the VBA for this, but the fornmula would be...
    =MOD(A23,1) Format as Time
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: extracting the number on the right of decimal of a date & time formated cell

    Please Login or Register  to view this content.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: extracting the number on the right of decimal of a date & time formated cell

    Assume your data in column A. Results in column B. Here is a VBA solution

    Option Explicit
    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    679

    Re: extracting the number on the right of decimal of a date & time formated cell

    Great! Thank you everyone.

+ 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. How do I take a cell formated as TIME and divide it by a cell formated as a number?
    By KellyHelpDeskManager in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-01-2015, 10:58 AM
  2. Excel 2013 – need help extracting date and time from text cell plus more.
    By Art Mann in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-18-2014, 10:55 AM
  3. [SOLVED] Number in cell formated as text
    By Oeysbrei in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-24-2012, 05:00 AM
  4. Extracting Date/Time from 1 cell
    By jaywizz in forum Excel General
    Replies: 3
    Last Post: 08-19-2010, 09:51 AM
  5. Adding zero's to Time formated cell
    By Browner in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-20-2006, 02:10 PM
  6. Replies: 3
    Last Post: 09-09-2005, 07:05 AM
  7. [SOLVED] How do I convert a number formated as a date to text in Excel?
    By BrotherNov in forum Excel General
    Replies: 6
    Last Post: 03-02-2005, 12:06 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