Tricky Scenario finding days/months/years! #2

I have tried both your solutions, i return a #VALUE! Error when place
in both scenarios.

Here is the method in which i set up the data.

SMS	                    SME                 	Date	      Result

Mon 29/12/2003	Sun 25/01/2004	01/03/04	=VLOOKUP((G5),2,2,(E5:F16))
Mon 26/01/2004	Sun 29/02/2004		
Mon 01/03/2004	Sun 28/03/2004		               =TEXT(G5,E5:F16)
Mon 29/03/2004	Sun 25/04/2004		
Mon 26/04/2004	Sun 30/05/2004		
Mon 31/05/2004	Sun 27/06/2004		
Mon 28/06/2004	Sun 25/07/2004		
Mon 26/07/2004	Sun 29/08/2004		
Mon 30/08/2004	Sun 26/09/2004		
Mon 27/09/2004	Sun 31/10/2004		
Mon 01/11/2004	Sun 28/11/2004		
Mon 29/11/2004	Sun 26/12/2004		


As you can see, my cell reference for the above service dates ar
within the range E5:F16. Cell G5 is the Date i want to check agains
the service dates.

If this is plausable, please help.

Thanks. 

PSA

+-------------------------------------------------------------------
|Filename: Test.zip                                                 
|Download: http://www.excelforum.com/attachment.php?postid=2788     
+-------------------------------------------------------------------

--
Rizits
-----------------------------------------------------------------------
Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1584
View this thread: http://www.excelforum.com/showthread.php?threadid=31482

0
11/18/2004 1:52:15 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
513 Views

Similar Articles

[PageSpeed] 13

have a look at thi

--
Lynxbci
-----------------------------------------------------------------------
Lynxbci3's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1037
View this thread: http://www.excelforum.com/showthread.php?threadid=31482

0
11/18/2004 2:14:21 PM
i will attach this tim

+-------------------------------------------------------------------
|Filename: Test2.zip                                                
|Download: http://www.excelforum.com/attachment.php?postid=2789     
+-------------------------------------------------------------------

--
Lynxbci
-----------------------------------------------------------------------
Lynxbci3's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1037
View this thread: http://www.excelforum.com/showthread.php?threadid=31482

0
11/18/2004 2:16:44 PM
Hi
if these are real date/time values
TEXT(G5,"MMMM")
should do

"Rizitsu" wrote:

> 
> I have tried both your solutions, i return a #VALUE! Error when placed
> in both scenarios.
> 
> Here is the method in which i set up the data.
> 
> SMS	                    SME                 	Date	      Result
> 
> Mon 29/12/2003	Sun 25/01/2004	01/03/04	=VLOOKUP((G5),2,2,(E5:F16))
> Mon 26/01/2004	Sun 29/02/2004		
> Mon 01/03/2004	Sun 28/03/2004		               =TEXT(G5,E5:F16)
> Mon 29/03/2004	Sun 25/04/2004		
> Mon 26/04/2004	Sun 30/05/2004		
> Mon 31/05/2004	Sun 27/06/2004		
> Mon 28/06/2004	Sun 25/07/2004		
> Mon 26/07/2004	Sun 29/08/2004		
> Mon 30/08/2004	Sun 26/09/2004		
> Mon 27/09/2004	Sun 31/10/2004		
> Mon 01/11/2004	Sun 28/11/2004		
> Mon 29/11/2004	Sun 26/12/2004		
> 
> 
> As you can see, my cell reference for the above service dates are
> within the range E5:F16. Cell G5 is the Date i want to check against
> the service dates.
> 
> If this is plausable, please help.
> 
> Thanks. 
> 
> PSA!
> 
> 
> +-------------------------------------------------------------------+
> |Filename: Test.zip                                                 |
> |Download: http://www.excelforum.com/attachment.php?postid=2788     |
> +-------------------------------------------------------------------+
> 
> -- 
> Rizitsu
> ------------------------------------------------------------------------
> Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15840
> View this thread: http://www.excelforum.com/showthread.php?threadid=314826
> 
> 
0
frank.kabel (11126)
11/18/2004 2:40:03 PM
Reply:

Similar Artilces:

MAPISP32 #2
MAPISP32 has taken over - uses almost all of the CPU (97 to 99%). Can't send or receive email. Restarting and not launching OUTLOOK gives me back my computer, but no email. Using OUTLOOK 97 on an XP machine. Have checked the postings, but found nothing about this problem (I get no error messages). Also tried renaming MAPISP32.dll and then reloading OUTLOOK, no improvement. Will check back later and thanks! ...

Beginning Inventory Balance #2
I am trying to duplicate the Average Inventory, according to KB856731, the formula is; (Beginning Inventory Balance + Summary of each months ending on hand Inventory + Current On Hand Inventory) / (number of months elapsed in the current year +2) Does anyone know what the formula is for the Beginning Inventory Balance? -- Anthony ...

Macro Security #2
Hi, I am trying to set the Macro security level to Low in Access 2003 but cannot find the option in the Tools\Macro menu. It is not even grayed out. The install is a complete install of Office 2003 and I tried a Detect and Repair without luck. Any suggestions? Thank you. Maybe a post in one of the Access newsgroups would get you an answer quicker. Clementius wrote: > > Hi, > I am trying to set the Macro security level to Low in Access 2003 but cannot > find the option in the Tools\Macro menu. It is not even grayed out. The > install is a complete install of Office 2003 ...

Spaces issue not fixed in 12.2
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel The previous threads have all been closed and none of them seem to have an answer. For anyone else struggling with Office not working with Spaces (and therefore having to disable Spaces to use Office 2008), here is the reply I just received from MS Office for Mac Tech Support: "The Spaces issue is still being worked on, please watch for future OS and Office updates" My guess is that as it has not been fixed in two service packs it is unlikely to change until Office 2012 (or whatever the next major release wil...

Client reboot every day at 3am
WIndows server 2003 clients configured Auto-update through WUSU server before. Just recently these servers reboot at 3am every night even removed from WUSU --AD intergrated. These servers were also reconfigured in registry --NoAutoUpdate ==Dword : 1. But there are still a few servers reboot at 3am . SUS clients ID were deleted from the registry but they always came back after reboot. Any advise ? [[ Right pew, wrong church - Forwarded to WSUS newsgroup (microsoft.public.windows.server.update_services) via crosspost as a convenience to OP ]] ginger wrote: > WIndow...

How to find a value with multi-column, multi-record list
I have a spreadsheet with $costs arranged according to values in both rows and columns. How do I create drop down lists for both the rows and columns and then return the cell value? eg if I have 5 rows (2-6) and 6 columns (B-G) with costs arranged in each cell within this group how do I create drop downs for each selection, 2-6 and B-G, and return the value to a selected cell. In a typical table of this nature there would be descriptive row and column headers. To do a lookup on this table you would then look for the intersection of a specific row header and a specific column ...

Conditional Formatting #2
Hello, I have a spreadsheet that has about 1200 rows many of which need to have conditional formatting. I had a template set of rows that I was transferring the conditional formatting from and it worked for 20 or so rows. Then in attempting to both copy/paste and drag formatting even further I noticed that the conditional formatting was not being transfered to the target cells. I have even tried doing one row of formatting at a time but it seems like it will not allow any more conditional formatting. The spreadsheet is a good size (about 2MB). I recieve no error messages about this. It simpl...

Printing 2 worksheets to a 2 sided document
Is there any way to print - front to back - 2 seperate worksheets? Turn the paper over and print the second worksheet???? Maybe you can create a worksheet with a picture of both ranges on it: Insert a new worksheet Edit|copy the first range shift-Edit|Paste Picture Link (on that new worksheet) Back for the second range and shift-edit|Paste picture link (right near your first pasted picture link). (insert a nice page break, too) And by pasting a link, you can change the original range and your picture will update right away. (Keep that worksheet as long as you want and print from there???)...

Export #2
Hi, How to export only the perticular group users. Regards Mustafa What sort of information do you need? "Mohammed Mustafa" <mohammed.mustafa@kharafinational.com> wrote in message news:OwKSDO$yGHA.3568@TK2MSFTNGP03.phx.gbl... > Hi, > > How to export only the perticular group users. > > Regards > Mustafa > See "IMI GAL Exporter" - http://www.imibo.com/imidev/Exchange/imige.htm "Mohammed Mustafa" <mohammed.mustafa@kharafinational.com> wrote in message news:OwKSDO$yGHA.3568@TK2MSFTNGP03.phx.gbl... > Hi, > > How ...

Summing of Different sites within a day
Hello! I have an employee attendance database for 2 sites, FL and AZ. My report is sorted first by date, then by site then by reason (there are 8, e.g. "Vacation") I have a sum in each Reason footer that gives me the total number of hours everyone at that site was out for a particular reason. I also have a sum in each date footer that gives me the total number of hours everyone at both sites was out for all reasons. What I need is this sum in the date footer to be broken up by Reason, regardless of site. Thank you for your help! I don't get how a "...

solver and macros #2
Before you use the solver within a macro, you must establish a reference to the Solver add-in. With a Visual Basic module active, click References on the Tools menu, and then select the Solver.xla check box under Available References. If Solver.xla doesn't appear under Available References, click Browse and open Solver.xla in the \Office\Library subfolder. Cheers Nick ...

Auto Number
Hi - In tblProvider I have ProcessYear and ProviderProcessNumber fields. In the bound form, frmProvider, I would like txtProcessProviderNumber to increment by 1 on each new record each new record entered during the Year. The year will be entered manually by the user via combo box in text field. Once the Process year changes to a new year I would want the numbers to start all over at 1. How can I do this. Any help would be greatly appreciated. -- Gary i n Michigan, USA GaryS wrote: >Hi - In tblProvider I have ProcessYear and ProviderProcessNumber fields. In >the bound form,...

Closing registers #2
1. Is there any way too remove the closing figure when the registers are closed... I don't want my cashiers to see the closing amounts. 2. Can a group of registers be closed out on a selected machine, e.g Reg 1, reg 2, reg 3, reg 4 are all closed on reg 1. Thanks If you don't want cashiers to see closing amounts, lock them out of doing x/z reports and viewing the journal - apply this setting in SO manager - database - cashiers - properties of each cashier - uncheck these boxes. On question 2, out of the box, RMS does not allow that - each register has its own batch that must ...

CRM v1.2 for MSDN?
We are a UNIVERSAL MSDN Subsciber which means we pay about $4000 a year to get the entire Microsoft Library. I have not seen CRM 1.2 in any of the updates which is very strange since I normally see them before any other subscription. Did I miss an update? I also notice on the MSDN downloads section they still only have v1.0 listed. Thanks! CRM 1.2 is only just shipping to partners etc. I would not expect it to appear on MSDN for a couple of months "MEI" <MEI@MEI.COM> wrote in message news:Of0KXo7sDHA.3496@TK2MSFTNGP11.phx.gbl... > We are a UNIVERSAL MSDN Subsciber whic...

hyperlinks #2
When I click on a hyperlink in outlook IE opens and stays blank, but another window opens and asks me to Locate link browser. I have been closing this window, but it reappears on every link. ??????????????? It may help you http://support.microsoft.com/default.aspx?scid=kb;en- us;329912&Product=out >-----Original Message----- >When I click on a hyperlink in outlook IE opens and stays >blank, but another window opens and asks me to Locate link >browser. I have been closing this window, but it reappears >on every link. ??????????????? >. > ...

Freezing Panes #2
How can I freeze two different rows in a single sheet. -- Naidu Pl don't multi-post. Check responses in .misc -- Max Singapore http://savefile.com/projects/236895 Downloads:17,000, Files:358, Subscribers:55 xdemechanik --- "Naidu" <Naidu@discussions.microsoft.com> wrote in message news:967FF8C4-431D-4BAE-9903-ECCDE18157D7@microsoft.com... > How can I freeze two different rows in a single sheet. > > -- > Naidu ...

Deleting Mail #2
This problem seems to be random over our network. We are running Windows Server 2000 with Outlook 2002. Some users are getting a notification that they have mail and when they check, there is nothing in their inbox. The new mail has automatically gone to the deleted items folder. It doesn't do it with all mail for that particular user or do all the users on the network experience this problem. There are no rules set up to do this. Has anyone else have this problem. If you have a suggestion please respond to my e-mail address DKimball@puc.nh.gov Thanks I have Outlook 2002 on...

Pay commissions to 2 salespersons
Is there a way to pay commissions to two different salespersons for one line item? When I try to enter secont salesperson in "Sales Commissions Entry" window I receive a message "The commission amount may not be greater the sale amount distributed to this salesperson" Commissions in GP are calculated a little differently than what we think about. If I have two salespeople and I want to give each a 5% commission on the sale, then I need to give each of them a 10% commission on 50% of the sale. -- Jim "Pay commissions to 2 salespersons" wrote: > Is th...

Find Last cell with Date
I need to find the last cell in a row that has a date entered. The date should then be entered into another cell which is updated as more dates are added to the row. Thanks for your help. Ken Russell Remove hat to reply by e-mail kenrussellhat@optushome.com.au --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.742 / Virus Database: 495 - Release Date: 19/08/2004 Hi for column A try: =LOOKUP(9.99999999999999E307,A:A) Note: - returns the last NUMERIC value. that is you can't differentiate between numbers and dates (as ...

create new worksheet based on month change
Hi, I have a problem I hope someone can help me with. I have a worksheet for employees to enter data. At the beginning of each month I run a macro which copies a mastersheet to start the new month. The first column of the sheet is for the employee to enter their name. Using code when they move to the 2nd column it automatically inserts the date and the 3rd column the time both based on whether there is an entry in the first column. I want to call my new worksheet macro when the date changes month. I have been trying to use ActiveCell.Offset command to look at the date in the row above but with...

Time #2
I need to be able to click on a cell and have it be populated with the current HH:MM:SS. I know ctrl+shift+; will do the job but I need it to be even more simple than that for the end users. One way: Put this in the worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) With Range("J1") If Not Intersect(Target, .Cells) Is Nothing Then .Value = Time .NumberFormat = "HH:MM:SS" End If End With End Sub ...

hyperlink #2
How do I make a hyperlink without underline? Thanks, Jo refer to http://www.publishermvps.com/Default.aspx?tabid=30 -- David Bartosik - MS MVP for Publisher help: www.davidbartosik.com enter to win Pub 2003: www.davidbartosik.com/giveaway.aspx "jo" <jo@discussions.microsoft.com> wrote in message news:1EA75A54-A237-4090-A4A7-EEEBE9DDA2B7@microsoft.com... > How do I make a hyperlink without underline? > > Thanks, > Jo ...

My mail is auto purged after 7 day!!!
Hi all, I am the network administrator. Recently a user complain that her email is auto purge every 7 days. I have checked user's outlook AutoArchive setting is not set. Only this user have problem, all of us is OK. Any clue for me what's going wrong? On Mon, 3 Oct 2005 19:12:01 -0700, "Cyber" <Cyber@discussions.microsoft.com> wrote: >Hi all, I am the network administrator. Recently a user complain that her >email is auto purge every 7 days. I have checked user's outlook AutoArchive >setting is not set. Only this user have problem, all of us is OK. A...

Bitmap button question #2
I'm trying to create a bitmap button. Here's what I've done: 1. Added a CStatic control to my dialog. 2. Set it's ID to IDC_BMPBUTTON 3. In the classwizzard, member varialbes tab, created a member variable m_Button, of type CBitmapButton (I had to manually change this from CStatic). 4. In the OnInitDialog(), I've added: m_BitmapButton.LoadBitmaps(IDB_BITMAP1, IDB_BITMAP2, IDB_BITMAP3, IDB_BITMAP4); m_BitmapButton.SizeToContent(); m_BitmapButton.ShowWindow(SW_SHOW); When I run my dialog, the bitmap appears grey. And which ...

Outlook and viruses #2
Hi folks, Obviously viruses need to be run, however I have heard of virus infected emails that need to be opened to infect a pc....not the attachment, the email. If this is true then does the mail window which allows one to see an email without opening it pose a risk? I do not open junk email, but it may open in this window. Thanks, Danny Yes, HTML messages can be a risk in older versions of Outlook. One of the first ones that I remember is the KAK worm. This bugger arrived as an innocent HTML message that exploited an Active-X component on the machine to place a file in a pre-d...