+ Reply to Thread
Results 1 to 2 of 2

Time and Date Value Formatting

  1. #1
    Registered User
    Join Date
    MS-Off Ver

    Time and Date Value Formatting

    Hi there,

    I am trying to run a few calculations in particular a VLOOKUP using time values. Now, I have googled the issue extensively and understand the varying formats that a time value could have will impact the ability of the lookup to find a match. As a result, I am using the standard off-the-shelf time value formatting from Excel (hh:mm:ss). Some of my data is pulling, but not all. For example, my 00:00:25 time stamp is not pulling through. The reason why this is, is because in Tab 1 the underlying number is 0.000324074074074073, and in Tab 2 the underlying number is 0.000324074074074074, i.e. the last dp is slightly different.

    I can not work out why this is? Formatting is the same. I am trying built a re-usable template, so while pasting one value over the top will probably work it is not a scaleable and reliable solution.

    Any thoughts on why/how this happens and ways around it would be much appreciated.



  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac

    Re: Time and Date Value Formatting


    First of all the formats of a cell will never impact how the value in that cell is used in any calculations with perhaps the sole exception when a macro might read the format of a cell and choose to do one thing or another dependent on the format. But ignore that, formats never affect the calculations when using standard excel functions.

    The difficulty you're encountering is the well known restriction associated with the processor's arithmetical precision at the ultimate decimal places. The solution is to use =ROUND() and specify a number of decimals to round to. Pick a number that is at least one more than the degree of precision required. So if your time is in A1 then use say =ROUND(A1,10)
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ 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. formatting of date and time
    By paradise2sr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-10-2014, 03:25 AM
  2. Date and time formatting
    By Mayank Trivedi in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-10-2008, 03:01 PM
  3. Date and Time Formatting
    By brook1 in forum Excel General
    Replies: 2
    Last Post: 10-23-2006, 09:25 AM
  4. date and time formatting
    By rantz in forum Excel General
    Replies: 3
    Last Post: 11-28-2005, 02:35 PM
  5. Date and time formatting
    By Marco in forum Excel General
    Replies: 7
    Last Post: 02-25-2005, 07:06 PM

Tags for this Thread


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