If's with dates times

[traceym]

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!

 

[Hui]

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"

 

[traceym]

Thanks Hui!

You're a star!

 

[traceym]

Hui,

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

=IF((R21+T21)<(N21+P21),"yes","NO")

 

[Kevin@Radstock]

Hi


One way might be using the following: =IF(COUNTA(R21,T21)<2,"",IF(AND(R21<=N21,T21<=P21),"Yes","No"))

 

[traceym]

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"

 

[SirJB7]

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

 

[traceym]

Argh!!!

Your formula is missing a parenthesis! Where do I put it?

 

[traceym]

Got it!!

Thanks! This works.

Love your work!

 

[traceym]

Actually no,

I need it so that if cells R21 & T21 are blank, then the formula returns a blank too.

 

[SirJB7]

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.

 

[traceym]

Parenthesis all good but now it still doesn't take into account the due time in T21.

 

[SirJB7]

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.

 

[traceym]

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

 

[traceym]

Oh, it does, doesn't it?

I am totally blonde

 

[SirJB7]

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...


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!

 

[SirJB7]

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?

 

[traceym]

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"

 

[SirJB7]

Hi, traceym!

Download again the updated file from same previous link.

Regards!

 

[traceym]

if r21 & T21 are blank & there is an x in V21 I want it to return "no"

 

[SirJB7]

Hi, traceym!

Again, please. You yet know the procedure.

Regards!

 

[traceym]

I totally love you!

Meet you in Polynesia

 

[SirJB7]

Hi, traceym!

Glad you solved it even against blondness. See you there

Regards!