+ Reply to Thread
Results 1 to 4 of 4

IF statement involving time subtraction versus entered time data

  1. #1
    Registered User
    Join Date
    02-05-2016
    Location
    Toronto, Canada
    MS-Off Ver
    Microsoft Office 2013
    Posts
    2

    IF statement involving time subtraction versus entered time data

    Hi,

    I'm trying to use an IF statement to determine if entered time versus calculated time via subtraction are equal to one another. I seem to be having a problem as for some time values the IF statement is working correctly, however for other values it is not. Does anyone know why this would be? I have attached photos with the designated cells. Cell P51 is 12:12:00 AM; similar to P114.

    Thanks
    Attached Images Attached Images

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: IF statement involving time subtraction versus entered time data

    Pictures are pretty worthless, when it comes to debugging numbers...

    Attach a sample workbook instead.

    I'm betting there's a floating point arithmetic issue here, though.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    02-05-2016
    Location
    Toronto, Canada
    MS-Off Ver
    Microsoft Office 2013
    Posts
    2

    Re: IF statement involving time subtraction versus entered time data

    Would this be more helpful to work with?

    Thanks,
    Jason
    Attached Files Attached Files

  4. #4
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: IF statement involving time subtraction versus entered time data

    As suspected, it's down to floating point arithmetic.

    Change your formula in A16 to
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Here's a decent explanation of why it's happening:
    https://blogs.office.com/2008/04/10/...wrong-answers/

+ 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. [SOLVED] IF statement on data validation, End time greater than Start time
    By Faridwahidi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-02-2014, 08:04 AM
  2. Replies: 8
    Last Post: 07-17-2013, 02:13 PM
  3. If statement involving time (RESOLVED)
    By o4008 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-27-2013, 11:27 AM
  4. Issue with IF statement involving time variables
    By persistent. in forum Excel General
    Replies: 1
    Last Post: 07-03-2012, 10:56 PM
  5. Excel 2007 : Time Stamp: Every time data is entered
    By otanii in forum Excel General
    Replies: 1
    Last Post: 08-04-2011, 05:29 PM
  6. Prompt message of time limit when time is entered
    By minato in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-31-2008, 10:13 AM
  7. [SOLVED] Show timesheet time in and out in regular time versus military tim
    By John in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-11-2005, 01:20 AM

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