date and time formatting

I have a formula in which the date and time are present in cells, bu
when the destination cell returns them, they are in their numerica
value. I need them to appear in the destination cell the same way the
do in their home cells.

here is the formula:

="p-v-w-"&B154&" ph "&E154&" fax "&H154&" ** "&X154&"  * "&Y154&" 
"&AA154&" * "&" * "&AB154&" "&(Y4)&" - "&(Y5)

y4 and y5 are the date and time cell refs

Thanks and Happy Holidays to all!

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements
0
12/1/2003 8:33:16 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
348 Views

Similar Articles

[PageSpeed] 24

You need to put the cell references inside TEXT functions

Good Luck
Mark Graesse
mark_graesser@yahoo.co
    
     ----- Lane wrote: ----
    
    
     I have a formula in which the date and time are present in cells, bu
     when the destination cell returns them, they are in their numerica
     value. I need them to appear in the destination cell the same way the
     do in their home cells
    
     here is the formula
    
     ="p-v-w-"&B154&" ph "&E154&" fax "&H154&" ** "&X154&"  * "&Y154&" 
     "&AA154&" * "&" * "&AB154&" "&(Y4)&" - "&(Y5
    
     y4 and y5 are the date and time cell ref
    
     Thanks and Happy Holidays to all!
    
    
     -----------------------------------------------
     ~~ Message posted from http://www.ExcelTip.com
     ~~View and post usenet messages directly from http://www.ExcelForum.com
    
     ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statement
     
0
anonymous (74722)
12/1/2003 9:01:03 PM
so you could use this instead of just B154:

.....&text(b154,"mm/dd/yyyy hh:mm:ss")&" ph "&....



Lane wrote:
> 
> I have a formula in which the date and time are present in cells, but
> when the destination cell returns them, they are in their numerical
> value. I need them to appear in the destination cell the same way they
> do in their home cells.
> 
> here is the formula:
> 
> ="p-v-w-"&B154&" ph "&E154&" fax "&H154&" ** "&X154&"  * "&Y154&" *
> "&AA154&" * "&" * "&AB154&" "&(Y4)&" - "&(Y5)
> 
> y4 and y5 are the date and time cell refs
> 
> Thanks and Happy Holidays to all!!
> 
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
> 
> ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
12/2/2003 1:42:46 AM
Reply:

Similar Artilces:

Adding input to date
Hopefully the last time I will have to bug everyone! Within the BMP database I am working on, I am trying to find a way to calculate the next inspection date for each BMP, given the previous inspection date [tblBMP.InspDate] and the inspection period [tblBMP.InspPeriod] which is in years (e.g. a given BMP is inspected every 3 years). I've seen examples of simply adding a static value to the date, but how would I go about about using an input number and adding it to the year of the previous inspection date? Thanks again everyone! If all are in years then use the DateAdd function. Da...

Pivot Table
I'm using Excel 2000. I have financial data with expenditures by date. I've added a column to calculate the month and year of the expense. A pivot table has been built over the data to show the expenditures by year (rows) and month (columns). I need to have all months (Jan...Dec) listed to be able to link from another spreadsheet. However not all months has expenditure. Is there a way to "force" the columns to include all months regardless of whether there is data for those months? I could add a row with zero expense for each missing month in the data; however I hav...

Format number
I'm always struggling with formatting numbers, despite all the online examples. I need to format 12470 to 124.70. Thanks in advance, Anna Anna wrote: > I'm always struggling with formatting numbers, despite all the online > examples. > I need to format 12470 to 124.70. You can't, they are different numbers. What you can do, e.g., is convert an integer 12470 to a floating-point 12470.0, then divide it by 100.0, and display it as foo.ToString("#.00"). HTH, -rick- > You can't, they are different numbers. What you can do, e....

A Time Format Problem
I remember reading this on this or another Excel group, and now can not find it. Please help Column A and B contain Start and End time. Column C calculates elapsed time =B2-A2 =B3-A3 etc The sum total elapsed time at bottom of Column C What I can not remember is the custom format to use in column C so that if the end time goes past midnight, it calculates correctly. Thanks for repeating the answer for me. Paul The custom format you need is: [h]:mm:ss this allows hours above 24 to be displayed, rather than wrapping. Hope this helps. ...

Smartlist Negative Formatting
Anyone on the board know how can I format a negative number and dollar to display/print like ($$.000.00). thanks, You cannot change the display of numeric fields in SmartList. SmartList Builder enables you to create new SmartLists and does provide the ability to change the display of numeric fields. -- Charles Allen, MVP "tintin91" wrote: > Anyone on the board know how can I format a negative number and dollar to > display/print like ($$.000.00). > > > thanks, > ...

MS Project Task Start Times
Hello - I am fairly new to MS Project and am having some problems with a Project Plan created for a class I am in. All of the tasks that have the same predecessor automatically have the same start date (even with resources assigned). My instructor is saying that Project will automatically adjust task start times depending on resource availability. My tasks just won't do this. Is this true? She posted an example of her project plan and I noticed that she did not use a start date constraint for these tasks yet they were organized seemingly to resource availability. ...

Month in report header based on dates in data table
I am creating a report based on a table containing transactions for various days of a month. I would like to have the appropriate month print on the report. For example, my data may have transactions for May 2, 6, 27, and 30 and I'd like the report to print "May 2007". Can anyone tell me how to do this? Many thanks in advance, TomD On Fri, 11 May 2007 09:39:02 -0700, TomD wrote: > I am creating a report based on a table containing transactions for various > days of a month. I would like to have the appropriate month print on the > report. For example, my data m...

Date format #20
I was copying some date data from other source to Excel. The orginal format is 3/12/34, which means March 12 of 2034. But Excel automatically convert as March 12 of 1934. Is there any add on or vba program I can get right year in Excel? Thanks This is an operating system setting, not Excel. In Windows, you need to Find your Regional Settings and find the date settings. Adjust range for interpreting a two-digit year as necessary. Example: In Windows XP, you click Customize and then click on the Date Tab from the Regional Settings and Language dialog. tj "Chris" wrote: > I...

Insert query using date as where condition
I have an insert query that runs every day at midnight that records our client's current environmental status (it's for a mental health facility). Here's the code: ----------------------------------------------------------------------------------------------- INSERT INTO dbo.tblCensusHistoryWithDischarge (FullName, LastName, FirstName, FileNumber, ClientNumber, StaffID, Environment, Location, Residence, FundSource, Act10, StartDate, ExpEndDate, EndDate, CensusTimestamp) SELECT TOP (100) PERCENT dbo.tblClients.LastName + N', ' + dbo.tblClient...

Date query 02-05-10
I need to run a query that picks up a particular date no matter how it's formatted. For example if a date is 02/02/2010 or 2/2/2010 or 02/2/2010, ect, I need to be able to pick them all up. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1 Hi - Given your example, I'm assuming your field is text, not date/time. If that's the case, lookup the DateValue() function. HTH - Bob dhoover wrote: >I need to run a query that picks up a particular date no matter how it's >formatted. For example if ...

need info to display only after date is current and stay there
Hi - I need your expert help. This should be easy-but cannot figure it out I have a simple spreadsheet that keeps track of "items in", "items out" and "carry over" by day. Since I do it by month, the formula shows the "carry over" number all through the end of the month. I do not want it to show up until the date is current, and then not go away on the previous columns. ie. Date 4/1/10 4/2/10 4/3/10 4/4/10 4/5/10 4/6/10 4/7/10 Work in 10 10 12 Work out 8 5 18 Carry Over 2 7 1 1 1 1 1 Any help would be greatly appre...

Apply HTML Formatting To All Outgoing Mail
Hello All My company has recently decided to apply a consistent formatting to all of our outbound email communication They have developed (using our existing Exchange 5.5, Office XP environment) an HTML-formatted message that they would like to use as a template My question is this Is there any way that we can configure this at the server level? We have a copy of Exchange Server 2003 sitting on the shelf waiting for our AD implementation to complete, so if it's required we can use that We just do not want to touch every desktop to configure Outlook with the appropriate signature file, fo...

excel time format
How can i format a cell that will display the ime as 08:00 when i enter 8? i have tried using the format hh:mm but when i enter 8 the cell displays 00:00:00 and the formula bar shows 08/01/1900 00:00:00. see http://cpearson.com/excel/DateTimeEntry.htm In article <C635A1E7-DCC6-4304-B236-1FA95FFE6075@microsoft.com>, JarvisC <JarvisC@discussions.microsoft.com> wrote: > How can i format a cell that will display the ime as 08:00 when i enter 8? i > have tried using the format hh:mm but when i enter 8 the cell displays > 00:00:00 and the formula bar shows 08/01/190...

Download from mutliple POP3 servers at the same time?
Hello, Can outlook download from multiple POP3 servers at the same time. My outlook seems to serially go through the servers, and only download from one at a time. Regards, Scott F1 NUT <noonehome@speakeasy.net> wrote: > Can outlook download from multiple POP3 servers at the same time. My > outlook seems to serially go through the servers, and only download > from one at a time. You can't have multiple POP connections open simultaneously. Outlook will, as you observe, download from each sequentially. -- Brian Tillman ...

SMTP Logs
I sent a test message at 2:30 p.m. and then I went to Exchange SMTP Logs and tracked my test message and it shows it was sent at 16:35, strange enough because it is should be showing 14:35. How can I fix this ... I want the SMTP logs to show 2:35 p.m instead of 14:35. Time on my Exchange Server and on my PC is correct. Please advise. Thank you in advance. Are you using local time on your smtp logs? In smtp virtual server properties | logging properties. -- Bharat Suneja MCSE, MCT -------------------------------- "WILDPACKET" <WILDPACKET@discussions.microsoft.com&g...

How can I create one mail that can send several times to many peop
Hi, Maybe my question is stupid, but I'm new to Outlook. I would like to send one mail to many people (not in one go) without having to retype the mail. I downloaded templates from Microsoft website but I don't know how I can change the text and save it. Anyone can help me? Thanks, Remy Remy wrote: > Hi, > > Maybe my question is stupid, but I'm new to Outlook. I would like to > send one mail to many people (not in one go) without having to retype > the mail. I downloaded templates from Microsoft website but I don't > know how I can change the text and ...

It's about time again
This is a multi-part message in MIME format. ------=_NextPart_000_0036_01C4A811.955E4470 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I am keeping track of time I work at home. The first formula in cells = E2 through E17 gives me the total hours I worked after entering starting = time and ending time. The second formula calculates the totals from the = E column. A problem arises when the sum is more than 24, if it's 27 = hours, the answer is 3 instead of 27. What can I use so the sum of my = hours just get totaled? Thanks...

formatting problem on imported data
I have a column that I set a custom format. On the data that is imported in, some cells are formatting according to the custom format, while other cells do not. On the cells that do not, I noticed that if I click in the formula bar, then click away, the formatting in the cell will change to the custom. So I have 2 questions - why is this happening, and is there an easy way to correct the whole column without having to click in the formula bar for each cell that is incorrect. Using Excel 2002. Lynn This is normally because the data coming in is seen as text by Excel and it tries t...

negative time values
not to worry, have found the answer but thanks anyway Mike ...

Conditional formatting again
Okay I have had a google and a read and am no wiser as to how I did it or how to do it My problem is this put simply. I have a range of cells called "Name" When Tom appears in the range I want it to be red When Jim appears in the range I want it to be blue When Ron appears in the range I want it to be green When Sue appears in the range I want it to be pink I have more names but won't bore you with them all. Is there a limit to how many I can have? Once again, Many thanks. Andrew On Feb 8, 3:07 pm, "@Homeonthecouch" <m...@home.com> wrote: > Okay I ha...

How to format a cell from date to week?
Dear all, How to format a cell date like "2009-12-10" to year /week format "YYYYWW" = 200950? Also how to determine number of weeks between 2 weeks like from 200950 to 201002 = 5 weeks? BR//nginhong You cannot achieve this with cell formatting. Excel does not support weeks in a date format. To change a date to yyyyww, use: =year(a1)*100+weeknum(a1) The number of weeks difference between two of these cells, use: =(int(a2/100)-int(a1/100))*52+(mod(a2,100)-mod(a1,100)) This calculates to 4 weeks, not 5, just as the difference between 201001 and 2010...

Date criteria 11-28-07
Hi, I have two fields: StartDate and EndDate. I would like my qry to pull out all students who are at my class between StarDate and EndDate that I supply as parameter. Ex: StartDate EndDate Student 1 01/15/07 12/15/07 Student2 02/16/07 05/01/08 Student3 04/01/06 04/01/07 Student4 01/01/06 01/14/07 If I enter StartDate: 01/15/07 and EndDate: 12/15/07, I would like to have student1 , student 2 and student 3 since either their StartDate or EndDate fall between these dates ( between 01/15/07 and 12/1...

3/10/2008 TIME: 12:30
NNTP TEST ...

Sync DC's time with external clock?
Hello, I was just wondering how other make sure their DC's time is kept correct as ours over the months can drift ahead of itself so I change oen DC's time time to the correct time and "net time \\DCname" the others, very manual I know. Hello Whiteford, Configure the DC with the PDCEmulator FSMO to an external time source: w32tm /config /manualpeerlist:PEERS /syncfromflags:manual /reliable:yes /update With "PEERS" you can set the time source, either DNS name (time.windows.com) or an ip address from a reliable time source. Here you can find ...

Pub formatting issues
I have recently upgraded from publisher 2000 to 2003 from an old computer running windows 98 to a new system running windows xp pro. I copied all the pub files from the old computer to the new computer and put them in the my documents folder. When I open the pub files on the new computer the formating is all screwed up and my logo's are all destorted. Is there something I need to change to get them to look like how they were created with my windows 98 machine -- Ambicious Same printer or different one? -- JoAnn Paules MVP Microsoft [Publisher] "Cocobola" <Cocobo...