+ Reply to Thread
Results 1 to 5 of 5

"Less than or equal to" operator not working with dates

Hybrid View

  1. #1
    Registered User
    Join Date
    05-14-2015
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    21

    "Less than or equal to" operator not working with dates

    I have a weekly cashflow spreadsheet. The columns are the allocated weekly expenses, the column headings are a date = "week ending". I am using the "=IF(AND" comparator to determine if a task (expense) occurs during that particular week. I am using the "<=" equation to test if the task end date is less than or equal to the week ending date. If the task end date is the same as the week ending date, the formula doesn't work. It works for all other dates. Here is my formula:
    = IF(AND(V$19>=$E85,$D85<U$19),DAYS($E85,U$19)*$O85,)

    V$19: Week-ending Date of current week
    U$19: Week-ending Date of previous week
    $D85: Start date of task
    $E85: End date of task
    DAYS($E85,U$19): the number of days in the current week the task is performed.
    $O85: an Expense constant

    Please note, this equation looks only at tasks that started before the week, but finish during the week. I have other equations for tasks that start and finish within the week etc.
    Last edited by Plastik mac; 05-14-2015 at 10:33 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,995

    Re: "Less than or equal to" operator not working with dates

    How are the task-end dates being entered? Excel stores dates as real numbers, as a number of days since 1/1/1900. The decimal part represents the time. If your week ending date is entered as (I'm translating from US format to the-rest-of-the-world format here):

    14/5/2015 00:00 (and displayed as 14/5/2015)

    it will not match a task end date entered as

    14/5/2015 14:15 (and also displayed as 14/5/2015)

    Formatting dates as d/m/yyyy hh:mm will help uncover the issue.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    05-14-2015
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    21

    Re: "Less than or equal to" operator not working with dates

    Hi Jeff,
    I see what you mean, my finish date is a calculated field and Excel is apportioning hours and minutes (to allow for the decimal portion of the date value I presume). How do I get Excel to compare the Date only?

    Michael

  4. #4
    Registered User
    Join Date
    05-14-2015
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    21

    Re: "Less than or equal to" operator not working with dates

    That seems to have fixed it, I have added a "Rounddown" constraint to the Task End date to remove the decimals and it seems to be working.

    Thankyou!

    Michael

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,995

    Re: "Less than or equal to" operator not working with dates

    Glad to help. ROUNDDOWN can do it; INT can also remove the time. Thanks for the rep!

+ 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] Delete Rows if a cell does not equal "Finished" or "Complete"
    By Justair07 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 09-27-2013, 12:40 PM
  2. [SOLVED] Cell Formula to count time with "Greater Than or Equal to" and "Less Than"
    By chriswhite1982 in forum Excel General
    Replies: 3
    Last Post: 06-16-2013, 12:30 AM
  3. [SOLVED] IF formula with logical " Less than Or Equal To" NOT WORKING (Data is week number)
    By zeko90 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-28-2013, 02:51 PM
  4. Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" not working
    By redders in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2011, 03:52 PM
  5. Replies: 3
    Last Post: 01-19-2006, 02:45 PM

Tags for this Thread

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