+ Reply to Thread
Results 1 to 8 of 8

If statement on working out percentage and based on time and date not working.

  1. 07-04-2019, 09:12 AM #1
    Johnny247
    Johnny247 is offline
    Forum Contributor
    Join Date
    12-18-2013
    Location
    Kings Lynn, England
    MS-Off Ver
    Excel 2019
    Posts
    234

    If statement on working out percentage and based on time and date not working.

    Hi all, I have two formulas I need help with please on the attached file.
    The first one, if a vehicle does not turn up at a specified time, on a specified date, it will show Not arrived in the corresponding cell (I have highlighted it for ease). I can get it to work using NOW() but as soon as the day changes then it is no longer not arrived! So how do I link it to the specific day?
    The second error I have, is with the percentages at the right hand side of the spreadsheet.
    The formula will work out a correct percentage based on 4 vehicles arriving. But if only 3, or 2 have been booked, it needs to reflect the amount booked, rather than the total amount that could come in.
    Any help appreciated on these.
    Kind Regards,
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-04-2019, 10:14 AM #2
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: If statement on working out percentage and based on time and date not working.

    I'm not following the logic you are using to get your results, that needs to be explained in greater detail.

    But NOW gives the current date and time*, and you are comparing that to just a time. So B9 will always be less than NOW.

    Shooting from the hip I think you want this part of your formula

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    to be

    Formula: copy to clipboard 
    Please Login or Register  to view this content.






    *Excel stores date and time as a real number of days since 1/1/1900. So NOW() gives you a number of days, plus a decimal fraction of the day that gives you the current time. TODAY() gives just the integer portion. MOD(NOW(),1) gives you just the time.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  3. 07-04-2019, 11:12 AM #3
    Johnny247
    Johnny247 is offline
    Forum Contributor
    Join Date
    12-18-2013
    Location
    Kings Lynn, England
    MS-Off Ver
    Excel 2019
    Posts
    234

    Re: If statement on working out percentage and based on time and date not working.

    Hi, thanks for that. I think, what that means is MOD NOW with the 1 is MOD will look for the number (which will be now) and divide it by a number (1). My question is on that, will that mean that tomorrow, when I open the file, up until that time, that will read that the vehicle is still planned? If so, then that will not work unfortunately.
    For further detail: A vehicle is booked and a time input in column B. When it arrives, a time is put in Column C which triggers a response from Column E (Early/Late/On time). If a time is input into column C, but not B, then a response Not Booked is triggered in Column E. What I need is almost the opposite, so if a time is entered in Column B, but no additional columns have a time, then after that time I am looking for Not Arrived to be entered. This all needs to be relative to the date on the row it is on (hence my question on now() ).
    Hopefully that all makes some sense!
    Any further help appreciated.
    Reply With QuoteRegister To Reply
  4. 07-11-2019, 10:20 AM #4
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,554

    Re: If statement on working out percentage and based on time and date not working.

    As to the first formula: =IF(AND(C3="",OR(B3="",B3=TIME(14,0,0))),"",IF(AND(C3="",B3<NOW()),"Not Arrived",IF(AND(C3<>"",B3=""),"Did Not Book",IF(C3<B3-TIME(0,30,0),"Early",IF(C3>B3+TIME(0,30,0),"Late","On Time")))))
    it seems to me that the issue is in the segment ...OR(B3="",B3=TIME(14,0,0)… specifically I don't understand why 14:00 hours is targeted.
    If the formula is modified to read: =IF(AND(C3="",B3=""),"",IF(AND(C3="",B3<NOW()),"Not Arrived",IF(AND(C3<>"",B3=""),"Did Not Book",IF(C3<B3-TIME(0,30,0),"Early",IF(C3>B3+TIME(0,30,0),"Late","On Time")))))
    then cells E3:E7 and E9 display the previous results and E8 displays "Not Arrived" as desired.
    As to the second formula, try the following in cell S3: =COUNTIF(E3:Q3,"On Time")/SUMPRODUCT((B3:N3<>"")*(ISNUMBER(SEARCH("Time Booked",B$2:N$2))))
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  5. 07-11-2019, 11:58 AM #5
    Johnny247
    Johnny247 is offline
    Forum Contributor
    Join Date
    12-18-2013
    Location
    Kings Lynn, England
    MS-Off Ver
    Excel 2019
    Posts
    234

    Re: If statement on working out percentage and based on time and date not working.

    Hi JeteMc.
    Many thanks. The first formula looks to have done the trick. The second formula I "think" has two errors, firstly it is displaying div/0! error I think because it is not ignoring blank cells.
    The second part is that when I have Did Not Book In, it comes up with N/A I "think" because the formula is looking at the booking in time and does not recognise it(?)
    Any ideas on those please would be great.(See attached)
    Thanks in advance.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 07-11-2019, 01:43 PM #6
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,554

    Re: If statement on working out percentage and based on time and date not working.

    As to the DIV/0 error you could use either:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    or
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    The first option would display 0% and the second would display a blank. If you are going to use the values for further calculation but don't want 0% displayed you could use the first option and hide zero values using conditional formatting.
    As to the N/A in the "Percentage Not Booked" column, you could modify the formula to read:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Reply With QuoteRegister To Reply
  7. 07-11-2019, 02:27 PM #7
    Johnny247
    Johnny247 is offline
    Forum Contributor
    Join Date
    12-18-2013
    Location
    Kings Lynn, England
    MS-Off Ver
    Excel 2019
    Posts
    234

    Re: If statement on working out percentage and based on time and date not working.

    Thanks very much! That has done it. Fantastic.
    Thanks again.
    Reply With QuoteRegister To Reply
  8. 07-11-2019, 06:07 PM #8
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,554

    Re: If statement on working out percentage and based on time and date not working.

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. multiple if statement not working but not working (make sense!)
    By vanessafvg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-17-2018, 12:06 AM
  2. [SOLVED] Replace a weekend date/time with the next working day 0800 time
    By susansmith32 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-11-2016, 06:42 PM
  3. Differential exact time required from two date & time columns withe non working hours vomi
    By sanjeev1986 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-05-2015, 06:57 AM
  4. Need a formula to indicate if the date and time is in working hours or out of working hour
    By dipika.gangurde in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-14-2015, 07:32 AM
  5. ploting time that is in excess of required working hours and recognizing non-working day/s
    By gerard_gonzales33 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-03-2013, 02:52 AM
  6. [SOLVED] 'IF' statement not working with time data
    By H28Sailor in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 07-14-2013, 07:24 PM
  7. [SOLVED] Calculate turnaround time for 9pm-6am working time & Start/End date may be on weekend
    By Wauiwa in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-26-2012, 02:36 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1