Combined date time cell to separate date & time components #2

From file dump have combined date time cells eg 14/04/03 14:20 (value 
37725.59722).  Want to perform time analysis so need to split to 2 separate 
cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722).   Is there a 
fnc to do this?  (Currently convert cell to value, then fncs trunc & cell 
less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively)

MarkAda (6)
12/1/2004 11:59:04 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 10

See the reply to your post in the microsoft.public.excel newsgroup.

Please don't post the same question to multiple groups. It just wastes 
the time of those replying to a question that was already answered, and 
fragments your answers.

In your case, this question was answered yesterday when you posted the 
same question. Look in the archives:*excel*


for tips on using these groups effectively.

In article <>,
 "Mark Ada" <> wrote:

> From file dump have combined date time cells eg 14/04/03 14:20 (value 
> 37725.59722).  Want to perform time analysis so need to split to 2 separate 
> cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722).   Is there a 
> fnc to do this?  (Currently convert cell to value, then fncs trunc & cell 
> less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively)
jemcgimpsey (6723)
12/2/2004 12:07:31 AM

Similar Artilces:

Email forwarding #2
Is there anyway to get your outlook emails forwarded automatically???? Hi! Microsoft Outlook, not Express? 1). Tools\Rules and Alerts... 2). Our Redirect add-in (, it redirect email and new recipient will receive the full copy of message, including internet headers and the same sender as in original email. > -----Original Message----- > From: Jason [] > Posted At: Wednesday, June 30, 2004 3:46 PM > Posted To: microsoft.public.outlook > Conversation: Email forwarding > Subject: Email forwarding > > Is there anyway to get your ...

Access Formula For Dates
I need to show on a weekly basis, the number of weeks left in the year. I don't want to have to enter it, I want Access to calculate it for me and show it on my report/database each time it is opened. For example , I want to say there are 34 weeks left in this year. Is there a formula I can usethat will determine the number of weeks remaining from today's date (whatever it is) to December 31? One way to look at this is that, if you can identify the "week" of the current date, you can subtract that from 52 to get an idea of how many weeks are left. .... o...

outlook 2003 has 2 "send" labels in the send receive drop down
hi, sometimes, i have to reboot my machine because the send recieve button in outlook does not work, when i go to the drop down menu, i dont see any of my "accounts" listed, and I see the word "send", then the next line is duplicated and also says 'send". the only way to get back to "normal" is to reboot the whole system until it happens again. This usually happens once per day. Close Outlook. Do a search for the outcmd.dat file (this is a hidden file so you'll have to either tell Windows Explorer to unhide it or search hidden files and folder...

How do u change date format to dd/mm/yyyy
Hi, does anybody know how to show all dates as dd/mm/yyyy instead of mm/dd/yyyy thks done it, thks anyway >-----Original Message----- >Hi, does anybody know how to show all dates as dd/mm/yyyy >instead of mm/dd/yyyy >thks >. > ...

Setting up Outlook 2003 #2
I am in the process of setting up Outlook 2003 on my new computer. I believe I may have click the wrong answer and have put in incorrect information for outlook to start. I keep getting a box that ask for Username and passcode. When I put in the information I think it should be, I receive this message: Unable to open your default e-mail. The file C:\Documents and Setting\Owner\Local Setting\Application Data\Microsoft\Outlook\outlook.ost is not an offline folder file. Please tell me what I should do. When I click OK. Outlook closes. Thanks. -- Trying to keep up with technology G...

query for Dates between today and 30 days back
Hi, I am trying to create a list from a the query that will be ran frequently, it is supposed to show employees on the list that are have dates between now and 30 days ago. I think that I am close with the where clause but no quite. Please will you help me? Thanks, Misty Ex. for today, 12/4/07, all dates between 12/4/07 and 11/5/07. Select Date, Employee From EmpRec Where Date >=DateSerial(Year(Date()),Day(Date()),1) And <DateSerial(Year(Date ()),Day(Date())+1,30) -- Message posted via WHERE [...

how do i set up drop down menus to populate cells in excel?
Hi, I'm trying to build a database in excel, and to aide data inputting, i'm trying to figure out if you can use drop down menu's like in the form view of access. eg in access you list all the possble responses and add this to the drop down menu for selection to populate the field so you don't have to type it out again. I'm using excel because several people will need access to the file and not many use access. Is it possible to do this in excel??? You can use Data Validation to create dropdown lists. There are instructions in Excel's help, and here: h...

Mails Combined
HI, I mistakenly delete all mails from Inbox and then found them on the deleted Items and selected all and ask to move folders back to Inbox. the problem is all mails were combined into one email and moved back in to Inbox as a single email. How do i go back to the original version. How do i uncombined all the mails in to individual mails from the right recipient? Please help ASAP.. Submitted using ...

Excel 2003- Tabbing to a specific location in a cell
I have created a template and I want to be able to fill in parts of the template by just tabbing and the cursor to move to the next cell...and to a specific area in the do i set up my template to do this? THanks so much unlock the cells you want to tab to. leave the other cells protected. then protected the sheet. -- Gary Keramidas Excel 2003 "monty the magician" <monty the> wrote in message >I have created a template and I want to be able to f...

Notification if no external e-mail recieved for over 2 hours.
Recently the SMTP authentication settings were changed so that no external e-mails could be recieved. There was no way to see that this was the case until someone complained. Is there any way to monitor externally recieved e-mails so that if none are recieved after a certain amount of time for a notification to be generated? In this case a script using telnet could be used to check if e-mails could be recieved externally, but I was looking more for something like if no external e-mails recieved by any users for two hours then create notification. I can't seem to think of any way to che...

Automatic settling of EDC Batch #2
Is there a way to stelle an EDC batch automatically in RMS, or an add-on that accomplishes this? This would be very handy. Thank you in advance. ...

merge/combine workshhets
I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Try this Jeff -- Regards Ron de Bruin "Jeff224" <> wrote in message >I need to merge about 75 worksheets into one spreadsheet on a regular basis - > all have exactly the same format/layout etc back can vary in the number of > rows Or http://www.rondebru...

FindWindow() taking much time to Execute
Hi all, In my application, FindWindow() is taking too much of time when I am not connected in the Network,but it works fine when connected to network.This problem is comming only in Windows 98.In rest of the Windows OS versions it is working fine irrespective of network connection. Is there any relation between this function & network. Here is the command I am using hwnd=FindWindow(NULL,pszStringDisc); Can anybody tell me why it is happening in this 98 OS version alone. Thanks in Advance. FindWindow(...) internally calls the GetWindowText(...) to get the title of the tar...

I have a time sheet but...
I have developed a time sheet. It simply tracks time per day and keeps a running total (in decimal hours) of time over consequtive days. HOWEVER, I would like the following elaborations. I would like to have a list of generalized tasks that I could add to as necessary. .... then be able to enter start and end times with a task that I spent that time on. .... then perhaps on a different sheet show total time, total time for each task, and percent of the total spent on each task. Any thoughts as to how I could proceed or might there be a downloadable example I might modify? Hi Hutch! Good...

Printing Entire Contents of Cell
A couple columns in a spreadsheet have lengthy text with the cell formatting set to wrap. Not all of the text displays when viewing the screen, but when clicking on the cell to edit, the contents are there. The real probem is that when printing, the contents of some of these cells are truncated. How do I get the entire contents to print? -- waterdawg ------------------------------------------------------------------------ waterdawg's Profile: View this thread:

payroll end dates
End dates for contract amounts, so that the payroll system stops paying ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane.

Finding the combination that appears more times
Hi everybody i have a list on excel 2007 that displays the purchase of items on columns A:E, each column showing one (1) item I want to find what combination of items appears more times, especifically, which combination of four (4) is the favorite mix eg: A B C D F A C D E F B C D E F B C D F Z in this example, the winner would be (B, C, D, E) as it appears 3 times the main problem is that I have over 1,000 rows and trying with =SUMPRODUCT(COUNTIF(A1:E1,$N$1:$R$1)) por each combination would take forever... is there a simpler, quicker ...

Reference a cell value in a formula
I have the following formula: =SUMPRODUCT((MONTH(Summary!$F$7:$F$15000)=1)*1) I want the "15000" to be replaced by the varaible value of a cell in an other worksheet ("Summary"). I tried using INDIRECT but it didn't work. Thanks in advance =SUMPRODUCT((MONTH(OFFSET(Summary!$E$7,0,0,C4,1))=1)*1) C4 in this case is the cell that holds the number of items to be used in the calculation -- If the post is helpful, please consider donating something to an animal charity on my behalf ..... and click Yes "Leon" wrote: > I have the f...

"Too many different cell formats" is preventing file from opening
An Excel 2000 workbook saved successfully but when attempting to open it again I receive the message, "Too many different cell formats." I have since learned that a maximum of 4000 format combinations are allowed in a workbook. I've also learned how to correct the problem. Unfortunately, Microsoft's instructions don't address how to get the file to open in order to carry out the corrective actions necessary. Any ideas about getting the file open? A recent post from Dave Peterson on this subject.......... Saved from a previous post: XL: Error Message: Too Ma...

Money 2005
I just reinstalled Money 2005 due to hard drive reformat. My electronic payments are not showing up in the register with the correct dates. I'm using CheckFree as my electronic payment provider. Example: I asked a payment to be made on 5-15-07 with the withdrawal date being 5-15-07. The payment is entered into the register as 5-09-07. If If I right click the payment status it shows me the correct withdrawal date. How to I get Money to display the correct withdrawal date in the register? CheckFree plans on 5 days worth of transaction time. If they have to physically mail a chec...

Exchange and Outlook #2
Guys, I'm new to Exchange and Outlook. Long story short the business I work for uses a third party pop3 e-mail tool for all users. For a select few I would like to set up Outlook to hit the exchange server using the corporate type setup. I've tried everything I know to connect to my mail server and Outlook cannot see it. We use Novell to authenticate for File and Print. What must be in place for Outlook and Exchange to talk to each other? Any suggestions would be appreciated. What kind of POP3 "tool"? Your company does have an Exchange server in = addition to POP3 ac...

Date value in cell with text
I would like a cell in Excel 2000 to have some text plus today's date, like this: "Total orders as of 3/09" I was able to pull the current date with =NOW() and right clicking the cell and choosing the date to look like 3/09 I was thinking that my formula should look like ="Total orders as of "&NOW() Any thoughts? ="Total orders as of "& TEXT(TODAY(),"m/dd") -- Kind regards, Niek Otten Microsoft MVP - Excel <> wrote in message >I...

Time Formula #2
Hi I run Excel 2K I download data from a mainframe. This data has a date & time format in it (dd/mm/yyyy hh:mm:ss) I work in a place that has a 3 shift cycle - day shift, afternoon shift, night shift. Day shift starts at 7:20am and ends at 15:29pm Afternoon shift starts at 15:30pm and ends at 23:19pm Night shift starts at 23:20pm amd ends at 7:19am I need a formula that looks at the cell with the date/time in it and displays the word DAY (for the time frame of day shift), AFTERNOON (for the timeframe of afternoon shift) and NIGHT (for the timeframe of night shift) In my previous ...

Combining 2 Excel (xls) files
I have 2 xls workbooks with different sheet names and both with different range names. I would like to combine them both into one workbook so that I don't have to re-create all of the range names. Is there a way to do that? -- LAF ------------------------------------------------------------------------ LAF's Profile: View this thread: Hi LAF- Open both files (let's call them A & B, and assume A will be your "combined" file). Working i...

Combining Publications
Can I combine two different publications into one? Suzi wrote: > Can I combine two different publications into one? ========================================= Personally, I would simply open two instances of Publisher (one document in each) / tile the two screens...and copy/paste. Maybe the following link will be useful: Combining Publisher Documents -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs Notice This is not tech support I am a volunteer Solutions that wo...