Re: Formula in MS Query

Unfortunately the format command wiill not allow grouping of the dates,
since time is appended with date in every field.

You could import the Time field in its Date/Time format, then reformat
the column in Excel
   (Format>Cells, Number tab -- Date category)

Krish wrote:
> I am trying to import external data into Excel spreadsheet. One of the
> tables in the external data has a field "Time" for Date and time of
> transaction. In MS query I inserted a new column with a new name and
> inserted a formula Left(Filename.Time,11). The result came as "Dec
2,2003",
> whereas I wanted this to show as "12/02/2003", in the true date format..
> What function I should use to get the desired result from MS Query.

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


0
Krish (9)
12/3/2003 2:22:22 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
472 Views

Similar Articles

[PageSpeed] 48

In MS Query, use the expression:  INT(Filename.Time)
This will return all dates with time set to zero.

Krish wrote:
> Unfortunately the format command wiill not allow grouping of the dates,
> since time is appended with date in every field.
> 
> You could import the Time field in its Date/Time format, then reformat
> the column in Excel
>    (Format>Cells, Number tab -- Date category)
> 
> Krish wrote:
> 
>>I am trying to import external data into Excel spreadsheet. One of the
>>tables in the external data has a field "Time" for Date and time of
>>transaction. In MS query I inserted a new column with a new name and
>>inserted a formula Left(Filename.Time,11). The result came as "Dec
> 
> 2,2003",
> 
>>whereas I wanted this to show as "12/02/2003", in the true date format..
>>What function I should use to get the desired result from MS Query.
> 
> 
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
12/3/2003 2:38:21 AM
Reply:

Similar Artilces:

Build Dynamic Query from Form
I am trying to build a dynamic Query from a Form. I keep getting an error that reads ‘Object qryFilter already exists’ I suspect it has something to do with the string of dates being passed to the Query; strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom] And [Forms]![SearchForm]![cboTo])" I am trying to add a means for a user to Query by Customer and Trader AND all records between two dates. This was working fine for Customer and Trader; when I added in the code to filter by dates I started having problems. I know the SQL will be li...

Combining cells to create a formula
Hi, I have two cells that I want to combine to have a working formula B1 = sum B2 = d3 b3 = =b1&"("&b2&")" D3 = 10 The result in b3 is : sum(d3) How do I get this to result in the actual value in D3. I know I can simply write =D3, but the actual reason for combining the two cells is more complicated than explained here. Thanks Hi I think you are perhaps looking for the Indirect function =INDIRECT("D"&ROW(3:3)) would return the value contained in D3 As you copy down, the formula would alter to 4, 5 etc. represent D4, D5 etc. -- Regards R...

Outlook 2000 doesn't respond after downloading MS update!!
Luckily, my Outlook Express seems to work, but my Outlook 2000 will open, but if I click on a message or do Anything, it FREEZES. HELP!! I rely on calendar functions, other things. HOW could MS allow an update that screws up their own programs? I did restore - to no avail. I lent CD to someone, so can't do "repair." (Trying to get a hold of friend) Please help! I'm in process of job hunt & really need Outlook to function... Thanks, Sherry Did you recently apply SP3 for Office/Outlook 2000 and is Outlook 2000 configured in Internet Mail Only mode? If yes, you...

in excel how can we put formula to convert numericalfigureto word
for example : in excel i have mention 25000.00 in numerical amount , i want to know how can i convert in next colum , about word ?/; How can i put formula to make the numerical in to words like 25000 in numerical to twenty five thousands in word. There is no direct functions to convert this. For a VBA solution check out the below links http://support.microsoft.com/kb/213360 http://www.ozgrid.com/VBA/ValueToWords.htm http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob (MVP - Excel) "excel" wrote: > for example : > > in excel i hav...

Formula Question #18
I have built a workbook in which I have inserted a formula to tell me whether the contents of a supply bin needs replenishment or not. The formula I used is: =IF(E3>F3,"REPLENISH!","No Action"). Each morning, I run a report to see what parts have been used, which becomes a new sheet in the workbook. Now, I want to add a formula that, whenever it sees "REPLENISH!," it will back through the workbook to count whether that same part needed replenishment on consecutive previous days. If it has, then the latest worksheet will report the number of days that ...

Move/Copy A Row Based on Formulas to a New Worksheet
I want to move several rows of sub-totals (averages within sub-groups) to a summary worksheet, but I get the Ref error. How can I copy sub-group averages to another worksheet? Thank you. high light and copy. select where you want it. edit>paste special>values. this will turn you formulas into hard numbers. you are getting the #Ref error because on the other sheet where you pasted the formulas, the formula no longer had the same references that they had on the other sheet. for example: =sum(a1:a10) in cell a11 you copy and paste on another sheet at cell a1. excell tries to compensat...

Erase data, preserve formula's
I have a an excel file with 12 worksheets for the financial year and an additional worksheet for yearly totals. I need to get a blank copy of this and was wondering if anyone knew a way to delete all the user inputted data while keeping the formatting and formula's intact. Any help is much appreciated. -- urbanfox ------------------------------------------------------------------------ urbanfox's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22826 View this thread: http://www.excelforum.com/showthread.php?threadid=519004 Hi Hit F5 and select Special a...

How do I set up a formula in excel that is the tenth root of 7 ve.
How do I set up a formula in excel that is the tenth root of 7 versus the square root of 7? try the formula =7^(1/10). HTH Alan P. "kestig1" <kestig1@discussions.microsoft.com> wrote in message news:99C16AB7-8BD6-4671-B138-5F2961E46067@microsoft.com... > How do I set up a formula in excel that is the tenth root of 7 versus the > square root of 7? ...

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

Please help..with a formula. I don't know code.
I have a long list of numbers - values in a file X, and I want to fin and replace those values in a even larger list in a file Z an highlight those values in Z -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to achieve. What do you want to replace, etc. You may give an example (plain text - no attachment please) >-----Original Message----- >I have a long list of numbers - values in a file X, and I want to find >and replace those values in a even larger list in a file Z and >highlight those values in Z. > > >--- >Message...

MS money 2004 Deluxe canadian edition
Is there a money 2004 deluxe canadian version? I have found the Standard edition everywhere, but not the Deluxe. I am using the trial version and it will expire soon, but when I try to buy the Canadian version, MS Money keeps taking me to US only version purchase options. The MS Money web site couldn't have been less helpful. Derrick In microsoft.public.money, Derrick wrote: >Is there a money 2004 deluxe canadian version? I have >found the Standard edition everywhere, but not the Deluxe. No Deluxe. > >I am using the trial version and it will expire soon, but >...

What is the MS Best Practice for positional files to XSD schema?
How do I get a positional flatfile from cobol (shown below) and create a Schema based on the positions below in vb.net? 'ERROR-TYPE-CDE Positional length =(2) 'ERROR-NUM Positional length =(10) 'COGGA-FLD-REQUEST-KY Positional length =(10, 0) 'COGGA-CUST-ACCT-KY Positional length =(10, 0) 'COGGA-ACCT-STAT-DESC Positional length = (8) 'COGGA-DISPATCH-DTE Positional length =(10) 'COGGA-DISPATCH-TME Positional leng...

formula: counting presence
Hello! I have to count presence of employees from sheets between START and END, which is stored in G9 cell. I think it should be something like: =SUM(IF(START:END!G9="present"; 1; 0)), but this one returns #REF and I don't don't why. Try these from a post of mine today. One way. Put the sumif on each sheet with an indirect reference to d12 of the master. then use =sum(sheet1:sheet21!a2) where a2 in your sumif formula. One way to put=SUMIF(B:B,Sheet1!D12) on each sheet is to select all>type the formula in the cell desired>after the error msg>delete from the ...

please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Help on Macro or Formula
Hi, i hope someone can help me. i need to create a formula that sits in a cell and looks for data. ( obvioiusly ). however, the formula needs to be in place even though the file from ehere the data comes from might not be there yet. ( i have to create a book that when a new file is created, the links are already in place ). i think it could work with an IF type formula for ( if B2="",""). here is my information. Cell description: A2 = Job no. B2 = Client Name D2 = Actual Spend on project Register!D2 = Job Description Register!H2 = Quoted Amount my path is S:\Clients\...

unable to access mail after re-enable account
Hi, After enable an disable account and change its password, it can login to its mail account. What's happening. Is it normal? Thanks a lot What is the error message when user tries to access mailbox? Does the user still show in ADUC has having Exchange attributes? Have you looked at the MB using ESM to see if it is disconnected? "Raymond" <anonymous@discussions.microsoft.com> wrote in message news:136c01c46f11$d16f8390$a301280a@phx.gbl... > Hi, > > After enable an disable account and change its password, > it can login to its mail account. What's hap...

Re: Update for Windows Vista (KB980248) -- No more fonts available!
cf. http://social.answers.microsoft.com/Forums/en-US/vistawu/thread/f7fa37ca-683c-4be1-b4ad-ab2dd1249399 PA Bear [MS MVP] wrote: > Are you running Vista SP1 or Vista SP2? Is Office 2010 installed? > > Was KB980248 offered & eventually installed via Windows Update or did you > (attempt to) install it manually? > > What anti-virus application or security suite is installed and is your > subscription current? What anti-spyware applications (other than > Defender)? > What third-party firewall (if any)? > > Has a(another) Norton or McAfee ap...

Formula for date field
1.I have simple dates in one column (say column A) . 2.In the next column(Column B) I would like the date five months after Column A to be displayed.Eg if Column A has an entry of 9th June 2007,Column B should display 8th November,2007. 3.A simple formula does not do the job as this does not take into account the different number of days in different months! regards S.Sanatani Your post is a bit ambiguous since you don't really say how the different number of days in months should be handled. One way: A1: <date> B1: =DATE(YEAR(A1),MONTH(A1)+5,DAY(A1)-1) In articl...

Copy/Paste formulas
How do I keep certain cells (those I want to point to a specific 'constant') from incrementing while the remaing cells in my formulas increment as expected. Example: ((E65)*(COUNTIF(I7:I7,"V"))) where the cell "E65" contains a set value that I want to be placed in the result as I step down the incremental (I) rows when the character "V" is found in the particular (I) row. When I do my copy and paste, the (E) row increments as the (I) rows increment. $E$65 "BobG" wrote: > How do I keep certain cells (those I want to point to a specific...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! $D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE
My cell c1 contains the formula b1-a1.when i copy this formula to cells d1 and e1 the cells d1 and e1 have the following formula : d1=c1-b1 e1=d1-c1 but i want the following d1 should be b2-a2 and e2 should be b3-a3 how do i do this? One way... In C1 enter & copy across: =INDEX($A:$A,COLUMN()-COLUMN($C$1)+1)-INDEX($B:$B,COLUMN()-COLUMN($C$1)+1) anantth wrote: > My cell c1 contains the formula b1-a1.when i copy this formula to cells d1 > and e1 the cells d1 and e1 have the following formula : > d1=c1-b1 > e1=d1-c1 > > but i want the following > d1 should be b2-...

missing arial unicode ms
I did some publisher projects a few yrs ago,under Win98 & Office SB 2000. When I try to bring them up now (under WinXP, Office SB 2000, it says arial unicode ms is embedded, & can't be loaded or is unavailable. The font Publisher subs screws everything up, but I can't locate the font (on discs for XP, Office, or Win98). How can I get it back? All programs are up-to-date Arial Unicode was installed with this applications Access 2000, Excel 2000, FrontPage 2000, Office 2000 Premium, Office Professional Edition 2003, Outlook 2000, PowerPoint 2000, Publisher 2000, Word 2...

Formula for competition timesheet
Here is the situation. I have a number of members in a clay target club who shoot a competition over a number of ranges. Ranges 1 to 8. They shoot a competition over 4 days. They start shooting at a specific time each day. Start time in cell A1. The duration of the time they spend on each range is specified in B1. These times may vary each day. I have set up a table in the worksheet that shows the squad numbers in column A, the ranges they shoot each day and the time they start to shoot on each range. This table only shows the squad numbers up to the number of members shooting, which is ...

stop automatically changing formula!
i have a countif function COUNTIF(Locking!I16:I40,"f") when i copy this and paste it to the next cell, the formula automatically change to COUNTIF(Locking!J16:J40,"f") How do I stop it from changing column I to J?!?!?! thanks. Caryn, =COUNTIF(Locking!$I$16:$I$40,"f") or =COUNTIF(Locking!$I16:$I40,"f") HTH, Bernie MS Excel MVP "caryn" <caryn.tan@gmail.com> wrote in message news:d7n4u3$hgi$1@avnika.corp.mot.com... > i have a countif function > COUNTIF(Locking!I16:I40,"f") > when i copy this and paste it to the nex...

Update Query
Access 2003 XP SP2 I am having a problem with an update query. Table is in a one-to-one relationship, referentail integrity and cascading data are checked. (The fileds I want to update are not in both tables) Table name= payForward Has 15 fields ie: ID, Name, MemID, Oct , Nov, Dec, etc Oct-Sep fields are yes/no type I want to "select" a field (Oct-Sep) via a query parameter and repalce "yes" with "no". Here is my query: UPDATE payForward SET [Enter month]=No The messages I get is 'operation must use an updateable query' Wh...