Hi,

I've been working with the following formula:
IF(B2<time(14,0,0),today()+1, today()).

What the formula should do is determine if it is before 2pm on a given day
then put today's day. If it's after 2pm then put tomorrow's date. I have
formatted cell B2 to state =Now().

Unfortunately, it's not working and I always get tomorrow's date. Any ideas
how I can fix this to make the formula extremely robust?

Many thanks,
Peggy

It all depends upon what is in B2. What is in there?


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Peggy" wrote in message
...
Hi,

I've been working with the following formula:
IF(B2<time(14,0,0),today()+1, today()).

What the formula should do is determine if it is before 2pm on a given day
then put today's day. If it's after 2pm then put tomorrow's date. I have
formatted cell B2 to state =Now().

Unfortunately, it's not working and I always get tomorrow's date. Any
ideas
how I can fix this to make the formula extremely robust?

Many thanks,
Peggy

Hi Bob,
I have =Now() in B2 so it records the current date and time.

"Bob Phillips" wrote:

It all depends upon what is in B2. What is in there?


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Peggy" wrote in message
...
Hi,

I've been working with the following formula:
IF(B2<time(14,0,0),today()+1, today()).

What the formula should do is determine if it is before 2pm on a given day
then put today's day. If it's after 2pm then put tomorrow's date. I have
formatted cell B2 to state =Now().

Unfortunately, it's not working and I always get tomorrow's date. Any
ideas
how I can fix this to make the formula extremely robust?

Many thanks,
Peggy

Oops, you did actually say that didn't you?

It works okay for me Peggy, at the moment it is showing 30th Jan for me (it
is 11:12AM here)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Peggy" wrote in message
...
Hi Bob,
I have =Now() in B2 so it records the current date and time.

"Bob Phillips" wrote:

It all depends upon what is in B2. What is in there?


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Peggy" wrote in message
...
Hi,

I've been working with the following formula:
IF(B2<time(14,0,0),today()+1, today()).

What the formula should do is determine if it is before 2pm on a given
day
then put today's day. If it's after 2pm then put tomorrow's date. I
have
formatted cell B2 to state =Now().

Unfortunately, it's not working and I always get tomorrow's date. Any
ideas
how I can fix this to make the formula extremely robust?

Many thanks,
Peggy

Hi Bob,
That's slightly reassuring. However, could mine not be working because I'm
still on excel 2000?

Thanks for your help.

Regards,
Peggy

"Bob Phillips" wrote:

Oops, you did actually say that didn't you?

It works okay for me Peggy, at the moment it is showing 30th Jan for me (it
is 11:12AM here)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Peggy" wrote in message
...
Hi Bob,
I have =Now() in B2 so it records the current date and time.

"Bob Phillips" wrote:

It all depends upon what is in B2. What is in there?


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Peggy" wrote in message
...
Hi,

I've been working with the following formula:
IF(B2<time(14,0,0),today()+1, today()).

What the formula should do is determine if it is before 2pm on a given
day
then put today's day. If it's after 2pm then put tomorrow's date. I
have
formatted cell B2 to state =Now().

Unfortunately, it's not working and I always get tomorrow's date. Any
ideas
how I can fix this to make the formula extremely robust?

Many thanks,
Peggy

=Now() includes the date and time.

So any date+time will be larger than any time alone.

How about
=IF(mod(B2,1)<time(14,0,0),today()+1, today())

=mod(b2,1)
will return just the time portion of =now()



Peggy wrote:

Hi,

I've been working with the following formula:
IF(B2<time(14,0,0),today()+1, today()).

What the formula should do is determine if it is before 2pm on a given day
then put today's day. If it's after 2pm then put tomorrow's date. I have
formatted cell B2 to state =Now().

Unfortunately, it's not working and I always get tomorrow's date. Any ideas
how I can fix this to make the formula extremely robust?

Many thanks,
Peggy

--

Dave Peterson

Nothing to do with it being Excel 2000.
TIME(14,0,0) is represented by 0.583333 (as times are represented as
decimals of a day)
NOW() returns a number like 39477.506, as NOW contains date and time, and
the date is 39477 days from Excel's time origin at the start of the year
1900.
You are comparing NOW with TIME(14,0,0), and it is never going to be
smaller, so the result of your formula will always give TODAY(), not
TODAY()+1.

If you want to check the current time (ignoring the date) against 14:00,
change your formula to
=IF(MOD(B2,1)<TIME(14,0,0),TODAY()+1, TODAY())
or alternatively change the =NOW() in B2 to =MOD(B2,1) and you can then use
your existing formula.
--
David Biddulph

"Peggy" wrote in message
...
Hi Bob,
That's slightly reassuring. However, could mine not be working because
I'm
still on excel 2000?

Thanks for your help.

Regards,
Peggy

"Bob Phillips" wrote:

Oops, you did actually say that didn't you?

It works okay for me Peggy, at the moment it is showing 30th Jan for me
(it
is 11:12AM here)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Peggy" wrote in message
...
Hi Bob,
I have =Now() in B2 so it records the current date and time.

"Bob Phillips" wrote:

It all depends upon what is in B2. What is in there?


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Peggy" wrote in message
...
Hi,

I've been working with the following formula:
IF(B2<time(14,0,0),today()+1, today()).

What the formula should do is determine if it is before 2pm on a
given
day
then put today's day. If it's after 2pm then put tomorrow's date.
I
have
formatted cell B2 to state =Now().

Unfortunately, it's not working and I always get tomorrow's date.
Any
ideas
how I can fix this to make the formula extremely robust?

Many thanks,
Peggy

Hi Peggy,

This?

=IF(MOD(B2,1)TIME(14,0,0),TODAY()+1, TODAY())

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Peggy" wrote in message ...
| Hi,
|
| I've been working with the following formula:
| IF(B2<time(14,0,0),today()+1, today()).
|
| What the formula should do is determine if it is before 2pm on a given day
| then put today's day. If it's after 2pm then put tomorrow's date. I have
| formatted cell B2 to state =Now().
|
| Unfortunately, it's not working and I always get tomorrow's date. Any ideas
| how I can fix this to make the formula extremely robust?
|
| Many thanks,
| Peggy

Hi guys,
thanks for all your replies. Unfortunatley, it looks like it still isn't
working.

I tried the =MOD(B2,1) in cell B2, but it just flagged up an error for the
circular reference.

thanks anyway. I guess I'd like to do something Excel is not able to cope
with?

Regards,
Peggy

"David Biddulph" wrote:

Nothing to do with it being Excel 2000.
TIME(14,0,0) is represented by 0.583333 (as times are represented as
decimals of a day)
NOW() returns a number like 39477.506, as NOW contains date and time, and
the date is 39477 days from Excel's time origin at the start of the year
1900.
You are comparing NOW with TIME(14,0,0), and it is never going to be
smaller, so the result of your formula will always give TODAY(), not
TODAY()+1.

If you want to check the current time (ignoring the date) against 14:00,
change your formula to
=IF(MOD(B2,1)<TIME(14,0,0),TODAY()+1, TODAY())
or alternatively change the =NOW() in B2 to =MOD(B2,1) and you can then use
your existing formula.
--
David Biddulph

"Peggy" wrote in message
...
Hi Bob,
That's slightly reassuring. However, could mine not be working because
I'm
still on excel 2000?

Thanks for your help.

Regards,
Peggy

"Bob Phillips" wrote:

Oops, you did actually say that didn't you?

It works okay for me Peggy, at the moment it is showing 30th Jan for me
(it
is 11:12AM here)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Peggy" wrote in message
...
Hi Bob,
I have =Now() in B2 so it records the current date and time.

"Bob Phillips" wrote:

It all depends upon what is in B2. What is in there?


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Peggy" wrote in message
...
Hi,

I've been working with the following formula:
IF(B2<time(14,0,0),today()+1, today()).

What the formula should do is determine if it is before 2pm on a
given
day
then put today's day. If it's after 2pm then put tomorrow's date.
I
have
formatted cell B2 to state =Now().

Unfortunately, it's not working and I always get tomorrow's date.
Any
ideas
how I can fix this to make the formula extremely robust?

Many thanks,
Peggy

Hi Peggy,

<change the =NOW() in B2 to =MOD(B2,1)

Should be

change the =NOW() in B2 to =MOD(NOW(),1)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"David Biddulph" <groups [at] biddulph.org.uk wrote in message ...
| Nothing to do with it being Excel 2000.
| TIME(14,0,0) is represented by 0.583333 (as times are represented as
| decimals of a day)
| NOW() returns a number like 39477.506, as NOW contains date and time, and
| the date is 39477 days from Excel's time origin at the start of the year
| 1900.
| You are comparing NOW with TIME(14,0,0), and it is never going to be
| smaller, so the result of your formula will always give TODAY(), not
| TODAY()+1.
|
| If you want to check the current time (ignoring the date) against 14:00,
| change your formula to
| =IF(MOD(B2,1)<TIME(14,0,0),TODAY()+1, TODAY())
| or alternatively change the =NOW() in B2 to =MOD(B2,1) and you can then use
| your existing formula.
| --
| David Biddulph
|
| "Peggy" wrote in message
| ...
| Hi Bob,
| That's slightly reassuring. However, could mine not be working because
| I'm
| still on excel 2000?
|
| Thanks for your help.
|
| Regards,
| Peggy
|
| "Bob Phillips" wrote:
|
| Oops, you did actually say that didn't you?
|
| It works okay for me Peggy, at the moment it is showing 30th Jan for me
| (it
| is 11:12AM here)
|
| --
| ---
| HTH
|
| Bob
|
|
| (there's no email, no snail mail, but somewhere should be gmail in my
| addy)
|
|
|
| "Peggy" wrote in message
| ...
| Hi Bob,
| I have =Now() in B2 so it records the current date and time.
|
| "Bob Phillips" wrote:
|
| It all depends upon what is in B2. What is in there?
|
|
| --
| ---
| HTH
|
| Bob
|
|
| (there's no email, no snail mail, but somewhere should be gmail in my
| addy)
|
|
|
| "Peggy" wrote in message
| ...
| Hi,
|
| I've been working with the following formula:
| IF(B2<time(14,0,0),today()+1, today()).
|
| What the formula should do is determine if it is before 2pm on a
| given
| day
| then put today's day. If it's after 2pm then put tomorrow's date.
| I
| have
| formatted cell B2 to state =Now().
|
| Unfortunately, it's not working and I always get tomorrow's date.
| Any
| ideas
| how I can fix this to make the formula extremely robust?
|
| Many thanks,
| Peggy
|
|
|
|
|
|
|
|

Sorry, what I intended to say was =MOD(NOW(),1)
--
David Biddulph

"Peggy" wrote in message
...
Hi guys,
thanks for all your replies. Unfortunatley, it looks like it still isn't
working.

I tried the =MOD(B2,1) in cell B2, but it just flagged up an error for the
circular reference.

thanks anyway. I guess I'd like to do something Excel is not able to cope
with?

Regards,
Peggy

"David Biddulph" wrote:

Nothing to do with it being Excel 2000.
TIME(14,0,0) is represented by 0.583333 (as times are represented as
decimals of a day)
NOW() returns a number like 39477.506, as NOW contains date and time, and
the date is 39477 days from Excel's time origin at the start of the year
1900.
You are comparing NOW with TIME(14,0,0), and it is never going to be
smaller, so the result of your formula will always give TODAY(), not
TODAY()+1.

If you want to check the current time (ignoring the date) against 14:00,
change your formula to
=IF(MOD(B2,1)<TIME(14,0,0),TODAY()+1, TODAY())
or alternatively change the =NOW() in B2 to =MOD(B2,1) and you can then
use
your existing formula.
--
David Biddulph

"Peggy" wrote in message
...
Hi Bob,
That's slightly reassuring. However, could mine not be working because
I'm
still on excel 2000?

Thanks for your help.

Regards,
Peggy

"Bob Phillips" wrote:

Oops, you did actually say that didn't you?

It works okay for me Peggy, at the moment it is showing 30th Jan for
me
(it
is 11:12AM here)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Peggy" wrote in message
...
Hi Bob,
I have =Now() in B2 so it records the current date and time.

"Bob Phillips" wrote:

It all depends upon what is in B2. What is in there?


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Peggy" wrote in message
...
Hi,

I've been working with the following formula:
IF(B2<time(14,0,0),today()+1, today()).

What the formula should do is determine if it is before 2pm on a
given
day
then put today's day. If it's after 2pm then put tomorrow's
date.
I
have
formatted cell B2 to state =Now().

Unfortunately, it's not working and I always get tomorrow's date.
Any
ideas
how I can fix this to make the formula extremely robust?

Many thanks,
Peggy

Peggy, go back and read the replies. You'll be much better off with Excel
when you assume the only reason Excel isn't doing what you want is because
of operator error.

Leave B2 alone. Change your formula, not B2.

Regards,
Fred.

"Peggy" wrote in message
...
Hi guys,
thanks for all your replies. Unfortunatley, it looks like it still isn't
working.

I tried the =MOD(B2,1) in cell B2, but it just flagged up an error for the
circular reference.

thanks anyway. I guess I'd like to do something Excel is not able to cope
with?

Regards,
Peggy

"David Biddulph" wrote:

Nothing to do with it being Excel 2000.
TIME(14,0,0) is represented by 0.583333 (as times are represented as
decimals of a day)
NOW() returns a number like 39477.506, as NOW contains date and time, and
the date is 39477 days from Excel's time origin at the start of the year
1900.
You are comparing NOW with TIME(14,0,0), and it is never going to be
smaller, so the result of your formula will always give TODAY(), not
TODAY()+1.

If you want to check the current time (ignoring the date) against 14:00,
change your formula to
=IF(MOD(B2,1)<TIME(14,0,0),TODAY()+1, TODAY())
or alternatively change the =NOW() in B2 to =MOD(B2,1) and you can then
use
your existing formula.
--
David Biddulph

"Peggy" wrote in message
...
Hi Bob,
That's slightly reassuring. However, could mine not be working because
I'm
still on excel 2000?

Thanks for your help.

Regards,
Peggy

"Bob Phillips" wrote:

Oops, you did actually say that didn't you?

It works okay for me Peggy, at the moment it is showing 30th Jan for
me
(it
is 11:12AM here)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Peggy" wrote in message
...
Hi Bob,
I have =Now() in B2 so it records the current date and time.

"Bob Phillips" wrote:

It all depends upon what is in B2. What is in there?


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Peggy" wrote in message
...
Hi,

I've been working with the following formula:
IF(B2<time(14,0,0),today()+1, today()).

What the formula should do is determine if it is before 2pm on a
given
day
then put today's day. If it's after 2pm then put tomorrow's
date.
I
have
formatted cell B2 to state =Now().

Unfortunately, it's not working and I always get tomorrow's date.
Any
ideas
how I can fix this to make the formula extremely robust?

Many thanks,
Peggy

Hi everyone,

It seems to working now!
Thanks for your help.

Kind regards,
Peggy

"Niek Otten" wrote:

Hi Peggy,

This?

=IF(MOD(B2,1)TIME(14,0,0),TODAY()+1, TODAY())

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Peggy" wrote in message ...
| Hi,
|
| I've been working with the following formula:
| IF(B2<time(14,0,0),today()+1, today()).
|
| What the formula should do is determine if it is before 2pm on a given day
| then put today's day. If it's after 2pm then put tomorrow's date. I have
| formatted cell B2 to state =Now().
|
| Unfortunately, it's not working and I always get tomorrow's date. Any ideas
| how I can fix this to make the formula extremely robust?
|
| Many thanks,
| Peggy