+ Reply to Thread
Results 1 to 9 of 9

=if(f5<(f5+time(f5+2, 0, 0)),"good","cancel")

  1. #1
    Registered User
    Join Date
    01-19-2012
    Location
    df
    MS-Off Ver
    Excel 2010
    Posts
    8

    =if(f5<(f5+time(f5+2, 0, 0)),"good","cancel")

    =IF(F5<(F5+TIME(F5+2, 0, 0)),"GOOD","CANCEL")

    I'm attempting to input a time in F5(would be the current time Shift+CTRL+. I want for cell G5 to let me know when the original time is 2 hours past cell F5.

    What am I doing wrong? I've attached the Excel sheet that I'm working on. Ideally what I'm looking to do is once it gives me a positive("GOOD") I want to set up an auto email sent to my outlook. Anyone that can help please and thank you.
    Attached Files Attached Files

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,849

    Re: =if(f5<(f5+time(f5+2, 0, 0)),"good","cancel")

    Not sure I fully understand but that formula will always be true while ever the time function is positive, ie 1 will always be less than 1 + a positive number. Are you trying to use actual time? Try now() instead
    Happy with my advice? Click on the * reputation button below

  3. #3
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,828

    Re: =if(f5<(f5+time(f5+2, 0, 0)),"good","cancel")

    "want for cell G5 to let me know when the original time is 2 hours past cell F5"
    Can you Brief this part again
    Punnam

  4. #4
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,849

    Re: =if(f5<(f5+time(f5+2, 0, 0)),"good","cancel")

    OK. G5 is in the area of your table where it is 4 hours but try this in C3 and copy down and for G3 down chnage the 2 to 4

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-19-2012
    Location
    df
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: =if(f5<(f5+time(f5+2, 0, 0)),"good","cancel")

    No that didn't work Crooza. That just made 11am-11pm GOOD and the others cancel.

    Ugh I'm not sure why this is so difficult. I've been messing with functions for the last two days and no luck.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,972

    Re: =if(f5<(f5+time(f5+2, 0, 0)),"good","cancel")

    If you want to test for time relative to "now", you need to use the NOW() function. Remember though, that will include the date, so if you are comparing to just time, you will need to strip the date part out...use =MOD(A1,1)

    Also, for TIME to update, the workbook needs to have a change made (F2, calc, enter something etc)
    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

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,972

    Re: =if(f5<(f5+time(f5+2, 0, 0)),"good","cancel")

    Try this...
    =if(MOD(NOW(),1)+"2:00"<=your cell time+"2:00",GOOD","CANCEL")

  8. #8
    Registered User
    Join Date
    01-19-2012
    Location
    df
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: =if(f5<(f5+time(f5+2, 0, 0)),"good","cancel")

    Quote Originally Posted by FDibbins View Post
    Try this...
    =if(MOD(NOW(),1)+"2:00"<=your cell time+"2:00",GOOD","CANCEL")
    That worked FDibbins but had to remove the +"2:00" from the part after <=. Now the you said that it something has to update on the report for the NOW function to work. Is there a way for it to Auto update something on the report. My plan is for once something reads as "CANCEL" I want to my a function for that to send an email to myself to pretty much tell me to stop slacking and get it canceled.

    =IF(MOD(NOW(),1)+"2:00"<=B4,"GOOD","CANCEL")

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,972

    Re: =if(f5<(f5+time(f5+2, 0, 0)),"good","cancel")

    The +"2:00" is what adds the extra 2 hours, and no, there is no way (apart from using VBA) to automate the update

+ 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. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  2. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  3. [SOLVED] Assign Categories for Time of Day: "Morning", "Afternoon", "Evening"
    By long_shanks in forum Excel General
    Replies: 3
    Last Post: 06-11-2013, 02:59 AM
  4. create links to check boxes marked "good" fair"and "bad"
    By pjb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-19-2006, 09:25 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