+ Reply to Thread
Results 1 to 7 of 7

using vba get the serial number of time portion of date and time cell

  1. 02-13-2020, 12:49 PM #1
    jprlimey
    jprlimey is offline
    Forum Contributor
    Join Date
    12-22-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    205

    using vba get the serial number of time portion of date and time cell

    Using the following vba code

    Please Login or Register  to view this content.
    I can get the serial number of the date.

    How can I get the time serial number portion of same cell formatted at m/d/yyyy h:mm using vba?

    Thanks
    Last edited by jprlimey; 02-13-2020 at 05:06 PM. Reason: Solved
    Reply With QuoteRegister To Reply
  2. 02-13-2020, 01:04 PM #2
    nigelog
    nigelog is offline
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: using vba get the serial number of time portion of date and time cell 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 02-13-2020, 02:18 PM #3
    jprlimey
    jprlimey is offline
    Forum Contributor
    Join Date
    12-22-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    205

    Re: using vba get the serial number of time portion of date and time cell

    nigelog,

    the solution offered appears not to work

    Please Login or Register  to view this content.
    I get a Run-time error 13, Type mismatch


    Attachment 662611

    The image above I want to subtract the date time serial number between the green and yellow cells (green - yellow). They are the same date but the time factor is different 11:42 AM for green cell and 10:37 AM for yellow cell. Therefore a 1 hour and 5 minute difference which should translate to a higher serial number for the green cell vs the yellow cell.

    In the edit line F2, the date appears as 1/6/2020 11:42:00 AM for the green cell.

    Thanks
    Reply With QuoteRegister To Reply
  4. 02-13-2020, 02:37 PM #4
    MrShorty
    MrShorty is offline
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,849

    Re: using vba get the serial number of time portion of date and time cell

    Since date/time serial numbers in Excel are stored as doubles, how about using CDbl(.cells(i,9)) instead of CLng()?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.
    Reply With QuoteRegister To Reply
  5. 02-13-2020, 03:21 PM #5
    jprlimey
    jprlimey is offline
    Forum Contributor
    Join Date
    12-22-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    205

    Re: using vba get the serial number of time portion of date and time cell

    MrShorty,

    For Date 1/6/2020 10:37:00 AM in Cells(j, 9)

    Please Login or Register  to view this content.
    returned (in the Immediate window) 43836

    Appears to be missing the time portion should have returned 43836.442136111

    Thanks
    Reply With QuoteRegister To Reply
  6. 02-13-2020, 04:02 PM #6
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,624

    Re: using vba get the serial number of time portion of date and time cell

    Dim iTime As Long ---> Double???


    I an not able to open your attachment:
    Invalid Attachment specified. If you followed a valid link, please notify the administrator
    Last edited by protonLeah; 02-13-2020 at 04:04 PM.
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  7. 02-13-2020, 05:04 PM #7
    jprlimey
    jprlimey is offline
    Forum Contributor
    Join Date
    12-22-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    205

    Re: using vba get the serial number of time portion of date and time cell

    protonLeah,

    Your solution worked great.

    Thanks very much.
    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. Excel - Distinguish Time category and calculate each portion of time?
    By bluekflo in forum Excel General
    Replies: 1
    Last Post: 05-13-2015, 07:17 AM
  2. Convert date and time in serial number format to text format
    By nda13112 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-11-2013, 02:45 PM
  3. Remove time portion in date
    By angie.chang in forum Excel General
    Replies: 2
    Last Post: 06-13-2012, 12:19 AM
  4. comparing the time portion of a date/time
    By syphlix in forum Excel General
    Replies: 8
    Last Post: 03-25-2011, 04:08 AM
  5. Text Box returning serial number than time
    By kazbarina in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-30-2010, 09:26 AM
  6. time serial number
    By Bob Gotti in forum Excel General
    Replies: 1
    Last Post: 01-19-2006, 06:10 PM
  7. [SOLVED] How Do I Eliminate the Serial Number for time from Date?
    By Bob Gotti in forum Excel General
    Replies: 2
    Last Post: 01-19-2006, 01:30 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1