• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If's with dates times

T

traceym

New Member
Hello

I have a spreadsheet in which I have a report & it's due date & time.

N21 is the due by date

P21 is the time it is due by

R21 is the date the report was received

T21 is the time that it was received


I would like my formula to return either yes or no if the report was received by the due date & time.

Please help!
 
Traceym


Dates are just Integer numbers

41298 = 24 Jan 2013

41300 = 26 Jan 2013

Times are decimals between 0 (midnight) and 1 (next midnight)

eg: 0.5 = 12 Noon

0.25 = 6:00 am

So you can just add them together to get a Date/Time combo

41298.25 = 24 Jan 2013 6:00am


So for your problem:

=IF((R21+T21)<(N21+P21),"Received ","Not Received ") &"on Time"
 
Hui,

If R21+T21 are blank, how do I get it to return a blank cell?

=IF((R21+T21)<(N21+P21),"yes","NO")
 
Thanks, but I don't think this takes into consideration the due time. If my report it returned late it still returns a "yes" when it should return "no"
 
Hi, traceym!

Tried this?

=IF(AND(IFERROR(R21,0)+IFERROR(T21,0)>0,IFERROR(R21,0)+IFERROR(T21,0)<N21+P21),"Yes","No")

Regards!


EDITED
 
Hi, traceym!

Sorry for the typo error. Formula updated in previous post.

Regards!

PS: Didn't read your last post, wait a minute, please.


EDITED:


Try this formula:

=IF(IFERROR(R21,0)+IFERROR(T21,0)=0,"",IF(IFERROR(R21,0)+IFERROR(T21,0)<N21+P21,"Yes","No"))

Hope I didn't mess it up again with the parenthesis but who knows... I didn't test it. :p
 
Hi, traceym!

Ok, you win, let me check it besides writing it. Back in a few minutes.

Regards!


EDITED:


Give a look at this file:

https://dl.dropbox.com/u/60558749/If%27s%20with%20dates%20%26%20times%20%28for%20traceym%20at%20chandoo.org%29.xlsx


Check full range of cases from row 21 in advance. Just advise if any issue.
 
This is crazy!

I can see that it works on yours... what am I doing wrong?

https://www.dropbox.com/s/fwlxwv2vsg3ily1/KPI%20VOM.xlsx
 
Hi, traceym!


In yours it works too... in my PC. I guess you're not willing to come to my location to run your workbook... :p


Here's an image (Alt-PrtScrn, open Paint, Paste, Save) of what I can see when I open your uploaded workbook. If you see anything different than Yes, Yes and No in W21:W23 at the same uploaded file, please repeat the procedure described, generate an image of your worksheet and paste the link.

https://dl.dropbox.com/u/60558749/If%27s%20with%20dates%20%26%20times%20%28for%20traceym%20at%20chandoo.org%29.png


Regards!
 
Hi, traceym!

Sure totally blonde? I think I'd check it to validate our assertion. :)

Regards!

PS: We still have pending a month in Polynesia, am I wrong?
 
So, I'll throw another one in the mix for you...

If V21 has x in the cell, in this formula how do you make it also return "no"
 
You must log in or register to reply here.
Back
Top