| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to record the date
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to record the date
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to record the date
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to record the date
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to record the date
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to record the date
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to record the date
=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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to record the date
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to record the date
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Record entry by date | Excel Worksheet Functions | |||
| Formula that will record the time and date when an entry is made on a sheet | Excel Worksheet Functions | |||
| Date function to record changes within a row | Excel Worksheet Functions | |||
| How can I count a record if it contains a date within a date range | Excel Worksheet Functions | |||
| How can I record the date of last revision in footer? | Excel Worksheet Functions | |||
Hybrid Mode
