Formula to ask what Time/Date it is.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peggy - 16 Jan 2008 13:29 GMT

Hi,

Do you have any ideas what formula I could put together to set up the
following criteria:

Purpose: to determine what time/date a cell is populated with.

Action: If the date & time are < 14:00 then put today's date, if it's after
2pm then put tomorrow's date. I tried writing one:
=IF(D4<14:00,today(),today()+1) but for some reason excel didn't like what I
wrote.

I'm keen to put this formula into a macro so no one can alter the formula.
I'm happy doing that, it's just IF statements always trip me up!

Thanks for your help.
Peggy

Reply to this Message

Don Guillett - 16 Jan 2008 13:41 GMT

Have you looked in the help index for TIME.

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi,
>
[quoted text clipped - 15 lines]
> Thanks for your help.
> Peggy

Reply to this Message

Bob Phillips - 16 Jan 2008 13:41 GMT

=IF(D4<TIME(14,0,0),today(),today()+1)

Signature

---
HTH

Bob

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

> Hi,
>
[quoted text clipped - 15 lines]
> Thanks for your help.
> Peggy

Reply to this Message

Peggy - 16 Jan 2008 14:38 GMT

Thank you everyone for your replies and help with this. Bob, your formula
has worked magic!!! Thanks again,
Peggy

> =IF(D4<TIME(14,0,0),today(),today()+1)
>
[quoted text clipped - 17 lines]
> > Thanks for your help.
> > Peggy

Reply to this Message

Per Jessen - 16 Jan 2008 13:45 GMT

> Hi,
>
[quoted text clipped - 15 lines]
> Thanks for your help.
> Peggy

Hi Peggg

Try this

=IF(HOUR(D4)<14;NOW();NOW()+1)

Just remeber to format the cell as Date.

Regards,

Per

Reply to this Message

Rita Palazzi - 16 Jan 2008 13:48 GMT

May not be the suggestion you're looking for, but I put 14:00 in a cell,
say A1, then the following worked

=if(d4<A1, today(), today()+1)

I'm assuming D4 has the time field you're wanting to measure against.

I know it's all in the proper formatting of the time, but not sure how
to get there...

Rita

> Hi,
>
[quoted text clipped - 13 lines]
> Thanks for your help.
> Peggy

Reply to this Message