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)

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

1 Replies
509 Views

Similar Articles

[PageSpeed] 17

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:

http://groups.google.com/advanced_group_search?q=group:*excel*

See

    http://cpearson.com/excel/newposte.htm

for tips on using these groups effectively.

In article <5DB7C3E4-113A-4CF0-8F4E-3B0D1A5898D1@microsoft.com>,
 "Mark Ada" <MarkAda@discussions.microsoft.com> 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)
0
jemcgimpsey (6723)
12/2/2004 12:07:31 AM
Reply:

Similar Artilces:

Custom Formatting for Dates
Is there a manner of formatting to see date as Text?, if entered in same cell. Think that is what I need. Using custom formatting: yymmdd and making column narrow, so just see dd (with right justify). Problem is that going from text to date format, just see ## is there way to make yymmdd in custom to conform to text??? Thanks a1 11/2/05 b1 =TEXT(A1,"yymmdd") you will get b1 051102 ==================== "nastech" <nastech@discussions.microsoft.com> wrote in message news:89817178-868E-4E3C-9946-6A1E93B0080F@microsoft.com... > Is there a man...

visible cell only
I'd like to use the PERCENTILE function in a list that has been autofiltered and get the results based only on the visible cells. I've used SUBTOTAL in order to get count, average, min and max. But I need to get the .25 and .75 percentile figures for the filtered data (visible cells only). I've scoured these forums. I've scoured the web. I've found some vba code that was supposed to select only visible cells but it doesn't work for me. I posted last week in the programming section of these forums (and again this morning) but got no reply. I figure...

Using part of a cell in a chart title
I have a chart which should get a title. However, this should be partly be used from a cell e.g. "counted with 5%" 5% should be taken from the cell and used in the title. Is this possible? Hi, Yes it's possible but all of the chart title needs to be in the cell. So you may need to use a helper cell and concatenate text and value. http://www.andypope.info/tips/tip001.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Nicole" <Nicole@discussions.microsoft.com> wrote in message news:5CB7A971-AA7F-4C34-BB42-7DC283AA2958@micro...

how do i add time up in exel
everytime i use =sum(a1:a30)*24 i keep getting an odd number even though i only enter even numbers ,this is to add my hours worked in a month Tell us what's in a1:a30. If they are all even numbers, you should definitely get an even result. But if they include minutes, you can get an odd number of hours. -- Regards, Fred Please reply to newsgroup, not e-mail "loadie" <loadie@discussions.microsoft.com> wrote in message news:12306C49-050C-4DED-AD75-2ECEE54A24F7@microsoft.com... > everytime i use =sum(a1:a30)*24 i keep getting an odd number even though > i &g...

Timed Rental
Retail Management Systems Ltd. has just released a new addon for Timed Rental. RMS TimeZone allows you to rent out 'zones'. The easy thing is, you decide what a zone is. Whether you own a golf dome, billiard hall, shooting range (see list below for many other industries) you have the power to specify base pricing, minimum times, and additional per minute pricing. The visual interface tells the employees which zones are occupied/rented, by which customer, and how long the zone has been in use. TimeZone supports an unlimited number of zones. This add-on provides easy to use mouse...

Lookup based on criteria in 2 columns
Hi, I am trying to use a vlookup or other function to return the value in the amount column based on the location and date. Here is a sample of my data: Location Date Amount 101 9/15/8 10 101 9/16/8 20 101 9/17/8 15 102 9/15/8 50 102 9/16/8 75 102 9/17/8 67 For example if I wanted to return the amount for location 102 on 9/15/8, what formula would I use? I tried using variations of vlookups but had no luck. Thanks, =SUMPRODUCT(--(A2:A50=102),--(B2:B50=DATE(2008,9,15)),C2:C50) -- Regards, Peo Sjo...

Microsoft POS #2
I have a group of customers that I am working on setting up A bundled POS system for. I have looked at RMS and Microsoft POS. The customer's are small bulk food stores. They package all of the bulk food in various containers and weigh it with a Hobart scale producing a label with an system 2 price enbedded barcode. These are UPC A formats. They are for the most part simple single store installs and donot require the complex RMS system. I have setup the Microsoft POS version 2.0 and found it will work perfect for these customers. The only problem when you programed the system you l...

Outlook 2003 keeps crashing #2
I get this message when I look in System information. Outlook keeps crashing quite often. I cannot for example delete two messages without outlook crashing. My colleagues do not have this problem. We are using the Microsoft mail exhange server. Faulting application outlook.exe, version 11.0.5510.0, stamp 3f1380f0, faulting module mso.dll, version 11.0.5606.0, stamp 3f334cce, debug? 0, fault address 0x003b6e16. Does anyone has a solution?=20 Best regards/ Bj=F6rn 1) I would definitely apply Service Pack 2for Office 2003 + applicable critical security updates. 2) Have you tried starting O...

move cell contents
Is there a way to move a cell contents to another cell with a formula. ex: if a5="Name" then move g5 to j5? Also, I am using =INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random names from a list. I have the formula in different place pick random names from different list. This does work, but I have different list with some of the same names and with the random pick I do not want the same name to appear. -- Thanks for any and all help. Davidl Hi David a formula can only affect the cell it is in, it can't move or change another cell for this you need some code ...

Rollup 2 and Office 2007
I already have Roll Up 1 and the Office 2007 compatibility patch installed. If I now install roll up 2 will I have to reinstall the office 2007 compatibility? yes -- Regards, MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "LLoyd" wrote: > I already have Roll Up 1 and the Office 2007 compatibility patch installed. > If I now install roll up 2 will I have to reinstall the office 2007 > compatibility? ...

what's the formula for adding symbols in cells?
I have a chart that has blank info in the legend. I want to add an * to indicate something, but just inserting a symbol doesn't work. Any ideas? Thanks. Debi - To add information to the legend, you need to add to a series name. Right click on the chart, select Source Data from the pop up menu, click on the series tab, select a series, and either type something in the name box, or click in it and select a cell with the mouse. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Debi wrote: > I have a chart tha...

Time differances
1 have 4 boxes start time end time lunch hours worked 09:00 17:00 1.00 7:00 09:00 17:00 0.30 7:70 (should be 7:50) (B3-A3)*24-C3 How do I make it 9.5 hours by changing forumla Thanks Trever, =(B3-A3)*24-C3*24 Format for General, Number, or anything except Date/Time. Format - Cells - Number. You'll get 7.5. If you got 7:50, as you requested, it would look like hours and minutes. -- Earl Kiosterud www.smokeylake.com "Trever B" <TreverB@discussions.microsoft.c...

Convert date
How can I convert a date in this format (mm-dd-yyyy) to a Julian-date (i.e., YYYYDDD)? For example: 09-13-2003 to 2003256 Hi see: http://www.cpearson.com/excel/jdates.htm -- Regards Frank Kabel Frankfurt, Germany gary wrote: > How can I convert a date in this format (mm-dd-yyyy) to a > Julian-date (i.e., YYYYDDD)? > > For example: 09-13-2003 to 2003256 On Wed, 8 Sep 2004 10:24:57 -0700, "gary" <anonymous@discussions.microsoft.com> wrote: >How can I convert a date in this format (mm-dd-yyyy) to a >Julian-date (i.e., YYYYDDD)? > >For exa...

Can you only merge up to a certain number of cells
I am working on microsoft excel 2003, I have a sheet that I merged cells starting with line 8 through 43...when I type my information in the merged area I can see all that I am typing...say it goes up to line 30 once I hit the enter key I can only see up to line 20. Even when I print it out it only prints up to line 20...I have checked to make sure there are not locked cells etc. I cannot figure out at all why this is happening...is there only up to a certain number of cells you can merge? From "Excel Specifications and Limits" Length of cell contents (text) ...

matching contents #2
E1 thru E10 contains sequentially-sorted data. B1 thru B6 contains sequentially-sorted data which matches SOME (but not ALL) of the data in Col A. For example. B E 1 1 1 2 3 2 3 4 3 4 5 4 5 6 5 6 10 6 7 7 8 8 9 9 10 10 I need to move the data in the cells in Col B next to the cells in Col E that have the same contents. For example: B E 1 1 1 2 2 3 3 3 4 4 4 5 5 5 6 6 6 7 7 8 8 9 9 10 10 10 Besides dr...

formating date from general2 custom
I have an input xls file with the date but like this: Wed May 14 00:00:00 GMT-05:00 2008 I need to have just following format dd/mm/yyyy I tried to format it using Format->Cells->Date or Format->Cells- >Custom but this is failing. Nothing changed. Any ideas? Thanks for the help. Vlad <vladimir.vaynshtok@gmail.com> wrote in message news:1013b138-352b-4799-81ba-9508b74a1850@t54g2000hsg.googlegroups.com... >I have an input xls file with the date but like this: > > Wed May 14 00:00:00 GMT-05:00 2008 > > I need to have just following format > > d...

Font dosen't revert back to original after typing in date
Example I n my text I type I have December 28th The th automatically reduces to a much smaller font and once you hit the sapce bar to continue it reverts back to the larger font. It only does this some of times, why is this being so inconsistent?. I refortmated my comp. last week and when I reinstalled office I was hoping this inconsistent problem would have been resloved, but no 2nd question please. When I post a question in "discussion group" and check the box "Notify me of Replies" I never receice a reply to my question I then have to go back to ...

Looking for help on timing within a function
At the facility I work, our associates use a very basic access database to sign out individual work orders on an interval of around every 12 minutes. They have asked if it would be possible to put a timer on the front page of the database to show how long they have been working on the current work order, updating each time they sing out another order. I am not very adept at access nor is the rest of our management staff. Is it possible to add this function, so they can better keep time of their work being done? Use the Form Timer event. Here is an example of how it can be done. In this ex...

Please ignore #2
Testing a posting problem -- Jim Bunton ...

How do I merge cells with multiple data values?
I've tried highlighting the two cells which are in the same row. It suggested that I format and align. Both of which I've tried. I keep getting the same error message, "The selection contains multiple data values. Merging into one cell will keep the upper-most data only." I need to make the cells one with all my information. Is this possible? Not knowing what you want to do, let me make a suggestion. Put all your data into the first cell and leave the second cell empty. Select both cells. Click on Format - Cells - Alignment tab. In the "Horizontal" b...

dates #5
Is there a formula to ad a numeric value to the name of a month. I am running a query in excel (pulling from an external source) and would like to sort as teh table refreshes by month and year. I have code to to do the refresh and the sort, but it sorts in alphabetical order. I was thinking a formula could place a numeric value to the month (1 = Jan, 2 = Feb, etc) and I can sort based on that. Is there an easy formula to do this. Thanks in advance Your easy formula could be: =MATCH(A1,{"Jan","Feb","Mar","Apr","May","Jun"...

2 register ruinning together on one z report
Is this possible, to have two registers running on one z report ? We have a small store and would like to setup a second register as a backup, temporary addition ( during rush hours) Since we are a small show i do not need to have a separate register reports. Can two registers ring up all sales together as one ? How can i set this up ? No, you can't do that. You might be able to use the Register Analysis report available in SO Manager Utilities/Crystal Reports to get something close to a single Z though. Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.c...

reason codes #2
I'm having a big problem tracking discounts in RMS. Whenever something is discounted up front, a reason code is set. There are several I use for discounts. However, when I try to do an Item Movement History report and select based on those reason codes, I never get any matches. When I do an item history report for a few weeks, I notice that the only reason codes that are displayed are the ones pertaining to inventory adjustment. It seems that reason codes associated with discounts do not display in this report. HELP! I need these to display to track discounts (and also store use)! ...

How do I copy data in single cell format to a merged cell format
I need to copy a list of data in single cell format into a spreadsheet where those same columns are merged cells (with four cells merged into one). I have several hundred lines of data so some way of copy & paste (other than each cell individually) would be appreciated. Thanks Paul Hi Paul What I would do is copy format of sheet with merge cells to blank sheet then copy single cell data to sheet with merge cells select single cell to paste you lose formatting so after just copy format from blank sheet created earlier I find this easiest way Hope this helps Tina "Paul" w...

Is there a way to schedule a email to send out at a certain time?
I tried delay the mail, but the sent hour is not right. For example, the email should go out at 8am, but the sent time is 5:30pm which should be 8am also. Please help! "Grace" <Grace@discussions.microsoft.com> wrote in message news:1395D87B-2982-4800-8522-165781125E6A@microsoft.com... >I tried delay the mail, but the sent hour is not right. > For example, the email should go out at 8am, but the sent time is 5:30pm > which should be 8am also. Please help! The Sent time and the delivery time are completely separate. The Sent time that people will see w...