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

Have you looked in the help index for TIME.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

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

--
---
HTH

Bob


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



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

"Peggy" skrev i en meddelelse
...
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

Hi Peggg

Try this

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

Just remeber to format the cell as Date.

Regards,

Per

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


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

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

"Bob Phillips" wrote:

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

--
---
HTH

Bob


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



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