Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If based Formula (Excel 2K)

    I have two cells formatted for date/time like "6/7/2004 1:30 PM". I want to format a third cell that will display "Yes" when the difference between the first two cells is more than 24 hours and "No" if it is less. I've tried everything (I think) but can't find a solution. Any help appreciated. Thanks!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: If based Formula (Excel 2K)

    If A1 and B1 have the 2 dates try:
    <pre>=if(abs(a1-b1)>1,"yes","no")</pre>


    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If based Formula (Excel 2K)

    Thanks, sdckapr! It works great. Could you advise what modification the formula would need so I can use hours instead of full days?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: If based Formula (Excel 2K)

    Excel times are in days. For example:

    =if(abs(a1-b1)*24>6,"yes","no")
    or
    =if(abs(a1-b1)>6/24,"yes","no")


    Will give you the difference that is greater than 6 hours
    If you stored the hours in a cell you could also use something like (if C1 contained the hours to check)
    =if(abs(a1-b1)*24>$C$1,"yes","no")

    Of course, for minutes you could use:
    =if(abs(a1-b1)*24*60>$C$1,"yes","no")

    Hope this helps,
    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If based Formula (Excel 2K)

    Thanks, sdckapr! All the formulas worked great.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •