+ Reply to Thread
Results 1 to 8 of 8

Time Subtraction Formula

  1. #1
    Registered User
    Join Date
    03-23-2012
    Location
    Australia
    MS-Off Ver
    Professional Plus 2010
    Posts
    51

    Time Subtraction Formula

    Hi,

    I'm trying to do the following:

    In the attached file, I'd like to subtract whichever is the earliest time between B4 or C4 from A4, then B5 or C5 from A5, etc.

    I assume I need a conditional "if" statement but I'm not sure if that will work.

    Any ideas?

    Bill
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-15-2011
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    436

    Re: Time Subtraction Formula

    Please try this function.

    =A4-MIN(B4:C4)
    Please click 'Add reputation', if my answer helped you.

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Time Subtraction Formula

    bill? bgates. bill gates? is that really you? i'm a fan of your Excel product. try this:
    =A4-MIN(B4:C4)

  4. #4
    Registered User
    Join Date
    03-23-2012
    Location
    Australia
    MS-Off Ver
    Professional Plus 2010
    Posts
    51

    Re: Time Subtraction Formula

    Thanks guys, and no, not "that" Bill Gates!

    That worked fine for that example. But now I'm having other problems.

    In the attached example, if you click on B4 and D4 you will see dates linked to those times. If you click on C4 you will see a value in a format similar to the time format but slightly different. This value is also a time. These values are entered automatically by the workplace time clock program. I suspect the different formats are interfering with the calculation.

    How can I get around this?

    Thanks in advance.

    Bill
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    09-15-2011
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    436

    Re: Time Subtraction Formula

    You can try this updated function.

    =B4-MIN(C4,TEXT(D4,"hh:mm:ss"))
    Attached Files Attached Files

  6. #6
    Forum Contributor aganesan99's Avatar
    Join Date
    04-26-2013
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    129

    Re: Time Subtraction Formula

    Hi bgates,

    Right click in cell E4, select Format cells--> Click on Custom --->and select hh:mm, then things will work out.

    Thanks,
    aganesan99

  7. #7
    Registered User
    Join Date
    03-23-2012
    Location
    Australia
    MS-Off Ver
    Professional Plus 2010
    Posts
    51

    Re: Time Subtraction Formula

    I couldn't get it to work.

    I've formatted the cells correctly and re-written the formula slightly but it seems to be subtracting C from B each time regardless of whether C is a larger value than D (See lines 11, 14, 18 and 24).



    HELP!
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-23-2012
    Location
    Australia
    MS-Off Ver
    Professional Plus 2010
    Posts
    51

    Re: Time Subtraction Formula

    I should have mentioned, the =B4-MIN(C4:D4) formula works fine using an example with values that are consistent, i.e. all values are either linked to dates in the formula bar or they aren't.

    Very puzzling!

+ 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. Time Subtraction with condition
    By lovinguy in forum Excel General
    Replies: 1
    Last Post: 04-20-2011, 04:27 AM
  2. Time Subtraction
    By realniceguy5000 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2009, 01:56 PM
  3. subtraction off time after midnight
    By Steved in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-23-2006, 08:50 PM
  4. [SOLVED] time subtraction problem
    By Govind in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 09-06-2005, 03:05 PM
  5. [SOLVED] time subtraction problem
    By in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 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