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