Can I use the NOW() function in a formula?

I have a cell B1 that has NOW() time and another cell B2 that has NOW() date.  
I am trying to have data from another cell F1 brought in to the destination 
cell B3,
when a given date and time occur.  
=(IF(AND(B1="23:00:00",B2="12/03/2009"),F1,"  ")
This formulas does not work. 
The Clock is continuously active / always changing.
Any suggestions would be appreciated.  Thanks.
 
0
Utf
12/3/2009 1:40:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
604 Views

Similar Articles

[PageSpeed] 47

The NOW() function returns both a date and a time. Even if you format the 
cell to display only date/time, all the data is still retained. NOte that if 
you did want just the date, you could use the TODAY() function. Your formula 
literally corrected would be:


=IF(NOW()=TIMEVALUE("23:00:00")+DATEVALUE("12/03/2009"),F1,"  ")

The problem is, this criteria would only be met for a few nanoseconds! 
Keeping in mind that time is stored as a decimal portion of a number, perhaps 
what you really want is:
=IF(AND(TODAY()=DATEVALUE("12/03/2009"),MOD(NOW(),1)>TIMEVALUE("23:00:00")),F1," ")

Note that if you change the date and time checks to cell references, it 
would make this formula slightly shorter.
-- 
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Smith512" wrote:

> I have a cell B1 that has NOW() time and another cell B2 that has NOW() date.  
> I am trying to have data from another cell F1 brought in to the destination 
> cell B3,
> when a given date and time occur.  
> =(IF(AND(B1="23:00:00",B2="12/03/2009"),F1,"  ")
> This formulas does not work. 
> The Clock is continuously active / always changing.
> Any suggestions would be appreciated.  Thanks.
>  
0
Utf
12/3/2009 1:58:01 PM
At 3:33 PM today I entered this into a cell
=IF(TIME(HOUR(NOW()),MINUTE(NOW()),0)>=TIME(15,37,0),"X","not yet")
It displayed NOT YET
I pressed F9 every 10 seconds to recalculate the worksheet
When the time on taskbar showed 3:37 PM the cell displayed X

For the Date I would use DATE(2009,12,13) rather than DATEVALUE to be 
date-format independent

=IF(AND(TODAY()=DATE(2009,12,13),TIME(HOUR(NOW()),MINUTE(NOW()),0)>=TIME(15,37,0),F1," 
")

best wishes
-- 
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"Smith512" <Smith512@discussions.microsoft.com> wrote in message 
news:36BA1B48-94AE-411E-AA65-AE990149B0AC@microsoft.com...
> I have a cell B1 that has NOW() time and another cell B2 that has NOW() 
> date.
> I am trying to have data from another cell F1 brought in to the 
> destination
> cell B3,
> when a given date and time occur.
> =(IF(AND(B1="23:00:00",B2="12/03/2009"),F1,"  ")
> This formulas does not work.
> The Clock is continuously active / always changing.
> Any suggestions would be appreciated.  Thanks.
> 
0
Bernard
12/3/2009 7:44:15 PM
Reply:

Similar Artilces:

Average formula where blank cells are counted as zeros
I am trying to write an average formula that takes into account the blank cells. I had only cells A2, A4, A6 filled out, but when I do =average(A1:A6), it is not treating the blanks as zeros so the answer is much higher than it should be. Should I do a logic formula within the cells? If you know the range, you could do the division yourself: =sum(a1:a6)/6 But this would include other non-numeric cells in the count of cells (6), too. krwelling wrote: > > I am trying to write an average formula that takes into account the blank > cells. > > I ha...

How can I rearrange the toolbars?
I've just now installed IE8 and I've checked the help files but am unable to find out how I can change to order of the toolbars. I am using RoboForm and I would really like to have it as the lowest toolbar. Any help greatly appreciated? Thanks, Lorraine ~~~~~~~~~~~~~~~~~~~~~~~~~ Customization in IE8: http://blogs.msdn.com/ie/archive/2009/04/20/customization-in-ie8.aspx Customize IE8...to fit your needs http://windowsteamblog.com/blogs/windowsexperience/archive/2008/08/29/customize-ie8-beta-2-to-fit-your-needs.aspx Many third-party toolbars have their own, ...

Is there a way I can only print the reply instead of the entire email message
I wonder if there is a way I can only print the reply to an email message, instead of printing the entire message? My choice for selection only seem to be gone with Outlook 2003 printing options. Any help is appreciated.............Peter ...

Using variable names for cells
I seem to remember a technique where I could assign a variable name to the contents of a cell so that whenever I wanted to use the contents, all I had to do was call up the variable name. Unfortunately I cannot find the way to set up the process. Any suggestsions or ideas would be appreciated. Thanks and a Happy New Year. -- Take out the trash to reply '05 FLHTCUI Hi dim rng as range set rng=activesheet.range("A1") msgbox rng.value -- Regards Frank Kabel Frankfurt, Germany Ultraglide wrote: > I seem to remember a technique where I could assign a variable name to >...

How can I access a public calendar from a custom form
I am trying to create a custom form that would allow a employee to request a vactation time frame. This form would then be mailed to a supervisor who could approve or disapprove the request. I am having difficulty getting the response posted to a public calendar vs. the managers private calendar. Help! -Jeff ...

Can a macro open another worksheet?
Hello, I've got a macro that I need to run on about 200 spreadsheets. Is there a way to get it to select and open all 200 spreadsheets? Thanks, Art. Are they all in the same folder? In fact, do you need to open all the workbooks in a folder? Or all the workbooks in multiple known folders? If you have files in one folder--but you want to choose (click on the first and ctrl-click on subsequent in the file|open dialog), you can do something like: Option Explicit Sub testme() Dim myFileNames As Variant Dim iCtr As Long Dim wkbk As Workbook myFileNames = Ap...

How do I use excel names with INDIRECT with charts
Hello, I want to create a Chart that does not directly reference cell-ranges (i.e. A1:A6), but excel-names that make the reference sheet-independent. My aim is to be able to copy one chart to other worksheets, which have their dataareas at the same places like the source-sheet. Problem: This works fine in cells but not in charts Excel name definition: =INDIRECT("R3C2";0):INDIRECT("R3C5";0) Any idea about this? Thanks in advance, Holger. You have to include the sheet name in the final formula that you want XL to use. -- Regards, Tushar Mehta www.tushar-mehta.co...

Can i display an Icon on a toolbar?
Hello, I'm building a toolbar but i do not want to manualy draw the buttons. Instead i want to be able to display prebuilt ICONs there. Can this be done? How do i proceed? Many thanks You should be able to do it by simply adding a CStatic on the toolbar and placing bitmap/icon on it. Following article shows how to add controls to the toolbar: http://www.codeguru.com/Cpp/controls/toolbar/placingcontrolsintoolbars/article.php/c2547/ ------------------ Ajay Kalra ajaykalra@yahoo.com "Bredal Jensen" <Bredal.Jensen@mimosa.com> wrote in message news:<eMRs7FziEHA.636@TK...

can someone explain what the icons mean
I have a question about the icons next to the email headings. for instance what does the little "i" mean next to the envelope that means it has tracking info attached. See http://www.outlook-tips.net/howto/icons_mail.htm -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM You can access this newsgroup by visit...

Formula Question (MPS)
i have a formula i use in a spreadsheet... = IF(YEAR(O$1)-YEAR(E3)=105,,YEAR(O$1)-YEAR(E3)) I use it to caculate ages where the current date is in cell O1 and the Person's birthday is shown in cell E3. It doesn't add up right because if the birthday is later in the year than the current day (shown in cell O1. ) It works fine if the birthday is. If the birthday has already happened this year, then the formula works fine. How shoud i adjust it? thanks. Mike instead of adjusting, how about using =DATEDIF(E3,O1,"y") or =DATEDIF(E3,TODAY(),"y") ?? ...

SBS2003 + Internet Explorer
We have a customer who has an SBS 2003 Premium server with ISA 2004 They have just installed a bolt on to Outlook which searches their mailboxes or something. They have discovered that they cannot get this to work unless the untick the 'Use automatic configuration script' in Internet Explorer. Trouble is each time they re-boot a PC the tick comes back, so sounds like it's a policy setting somewhere. What is this ? Where is it configured and what are the implications of turning it off ? Could it be that rather than turn it off whatever it is ought to be con...

Formula that checks balances
I have a long list of formulas in column "E". I need to know at a glance whether any value in this column is inequal to zero without having to page down all the way through thousands of rows. Summing the column is no good because there could be offsetting values (e.g. +5 + -5 = 0). I would like a formula I could place in one cell that would indicate if every formula in the column evaluates to zero or not (rounded to 2 decimals). Can anyone help? Thanks! "xp" wrote: > I would like a formula I could place in one cell > that would indicate if e...

formula #28
how do i show the formula when i need to print Hi You can use Ctrl and ` (which is to the right of number 1 on your heyboard) or you can use Tools / Options / View / Formulas (if my memory serves me right!) Hope this helps. -- Andy. "martin ekins" <martin_ekins69@hotmail.com> wrote in message news:5BE10AD0-0759-4AED-99A9-02203459FD0F@microsoft.com... > how do i show the formula when i need to print ...

can cvs files be opened using excel viewer
i have a user who cannot open cvs files with excel viewer but can ope with the full version of excel...anyone experienced this problem and i so, can you pls post the resolution -- darrie ----------------------------------------------------------------------- darriel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2388 View this thread: http://www.excelforum.com/showthread.php?threadid=37522 There is nothing in the description of the Excel Viewer to suggest that it can read anything other than true XLS files. http://support.microsoft.com/default.aspx?scid=kb;...

Manufacturing BOM Where Used query
Winthin manufacturing there is a where-used inquiry window that will show all BOMs that a given item appears in. You can also expand that view to see what BOMs and item's parent is used in. Some of our BOMs are 10 layers deep. In our environment we need to know which category of finished goods each purchased item is used in. Currently we maintain this manually and store the info in an inventory user-defined field. Over time as the number of our finished goods and purchased parts has increased this has become an unmangeable process. I need to find a way to query the BOM tables a...

Can I switch axes in a Worksheet?
Can I switch axes in a Worksheet? It was easier to enter data in the current setup but now it may be easier to analyze the data if I can switch the axes. Can it be done?? Again, I would just like to switch the x and y axes in the worksheet... Amy wrote: > Can I switch axes in a Worksheet? It was easier to enter > data in the current setup but now it may be easier to > analyze the data if I can switch the axes. ... Select the data and use Edit >> Copy Then go to another worksheet and select a cell (like A:1). Then use Edit >> Paste special >> Tra...

Can't activate trial version
I just bought the trial version of Publisher 2003 and I can't get it to activate. I've uninstalled it and reinstalled it twice, double checking the Product Key on the folder, but it keeps telling me that it's an invalid number. And, of course, there's no "support" provided by Microsoft. This is VERY frustrating. Any suggestions? By the way, the "activate by telephone" option isn't available on the trial, so I can't even call them. HELP! I'm unsure what the exact issue is. Are you unable to complete the installation because the program...

Frequency function
I was wondering if anyone could be of assistance with the frequency function in excel. I am trying to reduce data so that i can find out the number of times a particular results occurs. Every time i use the frequency function it either repeats the previous cell or returns the culumative frequency, can anyone help please? Hi! Countif or Sumproduct are less complicated! You need to provide some details. Biff "Pritesh" <Pritesh@discussions.microsoft.com> wrote in message news:E04CA99D-F8BE-4BEF-9384-EF72B8930993@microsoft.com... >I was wondering if anyone could be of ...

Can't get out of selection? Assistance would be most appreciated...
Perhaps I have done something inadvertently. I am in an Excel workbook and I appear to be in the middle of selecting a block of cells. Everytime I move the cursor, the cell selection area changes. I can't Esc out, or use the Enter key. Mousing over the menu, the menu does not respond. Rather silly really, but I desperately need to use this workbook and nothing seems to function. Even when the cursor is moving over the menu items, the cells selected in the workbook area still seems to be changing. I can't use the menu - the arrow keys don't work. Does anyone know how to &quo...

referring cell in macro function
If a create a macro intended to be used as a function in a worksheet is there a property of the referring cell? I want to know what cell the formula is in. Dim callCell As String callCell = Application.Caller.Address Returns the cell address of the cell where the UDF is used. "zxcv" <zxcvnosend@yahoo.com> wrote in message news:aef3cafd-4c52-40a6-8348-ebf957b3abad@a21g2000yqn.googlegroups.com... > If a create a macro intended to be used as a function in a worksheet > is there a property of the referring cell? > > I want to know what cell the form...

excel time formulas
I have a column which shows a format of hours, mins and seconds an would like to find a formula which can convert it into minutes only i.e 10:50.2 -- Message posted from http://www.ExcelForum.com Format the cells as [mm] -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "aly1cat >" <<aly1cat.185f1c@excelforum-nospam.com> wrote in message news:aly1cat.185f1c@excelforum-nospam.com... > I have a column which shows a format of hours, mins and seconds and > would like to find a f...

How do identify a blank cell in a formula
Example: IF(s69=blank,"Void".... What I want to say is if a cell, (s69in this example) is blank, enter the word "Void" but the above way don't work - I don't know how to enter that in this IF formula. Barb, here is one way =IF(ISBLANK(S69),"Void","") -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Barb123" <Barb123@discussions.microsoft.com> wrote in message news:47...

can't apply Office 2008 1224 update
List members: I regrettably was talked into reinstalling Office 2008 for the Mac on my Intel Mac running OS 10.6.2. Everything is fine except that I can't get Office 2008-1224 Update to apply. My HD appears with a yellow triangle over it, saying the update is inapplicable. I've tried trashing the product ID as Microsoft's web page suggests; no luck. No trial copies of Office anywhere on my HD. Would appreciate any advice; thanks in advance. - Mark Pavlick This is identical to the problem I reported on 25 March (Installing Office 2008). In my case the prob...

Moving Average Formula Problem
I have a workbook with a date on worksheet 1 that indicates data has been entered for that month. On worksheet 2 is are columns of numbers with an average for each column at the bottom. I would like the average to use the date on worksheet 1 to calculate the average. The start of the range is known, but the end of the range is found only by looking up the date on worksheet 1. I have tried to use something like: =AVERAGE(B10:ADDRESS(VLOOKUP(date, range, 1, TRUE). This doesn't work and I can't find the functions needed to make it successful. Thanks in advance for any help! Needs...

Input formulas in a defined data range and convert results as valu
Hi, I have a dataset for users to view data. It has the following format till year 2010 and have portions of topics like forecast, shipment, aging .... and the whole dataset can go very long vertically. Within each topics is the type of products measured, below is an extracts of my dataset: Forecast Accuracy Jan 2007 Feb 2007 Mar 2007 Apr 2007 Prod A x x x x Prod B x x x x Prod C ...