+ Reply to Thread
Results 1 to 23 of 23

IF Statement Returns FALSE or TRUE Instead of the Specified Value

  1. #1
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    IF Statement Returns FALSE or TRUE Instead of the Specified Value

    i have the following formula

    Please Login or Register  to view this content.
    which only seems to return True or False instead of a number value (yes cell is formated at [HH]:mm)

    ive tried using the following code as well but all that does in return a line of hash tags, but works until number hits (00:00)

    Please Login or Register  to view this content.

    can someone tell me where im going wrong please

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    hash tags shows time in minus, not error in value
    so, use:
    Please Login or Register  to view this content.
    Quang PT

  3. #3
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    thank you but that changed about 5 cells to Hashtag VALUE

  4. #4
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    here is the book, i have a list of codes that dont work displayed including the one for E17
    Attached Files Attached Files

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    Could you type some expected results?

  6. #6
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    sure no Problem, ok so based on what time is entered into G12

    so the sequence based on E,F & G if any or all cells have Time in them.

    G will go blank first (if time is in that) - these hours MUST be paid back that week
    E will count down until zero or blank cell achieved - any hours left in this cell will carry over to the next red cell I13
    F will then start to count down (again if any time is in that cell) but will be last to count down - can be carried over to the next orange cell H13 if not down to zero in F17

    this is all about paying back ours owed

    AliGW
    Last edited by Learning ExL; 02-20-2020 at 06:42 AM.

  7. #7
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    Anyone Help please

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    You were asked to add expected results to the workbook - help us to help you, please.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  9. #9
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    I did explain everything that i need in my post

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    OK - it's up to you.

  11. #11
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    Hi

    Your value is negative and you can not format as date/time.
    E14-C11+F13 is less than zero and E13-F17 too.
    So =IFERROR(IF(E14-$C$11+E13>=0,"",E13-F17),"") is a negative value.

  12. #12
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    how can i get the second line to the first line and have it work. if this doesnt work as time then how else can i do it please

  13. #13
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    there has got to be a way to get this thing to work. seriously ive tried so many different ways on my own an just cant do it.

    I even tried the code below and does not work

    Please Login or Register  to view this content.
    so if there is nothing in E17 and or G17 then the following code works great.

    Please Login or Register  to view this content.
    G17 code works ok

    Please Login or Register  to view this content.
    accept when you change the figure in G13 then G17 does not match G13

    so..

    if there is nothing in G17 to add to F17, i want E17 to be added to F17 and then count up or down depending on the time and date that is entered in to G13.

    i know there is no formula in G13 at this moment in time however J13 will have a formula in it as its the following week.

    i really hope this help, i really can explain it any easier right from the first post to this post
    Last edited by Learning ExL; 02-21-2020 at 04:10 AM.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    Not wishing to labour the point, but uploading the workbook would help us to help you to solve this much faster ...

  15. #15
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    Book1 as requested
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    Quote Originally Posted by AliGW View Post
    You were asked to add expected results to the workbook - help us to help you, please.
    Here is a step by step explanation. put into the workbook
    Attached Files Attached Files

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    That looks really comprehensive - thanks. I'll have a look a bit later.

  18. #18
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    ive just found an artical related to showing negative time in excel here is an extract

    "Custom formats for negative time values
    The custom time formats discussed above work for positive values only. If the result of your calculations is a negative number formatted as time (e.g. when you subtract a bigger time amount from a smaller one), the result will be displayed as #####. If you want to format negative time values differently, the following options are available to you:

    Display an empty cell for negative times. Type a semicolon at the end of the time format, for example [h]:mm;
    Display an error message . Type a semicolon at the end of the time format, and then type a message in quotation marks, e.g. [h]:mm;"Negative time"
    Generally speaking, a semicolon acts as a delimiter to separate positive values' format from negative values' formatting.

    If you want to display negative times as negative values, e.g. -10:30, the easiest way is to change the Excel's Date System to 1904 date system. To do this, click File > Options > Advanced, scroll down to When calculating this workbook section and check Use 1904 date system box."

    here is the site i got it from.

    HTML Code: 

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    I've looked at your new workbook and I have read it very carefully. I am afraid I could not follow it at all.

    I really do hope that somebody will understand and be able to help.

  20. #20
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    thanks anyway
    Last edited by Learning ExL; 02-22-2020 at 02:51 PM.

  21. #21
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    Quote Originally Posted by AliGW View Post
    I've looked at your new workbook and I have read it very carefully. I am afraid I could not follow it at all.

    I really do hope that somebody will understand and be able to help.
    Please tell me what you dont understand, help me to help you. you asked me to explain what i wanted to happen and i spent 2 hours putting it all down to the simplest point. please dont just say you dont understand with out explanation as to what you dont understand.

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    You lost me here, I'm afraid:

    This is the drivers finish time of his previous week PLUS 45 hours (manditory hours) (if there was not to be an hours carried over from previous 2 weeks but not added here). If there was hours in RED below these hour would be added here. As there are 10 hours to be added then the time would be 13:50 plus any hour in the orange cell which would push the time forward to 18:50.
    It is, of course, simple to you. You know your data inside out and exactly what you want it to do and look like. I don't, and I have failed to understand how what I am looking at works and fits together.

    Hopefully someone else will get it, but it's not for me - sorry I wasn't able to help on this occasion.

    I will put out a call for help to the community and see if anyone else can help.
    Last edited by AliGW; 02-22-2020 at 02:55 PM.

  23. #23
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: IF Statement Returns FALSE or TRUE Instead of the Specified Value

    This is a companion explanation to the workbook

    HGV Drivers Hours Weekly Rest

    What weekly rest must a HGV driver take?

    A driver has to start a weekly rest period no later than six consecutive 24 hour periods from the end of the last weekly rest period.

    A regular drivers hours weekly rest is a period of at least 45 consecutive hours. However, this can be reduced to 24 hours every other week as long as the reduction is made up by an equal rest period before the end of the third week following the reduced week. The time must be made up in one block that cannot be split and must be attached to another rest period of a minimum of 9 hours.

    In summary a HGV driver must have completed a minimum of two weekly rest periods, one of which being a full period, within two consecutive weeks.

    A working week starts at the end of a weekly rest period and finishes after another weekly rest period begins. This means the working week isn’t always a fixed Monday to Sunday week, and as long as all rules and regulations are met the working week can start on any day.

    HGV weekly rest periods
    HGV reduced weekly rest

+ 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. .FolderExists method Returns TRUE when FALSE
    By ironfelix717 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-19-2018, 12:18 AM
  2. This returns a false or a true statement
    By i2trader in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-29-2016, 01:28 PM
  3. [SOLVED] IF Statement Evaluates as "TRUE" but Returns "FALSE" Value (Excel 2007)
    By Simcik in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-11-2014, 12:43 PM
  4. [SOLVED] IF Statement Returns False When True On Certain Numbers
    By paulm99 in forum Excel General
    Replies: 3
    Last Post: 11-26-2013, 04:41 PM
  5. IF STATEMENT returns False, even though the results are true
    By Biolu527 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-28-2013, 11:21 AM
  6. Replies: 5
    Last Post: 12-09-2012, 11:04 PM
  7. Function that compares dates and returns TRUE or FALSE
    By Emily in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-25-2006, 04:30 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