Display text as date

Hey guys :)
I use this formula =">="&DATEVALUE(Files!D3)+TIMEVALUE(Files!D3) to
display and use as filteroption.
(Dont ask why I have to do this exercise....)
However the formula works all right but the display appears as
decimalnumber in this text expression:

Actual displayed: >=40170,6145833333
Wanted displayed:  >=23.12.2009  14:45:00

I KNOW that there is some way to transform the expression displayed,
but have not found anything useful on the web - so far.
Are there anybody called to give me a hand on this one?

Best regards Snoopy
(Wish you a Happy New Year as well)
0
Snoopy
12/30/2009 1:41:35 PM
excel 39879 articles. 2 followers. Follow

4 Replies
704 Views

Similar Articles

[PageSpeed] 48

I'm not sure if this will work with your filter, but you can change the display:

=">="&text(DATEVALUE(Files!D3)+TIMEVALUE(Files!D3),"dd.mm.yyyy  hh:mm:ss")



Snoopy wrote:
> 
> Hey guys :)
> I use this formula =">="&DATEVALUE(Files!D3)+TIMEVALUE(Files!D3) to
> display and use as filteroption.
> (Dont ask why I have to do this exercise....)
> However the formula works all right but the display appears as
> decimalnumber in this text expression:
> 
> Actual displayed: >=40170,6145833333
> Wanted displayed:  >=23.12.2009  14:45:00
> 
> I KNOW that there is some way to transform the expression displayed,
> but have not found anything useful on the web - so far.
> Are there anybody called to give me a hand on this one?
> 
> Best regards Snoopy
> (Wish you a Happy New Year as well)

-- 

Dave Peterson
0
Dave
12/30/2009 2:07:32 PM
On 30 Des, 15:07, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> I'm not sure if this will work with your filter, but you can change the d=
isplay:
>
> =3D">=3D"&text(DATEVALUE(Files!D3)+TIMEVALUE(Files!D3),"dd.mm.yyyy =A0hh:=
mm:ss")
>
>
>
>
>
> Snoopy wrote:
>
> > Hey guys :)
> > I use this formula =3D">=3D"&DATEVALUE(Files!D3)+TIMEVALUE(Files!D3) to
> > display and use as filteroption.
> > (Dont ask why I have to do this exercise....)
> > However the formula works all right but the display appears as
> > decimalnumber in this text expression:
>
> > Actual displayed: >=3D40170,6145833333
> > Wanted displayed: =A0>=3D23.12.2009 =A014:45:00
>
> > I KNOW that there is some way to transform the expression displayed,
> > but have not found anything useful on the web - so far.
> > Are there anybody called to give me a hand on this one?
>
> > Best regards Snoopy
> > (Wish you a Happy New Year as well)
>
> --
>
> Dave Peterson=96 Skjul sitert tekst =96
>
> =96 Vis sitert tekst =96

Thanks Dave
I'll dive into this - there is some translation trouble; english vs
norwegian, to by-pass

Best Regards
Snoopy
0
Snoopy
12/30/2009 2:16:47 PM
> I'll dive into this - there is some translation trouble; english vs
> norwegian, to by-pass

TEKST, DATOVERDI, TIDSVERDI.
Hent KeepITcools formeloversetter her, den er uerstattelig:
http://members.chello.nl/jvolk/keepitcool/index.html

HTH. Beste hilsen Harald
0
Harald
12/30/2009 11:02:12 PM
Tack s� mycket.

(translate.google.com <vbg>)

Harald Staff wrote:
> 
> > I'll dive into this - there is some translation trouble; english vs
> > norwegian, to by-pass
> 
> TEKST, DATOVERDI, TIDSVERDI.
> Hent KeepITcools formeloversetter her, den er uerstattelig:
> http://members.chello.nl/jvolk/keepitcool/index.html
> 
> HTH. Beste hilsen Harald

-- 

Dave Peterson
0
Dave
12/31/2009 1:49:01 AM
Reply:

Similar Artilces:

no date/time on individual emails in inbox
Hi, Currently my inbox emails are showin up correctly in the "list" view with recipient, title and date and time. However when I click on each individual email they read "sent:none" instead of the date/time. This is just in my inbox, but is a problem when printingor fowarding a copy as they have no date/time on. Have tried uninstalling and re-installing outlook but with no success Any help would be greatly appreciated. Did outlook download the messages or did you import them? What type of email account? -- Diane Poremsky [MVP - Outlook] Outlook Tips:...

How can I change the chart so date is up and down and data is acro
I have a chart that has the date across the bottom (category axis) and the data up the side (value axis). I want to change these. Is this possible? Hi, It depends on what chart type you are using. Have a look at Jon Peltier's example for a line chart. http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html Cheers Andy speet wrote: > I have a chart that has the date across the bottom (category axis) and the > data up the side (value axis). I want to change these. Is this possible? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info =?Utf-8?B?c3BlZXQ=?= Wrote: &...

Transaction Dates
Bill Gates just can't make it easier can he? Ever since my bank upgraded their software I am having the worst time with e-pay's & x-fers thru money. Can someone help me understand why? When I submit a payment via Money the program tells me it has to change the date because it can't be processed on that day. For example; Today I requested that 3k be transfered from my savings to my checking. In the past the transfer would go thru and there was no issue. Now I get a message that says my bank doesn't have enough time to do the transfer and the date must be modified ...

a valid exchange rate not found for the return date
Anyone ever seen this message? There is no relative help in knowledgebase and what there is - we have covered. Not sure why GP is saying we don't have a valid exchange rate only when doing a return. The return is within the month and the exchange rate table is active for a period long before and long after this date. We've been trying to solve for days now and the error makes NO sense at all. Please help ...

date shows none in out of box
Cannot send e-mails, I can compose an e-mail and it goes into the outbox but will not send. The date shows none in the outbox. Every e-mail I compose goes into the outbox but the date shows none. Please advise Michael wrote: > Cannot send e-mails, I can compose an e-mail and it goes into the > outbox but will not send. The date shows none in the outbox. Every > e-mail I compose goes into the outbox but the date shows none. > Please advise Version of Outlook? Type of Mail Server? If pop/imap, does it go if you do a Send/Recieve? Did it ever work? Any known changes between i...

Text Field
I have created a database using linked tables. There are no notes fields in those tables and I need one. I created a text field and entered a comment but that comment appears against every record but I want it to stay within the record it was recorded against. Anyone help ? Hi KC Where did you create the text field? Since the table is linked, you need to create the text field in your data source, where ever the tables actually reside. If you created the text field (box) on a form, it will not be bound to a particular record and thus it will show up for all of the records, similar to ...

Add Date
We have a spreadsheet that we would like to automatically add the date in the first cell of each row when information is entered into another cell in that row. The date would need to remain static once added. Any suggestions? This worksheet_Change event will update the date in column A each time there's a change anywhere else in the row. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range Dim myRngToCheck As Range Dim myIntersect As Range With Me Set myRngToCheck = .Range("B1", .Cells(.Cells.Count...

insert text data using function
Hi Guys, I need help on the following matter. Suppose i have two column A and B. Now when i insert some text in the A1 cell i want the data automatically set up in the B1 Cell. Fox example if i type Linux in the A1 cell "Open Source" will be automatically inserted in the B1 cell but if i insert windows "close source" will be inserted autoamtically. I tried with if function but no success. How can i achieve that? Mosaddeq Aziz You need to set up all of your possible data entry values for column A on Sheet2. Then in column B on sheet2, put the values you want aut...

Table of Contents not displaying
I have been succssfully using the Table of Contents feature forever. Suddenly, it displays only codes on the screen, yet will properly print and will display properly in "Full Screen Reading" mode. Creating a PDF also displays the ToC correctly. This occurs only on my computer and will display properly on other computers. How do I fix this? Press Alt+F9 to toggle the display from field codes to field results. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "tomMedClinic" <tomMedClinic@discussion...

Text Box in Publisher
I am trying to create a text box in publisher with the measurement of 5.75 (h) x 5 (w). But publisher is making the height 5.751. How do I correct this? Is this even possible. Don't think it is possible... The .001 is so tiny it shouldn't make much difference. I can get a true 5.75 in Publisher 2000 but as soon as I copy/paste it goes back to 5.751 in 2002, 2003 and 07. Lines do the same. I can create a rectangle in a draw program, paste special into Publisher, ungroup, this rectangle will maintain the correct size. -- Mary Sauer http://msauer.mvps.org/ "Nikki" &l...

CListCtrl large icons and text problem
Hi, I have a simple thumbnail view control based on CListCtrl and description of each item is a 3-line text. Unfortunately CListCtrl shows only two first lines, and the third one is visible only for selected item. How can I show 3 lines of text for all items ? How can I increase label width ? Thanks in advance, Kamil Karmil, Here is a control that might do what you'd like. Tom http://www.codeguru.com/listview/ReportCtrl.shtml "kamil" <kamildobk@xxxpoczta.onet.pl> wrote in message news:OndTGoS4DHA.3360@tk2msftngp13.phx.gbl... > Hi, > > I have a simple thum...

PDFs do not display in OWA using FE/BE scenario, IE 6, Adobe Reader 7.0.7
Greetings, My organization is currently running Microsoft Exchange Server 2003 SP2 in a FE/BE scenario with the FE serving OWA. We are experiencing problems with our OWA users not being able to open PDF attachments within OWA when they are using IE 6 (fully patched) and Adobe Reader 7.0.7. After some testing, I have discovered the following: When using: Abobe Reader 7.0.7 and IE 6 (fully patched) - PDFs will not display in OWA (no errors given) Adobe Reader 7.0.7 and IE 7 Beta 2 - PDFs will display properly Adobe Reader 7.0.7 and Firefox (fully patched) - PDFs will display properly ...

Parse(?) data from text file
I have a large text file that has hundresds (or thousands) of compiled questionairres that I want to convert to a more usable format (excel?). Here is what it looks like: ======================== Name: Joe Jones Address: 123 main comment: varies from blank to 20+ lines may have muliple paragraphs others: 8 or 10 other fields =============================== next questionnaire ====================== A couple of details: the separator line of ====s vary in length, the individual questionnaires are not always identical. Some have a couple of extra 'fields'. I envision a separat...

I want the day of the week when I insert a date
Excel 2003 WindowsXP I am creating a spreadsheet and inserting many dates. I want to insert the date and have the day of the week of that particular date appear. Otherwise I would have to look up years worth of dates. Is there a way to do this? Thank you. Right-click on the cell with the date you wish converted to "Weekday" > Format Cells > NumberTab > Custom > and type dddd in the little window....> ok Vaya con Dios, Chuck, CABGx3 "HT" wrote: > Excel 2003 > WindowsXP > > I am creating a spreadsheet and inserting many dates. I want to ...

how do I convert an imported number to text
I am downloading data from a computer and wish to convert the number to text in an excel format eg: 0 = pump #1there is a series of numbers from 0 to nine and each one represents a different problem or failure of a piece of machinery Have you tried Find and Replace (Ctrl+H) -- Jacob "Lyn" wrote: > I am downloading data from a computer and wish to convert the number to text > in an excel format eg: 0 = pump #1there is a series of numbers from 0 to nine > and each one represents a different problem or failure of a piece of machinery ...

XML page cannot be displayed
Great Plains 9 My users are getting the error: XML page cannot be displayed. "Access denied. Error processing resource 'file///c:'program files/microsoft/dynamics/GP/background/homepage.xls" They have read, write and modify rights to this file. Any ideas? Stacey Hammons ...

Publisher Text Box #2
I am looking for guidance on missing text from text boxes. I understand how overflow works, but this is a different problem. I am preparing a trifold pamphlet where it's important that my text fits into each third without overlapping the folds. I have one long text box filling each third. The only text box that displays all contents when printed is the center third. The first few letters of each line in the left third are curtailed at the left margin. The last few letters of each line in the right third are curtailed at the right margin. I have played around with the margins on bot...

IF Function with Dates
Hello, I hope someone can help me. I would like to create an IF function for dates in my worksheet. I have a Start Date column and End Date column. If the date in the Start Date and End Date falls within the Fiscal Year(06), then I want to display a 1, otherwise a 0(zero). A1=7/01/06 (Start Date); B1=6/30/10 (End Date) FY06 is from 7/1/2005 to 6/30/2006 What I have in C1 is =IF(AND(A1>=7/1/2005,B1<=6/30/2006),1,0) My result for FY06 is 0, but that's not correct because the End Date is not until 12/31/2010, so it falls within the FY06. I want this formula to...

Displaying timestamps corresponding to min / max values
I have a table with temperature readings of different locations over a period of time. Thus multiple records per location. I have a query to show me the min and max temperatures for each location. I needed to add to that query the date when the min and the max temperatures were recorded. How could I do that? Many thanks in anticipation. This query returns two records (one for the minimum at the location and one for the maximum) SELECT YourTable.Location, Temperature IIF(YourTable.Temperature = YourCurrentQuery.MinTemperature, "Minimum","Maximum") as Which FROM You...

How to combine text and the value in another cell?
Excel 2007 Lets say in A2 I have a calculated value of 2 in A1 I want it to say The total of XX = 2 This is what I tried: =CONCATENATE(The Total of XX =,A2) Gave me a "value" error what other approach should I try? tia dave Try this: ="The total of xx = "&A2 -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== "Dave" <dave@accessdatapros> wrote in message news:20C0AC3C-2CF3-49BC-8837-428F60A42692@microsof...

Is there a way to automatically resize a text box to the font size
I know how to set the font to automatically resize to the text box, but is there a way to automatically resize the text box to the font size? Very often I have the font size exactly how I want it and I have to adjust the text box so it's as tight to the text as possible. If there isn't an automatic way to do this, this feature should be included in the next version of Publisher. ...

Autorun macro by date
This is a repost as my one this morning doesn't appear to have saved, so ap;ologies if I end up raising the same question twice. I have a macro called 'Update_Me'which I would like to run automatically on the first of each month (when excel is opened, obviously). How do I go about this? Thanks in advance Steve Try something like: Sub Workbook_Open() Dim dToday As Date dToday = Now If Day(dToday) = 1 Then 'rest of code here End If End Sub --- Place this in ThisWorkbook module. HTH Jason Atlanta, GA "Steve" wrote: > This is a repost as my one this morni...

How do I enter dates with one keystroke
I am new to excel. This may be the stupidest question of all time, but I would like to know how to assign, program, or whatever you call it in excel a date to a key so that when I am in a cell and want to enter that date all I have to do is use one keystroke. For example, can I somehow assign a date like 07/26/08 to the F10 key so that every time I press F10 the date 07/26/08 is entered into the cell where the cursor is? Thanks in advance for any help you can provide me. -- barefoot This may not be exactly what you're looking for, but to enter the current date in a cell click ...

displaying mail from different e-mail address
Does anyone have any experience in displaying mail from various e-mail accounts on the main page of outlook? I can't figure out which mail is comming from my various POP 3 Accounts... Please advise. -THANKS! Use Field Chooser to add the Email Account field to the view. -- Russ Valentine [MVP-Outlook] "Pable" <anonymous@discussions.microsoft.com> wrote in message news:115ed01c441ae$7fe203d0$a301280a@phx.gbl... > Does anyone have any experience in displaying mail from > various e-mail accounts on the main page of outlook? I > can't figure out which mail ...

Why does this text automatically update?
I'm editing a newsletter used by former staff members created in Publisher 2003. (I'm using the same program.) There is a text box on each page in the same spot that says "Volume # Issue #." When I edit it on the first page it automatically changes on all the other pages. I don't think it is an object, embedded or linked or otherwise, because the "Edit Objects" option is greyed out. What is it, and how can I edit/replicate it? It's synchronized text. From Publisher's Help File: When you create a publication from a Publisher wizard or by using Publi...