Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can you just pull Just the Time Value out of a Cell?
I was wondering if this was possible, it's been haunting me for awhile. I'm exporting data into a spreadsheet. There is a "created" column in which the cells include dates and times, and example of the contents of a cell is this "7/14/2006 1:49:03 PM" If you go to cell formatting, you can change it all you want, but the contents of the actual cell stay the same, what you see just changes. An example is if I pick 'h:mm' it will show the hours and minutes if you just look at the cell, but if you click on it to see its contents it still says 7/14/2006 1:49:03 PM. I need this for sorting purposes, so that I can put everything from a certain time together. As of now, since the date is still part of the cell, it will group them by day. Any help would be great, thanks! ~J -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=563764 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can you just pull Just the Time Value out of a Cell?
hi,
have you tried Datatext to columns yet. this will parce the date and time into seperate cells. you can do it manuelly or with macro. this sniplet is the parce code... Range("E1:E11").Select Selection.TextToColumns _ Destination:=Range("E1"), _ DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), _ Array(10, 1)), _ TrailingMinusNumbers:=True you will have to come up with your own select. i just used the range above to test. you may also have to adjust the arrays.(i tested date with 1 and 2 digit month and day and time with 1,2 and 0 digit hours. works) Regards FSt1 "nbaj2k" wrote: I was wondering if this was possible, it's been haunting me for awhile. I'm exporting data into a spreadsheet. There is a "created" column in which the cells include dates and times, and example of the contents of a cell is this "7/14/2006 1:49:03 PM" If you go to cell formatting, you can change it all you want, but the contents of the actual cell stay the same, what you see just changes. An example is if I pick 'h:mm' it will show the hours and minutes if you just look at the cell, but if you click on it to see its contents it still says 7/14/2006 1:49:03 PM. I need this for sorting purposes, so that I can put everything from a certain time together. As of now, since the date is still part of the cell, it will group them by day. Any help would be great, thanks! ~J -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=563764 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can you just pull Just the Time Value out of a Cell?
Since each day is a whole number, you can try:
=MOD(datetime,1) That will leave you with just the time portion. All the result might show 1/0/1900 along with the corresponding time. All you have to do is format those resulting cells to a time format. -Simon "nbaj2k" wrote: I was wondering if this was possible, it's been haunting me for awhile. I'm exporting data into a spreadsheet. There is a "created" column in which the cells include dates and times, and example of the contents of a cell is this "7/14/2006 1:49:03 PM" If you go to cell formatting, you can change it all you want, but the contents of the actual cell stay the same, what you see just changes. An example is if I pick 'h:mm' it will show the hours and minutes if you just look at the cell, but if you click on it to see its contents it still says 7/14/2006 1:49:03 PM. I need this for sorting purposes, so that I can put everything from a certain time together. As of now, since the date is still part of the cell, it will group them by day. Any help would be great, thanks! ~J -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=563764 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can you just pull Just the Time Value out of a Cell?
If those values aren't changing, then it's because excel is seeing them as text.
I'd try this. Close excel make sure your short windows date is for mdy (month, day, year) using windows control panel|regional settings. (Common in the USA, not so much in Europe.) Then back to excel. Select those cells edit|replace what: / (just a slash) with: / replace all Excel should see that you made a change and recognize the values as numbers (date and times). Try formatting them to test it out. And if you want just the time: =a1-int(a1) or =mod(a1,1) but format that cell as time. nbaj2k wrote: I was wondering if this was possible, it's been haunting me for awhile. I'm exporting data into a spreadsheet. There is a "created" column in which the cells include dates and times, and example of the contents of a cell is this "7/14/2006 1:49:03 PM" If you go to cell formatting, you can change it all you want, but the contents of the actual cell stay the same, what you see just changes. An example is if I pick 'h:mm' it will show the hours and minutes if you just look at the cell, but if you click on it to see its contents it still says 7/14/2006 1:49:03 PM. I need this for sorting purposes, so that I can put everything from a certain time together. As of now, since the date is still part of the cell, it will group them by day. Any help would be great, thanks! ~J -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=563764 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time sheet drop down lists | Excel Discussion (Misc queries) | |||
Capturing The First Change in a Formula | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
How do I get one cell to record the time another cell was changed. | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |