Time - By 8am the next morning
 

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

Time - By 8am the next morning
 

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.

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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!!!

Time - By 8am the next morning
 

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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!!!


.

Time - By 8am the next morning
 

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(B1INT(A1+1)+TIME(8,0,0),"N" ,""),"")

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

=IF(B1INT(A1+1)+TIME(8,0,0),"N","")

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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!!!


.

Time - By 8am the next morning
 

This is great - however - is there any way I can exclude weekends? I just
copiy andpasted the formula and it worked!!!
"T. Valko" wrote:

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(B1INT(A1+1)+TIME(8,0,0),"N" ,""),"")

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

=IF(B1INT(A1+1)+TIME(8,0,0),"N","")

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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!!!


.



.

Time - By 8am the next morning
 

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?

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
This is great - however - is there any way I can exclude weekends? I just
copiy andpasted the formula and it worked!!!
"T. Valko" wrote:

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(B1INT(A1+1)+TIME(8,0,0),"N" ,""),"")

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

=IF(B1INT(A1+1)+TIME(8,0,0),"N","")

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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!!!


.



.

Time - By 8am the next morning
 

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

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
This is great - however - is there any way I can exclude weekends? I just
copiy andpasted the formula and it worked!!!
"T. Valko" wrote:

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(B1INT(A1+1)+TIME(8,0,0),"N" ,""),"")

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

=IF(B1INT(A1+1)+TIME(8,0,0),"N","")

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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!!!


.



.



.

Time - By 8am the next morning
 

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?

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
yep. that's what i mean. Thanks so much for your help!

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
This is great - however - is there any way I can exclude weekends? I
just
copiy andpasted the formula and it worked!!!
"T. Valko" wrote:

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(B1INT(A1+1)+TIME(8,0,0),"N" ,""),"")

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

=IF(B1INT(A1+1)+TIME(8,0,0),"N","")

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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!!!


.



.



.

Time - By 8am the next morning
 

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
has to before Monday 8:00 AM?"

This is exactly what I mean.


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


"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
yep. that's what i mean. Thanks so much for your help!

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
This is great - however - is there any way I can exclude weekends? I
just
copiy andpasted the formula and it worked!!!
"T. Valko" wrote:

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(B1INT(A1+1)+TIME(8,0,0),"N" ,""),"")

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

=IF(B1INT(A1+1)+TIME(8,0,0),"N","")

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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!!!


.



.



.



.

Time - By 8am the next morning
 

Ok, try this...

=IF(COUNT(A1:B1)=2,IF(B1INT(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


"Jackajoo" wrote in message
...
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
has to before Monday 8:00 AM?"

This is exactly what I mean.


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


"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
yep. that's what i mean. Thanks so much for your help!

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
This is great - however - is there any way I can exclude weekends?
I
just
copiy andpasted the formula and it worked!!!
"T. Valko" wrote:

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(B1INT(A1+1)+TIME(8,0,0),"N" ,""),"")

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

=IF(B1INT(A1+1)+TIME(8,0,0),"N","")

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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!!!


.



.



.



.

Time - By 8am the next morning
 

Perfect!!! Thanks so much!!!

"T. Valko" wrote:

Ok, try this...

=IF(COUNT(A1:B1)=2,IF(B1INT(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


"Jackajoo" wrote in message
...
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
has to before Monday 8:00 AM?"

This is exactly what I mean.


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


"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
yep. that's what i mean. Thanks so much for your help!

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
This is great - however - is there any way I can exclude weekends?
I
just
copiy andpasted the formula and it worked!!!
"T. Valko" wrote:

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(B1INT(A1+1)+TIME(8,0,0),"N" ,""),"")

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

=IF(B1INT(A1+1)+TIME(8,0,0),"N","")

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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!!!


.



.



.



.



.

Time - By 8am the next morning
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
Perfect!!! Thanks so much!!!

"T. Valko" wrote:

Ok, try this...

=IF(COUNT(A1:B1)=2,IF(B1INT(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


"Jackajoo" wrote in message
...
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
has to before Monday 8:00 AM?"

This is exactly what I mean.


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


"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
yep. that's what i mean. Thanks so much for your help!

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
This is great - however - is there any way I can exclude
weekends?
I
just
copiy andpasted the formula and it worked!!!
"T. Valko" wrote:

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(B1INT(A1+1)+TIME(8,0,0),"N" ,""),"")

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

=IF(B1INT(A1+1)+TIME(8,0,0),"N","")

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Jackajoo" wrote in
message
...
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!!!


.



.



.



.



.