Concatenating date field into text ands using in a formula

Hi all,

Hope you can help.

I have a date field (B1) and am trying to concatenate this into a
directory path as part of a formula to pull data from another
worksheet.
IE My worksheets are named open"date of report".xls
B1 contains "date of report"
I have tried concatenating the worksheet name in a field and then
using that field.
I have tried cocatenating the directory path in a field and using that
field.
My last attempt at a formula is

=SUM(IF('=concatenate("D:\CSA\Reporting\Open
Jobs\[open"&TEXT(B1,"ddmmyy")&".xls]Customer")'!$I$3:$I$96=$A$41,IF('D:\CSA\Reporting\Open
Jobs\[open110903.xls]Customer'!$C$3:$C$96<B48,1,0),0))

Date is 110903, worksheet name is open110903.xls. 

If I manually type it in as below the formaula works dream.

=SUM(IF('D:\CSA\Reporting\Open
Jobs\[open110903.xls]Customer'!$I$3:$I$96=$A$41,IF('D:\CSA\Reporting\Open
Jobs\[open110903.xls]Customer'!$C$3:$C$96<B48,1,0),0))

Any assistance or direction pointing would be greatly appreciated.
I hope I have provided enough info.

Cheers Jason
0
9/11/2003 5:27:47 AM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
425 Views

Similar Articles

[PageSpeed] 5

Reply:

Similar Artilces:

Format a date
I have a column of dates - 8 digit, no slashes - example: 02032003 02052003 etc. I want to have them display 02/03/2003. When I go to format-cell-date-3-14-1998, I either get ########### (and the column is plenty wide enought for the new date to fit) or I get some really bizarre numbers - 02012003 is converted to 9/3/7408. Any ideas what is going on? Thanks! Kathie, If you want to use date formatting, your dates must be Excel date-time serial numbers. Yours aren't. See www.cpearson.com for details on how that works. You can convert your "dates" to real XL dates with...

Text Field Validation Rule for Date
Hi, I have a RESPONSE field on my table. This field is set up as a text field (I do not want to change it due to specific requirements)- however, I'd like to set up a validation rule to determine whether or not it is a valid date: mm/dd/yyyy. What should I input in the validation rule? Thanks I do not think it is possible at table level but in your data entry form us AfterUpdate event and IsDate function. -- Build a little, test a little. "shm135" wrote: > Hi, > > I have a RESPONSE field on my table. This field is set up as a text > fiel...

Outlook change default start/due dates
When I make a new task on the To Do List, how can I have Outlook list the start and due date automatically as None rather than today's date? I'd rather have them all start as None, then I can assign dates as needed. Kerrin wrote: > When I make a new task on the To Do List, how can I have Outlook list > the start and due date automatically as None rather than today's > date? I'd rather have them all start as None, then I can assign > dates as needed. You can't. However, if you create them through the New Task button, or from control-shift-k, it'll be ...

Publisher 2002: How to remove empty pages left by deletion of text
My wife deleted about six pages of text from a long document (300+ pages). However, the deletion left 6 empty pages (that is, the gap didn't close up). She has tried various kinds of dlete and cut editing commands, but nothing so far has worked. EmoryW wrote: > My wife deleted about six pages of text from a long > document (300+ pages). However, the deletion left 6 empty > pages (that is, the gap didn't close up). She has tried > various kinds of dlete and cut editing commands, but > nothing so far has worked. ============================= I guess you tried...Edit / ...

Excel 2003, problem with UK date format using OLE
I have a C++ package that sends data to a spreadsheet using OLE. The package works okay in Excel 97 and Excel 2000 systems, but has a problem with Excel 2003. In Excel 2003 the "Date" column is not recognised as a date when the "Locale" is "UK" (left as "General"), but date is recognised okay when the "Locale" is "US". If I just manually enter the date in "UK" it is recognised okay. I have tried formatting the column according to the "Locale" ("MM/DD/YY" or "DD/MM/YY" or "YY/MM/DD"...

file export of report ... "Yes/No" data type fields
I did File / Export in Microsoft Access for a report to Excel. The "Yes/No" data type fields are not exported. Is there a way I can force them to be exported to Excel? I expect them to be exported with True/False values. Is there way to export them as "check marks" like they are displayed in the report? Thanks. You cannot expect checkboxes (which are a GUI representation of a data value) to be exported to Excel. Further, why would a person ever export anything to Excel from Access, well, I don't undertsand that. "snlcoy" wrote: > I did File / E...

When concatenating concatenates don't concatenate...
Hi List, Can anyone help? When concatenating already-concatenated cells, th result displays perfectly well in the Excel spreadsheet, but truncate when the cell is pasted into a .txt file. It doesn't seem to be due t Data Validation limits (having said that, selecting the entir worksheet and doing Alt > Data > Validation > Validation criteria Allow = Any value" did seem to solve the problem once, but only to com back next time round). The truncation occurs sometimes after 8 or 1 chars, and sometimes after 20 or so, always at the same spot. If I cop the cell into a fresh Excel...

print report according to value field
I am trying to print a report depending on a the field "OrderQty". i did created the code with the print command and the criteria and it looks like this: DoCmd.OpenReport "rptProduction", acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip DoCmd.PrintOut , , , , [OrderQty] however, it give me an error saying, that "|" field is not found. i looked through the code and tested the code, by putting 1 or 2 in place of OrderQty and it prints fine, that means to me that it's not the code and i looked to through the Query that the source of that R...

SUMIF only it has 3 ceretain texts in the same row #6
Thanks for the help and the formula works. Hopefully you got my othe email for other concens. Thanks! ; -- vane032 ----------------------------------------------------------------------- vane0326's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1473 View this thread: http://www.excelforum.com/showthread.php?threadid=26565 You're welcome ! -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "vane0326" <vane0326.1dkmmn@excelforum-nospam.com> wrote in message news:vane0326.1dkmmn@excelforum-nospam.com...

File Creation Date
Excel 2007 I have a need to insert the file creation date into a workbook. I could swear that I've done this before, in earlier versions of Excel. However, I'm not real familiar with Excel 2007 yet and I'm having a hard time figure out how to do this. I went to the Formulas ribbon and then to Insert Function and looked through the date and time functions, but did not see what I need. Is there a way to insert the file creation date into an Excel workbook? --Tom You'll need to create a user defined function. Alt+F11 to get to VBA, then Insert > Modul...

Download transaction date overwriting my date.
I found Money 2003 downloaded transaction dates were overwriting my dates for the matching transactions. So I went to Tools|Options and the Online Services tab and dechecked the box "Overwrite transaction dates with dates from downloaded transactions". No change. My dates for my entered transactions are replaced with the date found in the matching downloaded bank transaction as soon as I hit the "Accept" button. What is the problem? Thank you, - Phil What happens when you download another statement - do the dates work then? It may be that existing downloaded ...

Update a date/time control when check box control is updated
I have two columns in tbl.Jobs. One field is a bit data type that I named Jobs.OnHold and the control on the form is a check box. I also have a date/time field named HoldDate. I want the hold date field to automatically udpate with the current date everytime the check box control is udpated. What is the best way to accomplish this? Use the After Update event of the check box control: Me.txtHoldDate = Date -- Dave Hargis, Microsoft Access MVP "BrianP" wrote: > I have two columns in tbl.Jobs. One field is a bit data type that I named > Jobs.OnHold and the control ...

If statement with date
I'm trying to return new salary information based on a hire date. The criteria is if you were hired on or before Jan. 1, 2005, you get 3% of the mid-point of your current salary range. I have the spreadsheet set up as follows: A1 B1 C1 D1 E1 Current sal hire date PG mid-point new salary 40,950.00 12/15/03 15 52,460.00 ???? I was using the formula in E1: =If(B1>1/1/05,(D1*.03)+A1,A1) But when I entered a hire date that was after 1/1/05, it was still adding ...

Concatenate function
Hi, How can I concatenate these 2 cells: one is time and the other is text: 9:00 and AM and I want the result to be 9:00 AM =CONCATENATE(AD2, " ", AE2) I have tried different formating cells but it doesn't work, this is what I get: 0.375 AM Thanks for your help. NSNR - You must format the time (which is a number) to text: =TEXT(AD2,"H:MM") & " " & AE2 -- Daryl S "NSNR" wrote: > Hi, > How can I concatenate these 2 cells: one is time and the other is text: 9:00 > and AM and I want the result to be ...

Convert text to number in IIF statement
Hi, I have a query where I am using an iif statement so that my field we either equal text or number depending on the value of another field. Here is my iif statement Portfolio1: IIf([qry_mhfsac4dPassThrough.SecId]="60934n583","FPA",IIf([qry_mhfsac4dPassThrough.SecId]="825252646","Aim",CInt([portfolio]))) I am basically saying that if the SecID of a security is x then"FPA" or "AIM" otherwise I want to see the portfolio number. However, whether I use the formula cint() or val() or a couple others that I have tried, this field still c...

Counting dates
I am trying to count the number of dates in a column that appear b month and year. For instance, I some that are 11/23/2003 and 11/24/200 and 11/21/2002. How can I makew a formula that will count the instance of 11/2003 -- Message posted from http://www.ExcelForum.com =SUMPRODUCT((YEAR(A1:A100)=2004)*(MONTH(A1:A100)=11)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "jdavis3874 >" <<jdavis3874.14770b@excelforum-nospam.com> wrote in message news:jdavis3874.14770b@excelforum-nos...

Sharepoint Export to Excel missing "Person/Group" Fields
Hello, I am wondering if anyone else has had the problem that I am having. I am using Sharepoint Services 3 as well as Excel 2003 and when I create a List in Sharepoint with "Person" fields that show network user smart tags. I attempt to export the list into Excel and when I do the "Person", Created By, Modified By fields do not appear. I have asked colleagues of mine to attempt the same using Excel 2007 and they are not having any missing columns as I am. Can anyone help me out to find a workaround to my problem? Excel 2007 is not an option for my company at th...

Some unspecified "install on first use"
Outlook2003 - SP1 WindowsXP SP2 If I have a mail in Outlook with an attachment and I want to save the attachment to the desktop some kind of install-on-first-use runs. Does anybody know what feature is installed here? When I have found out I want to change that setting to "run from my computer". Regards Anette Also it seems to be an issue first time I want to save to desktop from Word2003, Excel2003 .... Any ideas? Regards Anette "Anette Linnea" <alr (a) tdc.dk> skrev i en meddelelse news:uraSbodtEHA.2532@TK2MSFTNGP10.phx.gbl... > Outlook2003 - SP1 > Win...

Undefined Function DATE
Access 2003 On 3 PCs the query works, on two PCs it gives the error message Undefined function DATE when I use Date() as a query Criteria Any ideas? Thank you. On Fri, 16 Nov 2007 17:12:15 -0500, Brendan on Comcast.net wrote: > Access 2003 > On 3 PCs the query works, on two PCs it gives the error message > Undefined function DATE > when I use Date() as a query Criteria > Any ideas? > Thank you. Those PC's have a missing reference. Open any module in Design view (or click Ctrl + G). On the Tools menu, click References. Click to clear the check box for the typ...

How to convert date to text
I have a field - date. Let's say that one of the values is 6/20/05. How can I convert it to a number field, for example 6/20/05 will be shown as 20050605? Thanks, galsaba =TEXT(A1,"yyyymmdd") -- HTH RP (remove nothere from the email address if mailing direct) "galsaba" <galsaba@aol.com> wrote in message news:1116789405.609939.308320@g47g2000cwa.googlegroups.com... > I have a field - date. Let's say that one of the values is 6/20/05. > How can I convert it to a number field, for example 6/20/05 > will be shown as 20050605? > > Thanks,...

Concatenating Cells
I have spent hours this afternoon in Excel 2003 trying to concatenate two adjacent text columns into a third column defined as Text format. It doesn't work, the result cell just displays the formula you enter {e.g. =A1&B1 or =CONCATENATE(A1,B1)}. I discovered after a great deal of frustration that this will only work if the cell containing the formula is formatted as '*General'*. All the MS command help refers to the data being concatenated as 'text' data as does the command help that displays as you type. I found no help on this on the MS site and trawling the w...

weekending date of a given date
Hi All, If I have a past date of say fri 3/7/2008 and the weekending date for me is that sun the 9th, is there a way to get that date of the 9th? thanks, rodchar You can base it on offset from a well known Sunday date: DECLARE @dt DATETIME; SET @dt = '20080307'; -- 7 Mar 2008 -- Jan 7 1900 is Sunday SELECT DATEADD(DAY, (DATEDIFF(DAY, '19000108', @dt) / 7) * 7, '19000114') AS sunday_date; /* sunday_date ----------------------- 2008-03-09 00:00:00.000 */ -- Plamen Ratchev http://www.SQLStudio.com thanks for the help, rod. "...

Filltering data between two date ranges
Hi, wonder if anyone can help me - I have a list containing peoples birthdays. I wish to be able to enter a date range i.e. today and 2 weeks into the future. I then wish for the list to be filtered showing only those birthdays within the date range. The date range could span across 2 different months. I'm not particularly skilled in using features such as VBA etc, so if there is a simple solution it would be much appreciated. Many thanks for any help. Hi Dave! If you enter the birthdays as Month/Day this is somewhat easy. If you enter the birthdays as Month/Day/Year it is much m...

outlook xp: can not use exchange server with another user.
tried to configure a laptop for "exchange server" mail to dial to work and open Outlook to view mail the same way as in the office. But, on the configuration stage it; check name part, it ask for a user name and password. the user name and password is not accepted by the Server. "check info or contact the administrator" appears. have tried erasing profile and doing it over several times but comes out the same. appreciate your assistance. ...

Payroll Manual Checks taking user date and not check Date
Hi everybody, I am facing a problem when posting the Payroll Manual checks, it's taking the user date as the check date and not the check date itself. Anyone who can help on this issue. Thanks, -- Khaled ASK Mr Khaled, I tested the scenario which is stated by you in Fabrikam company. I entered a manual check with posting date (01 April 07) on Batch Entry Windows, which is the check posting date in General Ledger. My Userdate is the (12 April 2007) and My Check Date is (10 April 07) i posted the check and its all fine. The Checkdate is exactly the Date i give for Check, My Post...