Time - By 8am the next morning

[Jackajoo]

I have two columns with date and times. I need a formula to show that the
date in the second column was no later than 8am the day after the date/time
in the first column. For example:

A1
04/05/09 14:35 PM

B1
04/06/09 11:01 AM

So using my explanation above, this would be "not compliant" with the
scenario and I would like an "N" returned.

A compliant example would be:

A1
04/06/09 14:35 PM

B1
04/07/09 07:30 AM

Is there a formula for this? Thanks!!!

 

[T. Valko]

Are you sure your dates/times are true Excel dates/times? Excel doesn't
recognize those as true dates/times when you have the AM/PM following a 24hr
time format:

14:35 PM

This would be a true Excel date/time: 04/05/09 2:35 PM

One way to test is to see if the date/time is a number. In Excel, true
dates/times are really just numbers formatted to look like dates/times.

Let's assume cell A1 contains 04/05/09 14:35 PM.

What result do you get from this formula:

=ISNUMBER(A1)

If you get a result of FALSE then your dates/times are not true Excel
dates/times. They are TEXT strings.

 

[Jackajoo]

Hi!

I did what you asked and I got a "TRUE" answer. It just depends on where
you are viewing the field. If you look at the actual cell, it has "Army
Time" (using the 24 hour clock) and if you look at the top (the formula bar),
it is in what I call "regular time" (using the 12 hour clock).

Let me know if you can help! I appreciate it!

 

[T. Valko]

Try this...

Assuming that the date/time in B1 will *always* be later than the date/time
in A1.

=IF(COUNT(A1:B1)=2,IF(B1>INT(A1+1)+TIME(8,0,0),"N",""),"")

If there will *always* be entries in both cells then you can shorten that
to:

=IF(B1>INT(A1+1)+TIME(8,0,0),"N","")

 

[Jackajoo]

This is great - however - is there any way I can exclude weekends? I just
copiy andpasted the formula and it worked!!!

 

[T. Valko]

is there any way I can exclude weekends?

How do you mean exclude weekends?

Do you mean if the date in cell A1 is a Friday then the date/time in B1 has
to before Monday 8:00 AM?

 

[Jackajoo]

yep. that's what i mean. Thanks so much for your help!

 

[T. Valko]

Ok, need a bit of clarification.

If you want to exclude weekends then I'm assuming the start date/time in A1
will *never* be a weekend date/time. Is that correct?

In your first post you have the example:

A1 = 04/05/09 14:35 PM

So, exactly what date is that? Is it April 5 2009 which is a Sunday, or is
it May 4 2009 which is a Monday?

 

[Jackajoo]

Hello Valko -

You are correct - although it may happen, it would only be once in a great
while... the exception, not the rule.

As for the date - 04/05/09 is April 05, 2009.

Also, I don't think I replied to the following question you had:

"Do you mean if the date in cell A1 is a Friday then the date/time in B1
This is exactly what I mean.


Thanks so much Valko/Biff! You are very helpful!

 

[T. Valko]

Ok, try this...

=IF(COUNT(A1:B1)=2,IF(B1>INT(A1+CHOOSE(WEEKDAY(A1,2),1,1,1,1,3,2,1))+TIME(8,0,0),"N",""),"")

If the weekday in A1 is Monday to Thursday the formula checks to make sure
the weekday in B1 is no later than the next day 8:00 AM. If the weekday in
A1 is Friday to Sunday the formula checks to make sure the weekday in B1 is
no later than the following Monday 8:00 AM.

 

[Jackajoo]

Perfect!!! Thanks so much!!!

Ok, try this...

=IF(COUNT(A1:B1)=2,IF(B1>INT(A1+CHOOSE(WEEKDAY(A1,2),1,1,1,1,3,2,1))+TIME(8,0,0),"N",""),"")

If the weekday in A1 is Monday to Thursday the formula checks to make sure
the weekday in B1 is no later than the next day 8:00 AM. If the weekday in
A1 is Friday to Sunday the formula checks to make sure the weekday in B1 is
no later than the following Monday 8:00 AM.

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

You're welcome. Thanks for the feedback!