Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have columns of numbers that represent date/time info. For instance I have
the number 20081029092245 which represents: 2008/10/29 09:22:45 or October 29, 2008 at 9:22 AM And I want it to display as one of the above formats. I can parse the big number and concatenate the resulting columns, but is there a less cumbersome way to do this with Format/Cells/Number/Custom or something similar? Many thanks. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
You could use a helper column with a formula like:
=--TEXT(A1,"0000-00-00 00\:00\:00") and give the column a numberformat of: mmmm dd, yyyy hh:mm:ss or yyyy/mm/dd hh:mm:ss BDT wrote: I have columns of numbers that represent date/time info. For instance I have the number 20081029092245 which represents: 2008/10/29 09:22:45 or October 29, 2008 at 9:22 AM And I want it to display as one of the above formats. I can parse the big number and concatenate the resulting columns, but is there a less cumbersome way to do this with Format/Cells/Number/Custom or something similar? Many thanks. -- Dave Peterson |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID (A1,9,2),MID(A1,11,2),RIGHT(A1,2))
rather than a re-format of A1. Format the cell with the formula as Custom mm/dd/yyyy hh:mm:ss -- Gary''s Student - gsnu200810 "BDT" wrote: I have columns of numbers that represent date/time info. For instance I have the number 20081029092245 which represents: 2008/10/29 09:22:45 or October 29, 2008 at 9:22 AM And I want it to display as one of the above formats. I can parse the big number and concatenate the resulting columns, but is there a less cumbersome way to do this with Format/Cells/Number/Custom or something similar? Many thanks. |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Thu, 30 Oct 2008 06:48:01 -0700, BDT wrote:
I have columns of numbers that represent date/time info. For instance I have the number 20081029092245 which represents: 2008/10/29 09:22:45 or October 29, 2008 at 9:22 AM And I want it to display as one of the above formats. I can parse the big number and concatenate the resulting columns, but is there a less cumbersome way to do this with Format/Cells/Number/Custom or something similar? Many thanks. No there is not. Formatting only affects how the data is displayed, not how it is parsed on data entry. With your big number in A1, you could use this formula to convert it to a value Excel understands; and then format it however you like: =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID (A1,9,2),MID(A1,11,2),RIGHT(A1,2)) OR, if this will be used on systems utilizing the US Date format, you could convert it to a proper Excel date/time value with this formula: =--TEXT(A1,"0000\-00\-00\ 00\:00\:00") --ron |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks everybody.
The formula: =--TEXT(A1,"0000-00-00 00\:00\:00") worked fine when I deleted the 2 minus signs after the equal sign and the other one: =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID (A1,9,2),MID(A1,11,2),RIGHT(A1,2)) generated just the date which might be helpfull. But I couldn't get the Custom format to work with yyyy/mm/dd hh:mm:ss or mm/dd/yyyy hh:mm:ss. These just return an infinite row of # signs as wide as you set the column width. thanks again, BDT |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
The =text() will make the cell look like a date, but it's really text. You may
find that some of your date arithmetic may not work the way you want. The -- stuff coerces the text that looks like a date to a real number. But unless you apply a nice numberformat, you'll end up with something that looks like: 39750.3908 or 39750.3907986111 when you look at lots of decimals. Try changing the numberformat (format|cells|number tab in xl2003 menus) and you'll probably be happier. BDT wrote: Thanks everybody. The formula: =--TEXT(A1,"0000-00-00 00\:00\:00") worked fine when I deleted the 2 minus signs after the equal sign and the other one: =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID (A1,9,2),MID(A1,11,2),RIGHT(A1,2)) generated just the date which might be helpfull. But I couldn't get the Custom format to work with yyyy/mm/dd hh:mm:ss or mm/dd/yyyy hh:mm:ss. These just return an infinite row of # signs as wide as you set the column width. thanks again, BDT -- Dave Peterson |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Thu, 30 Oct 2008 10:52:23 -0700, BDT wrote:
Thanks everybody. The formula: =--TEXT(A1,"0000-00-00 00\:00\:00") worked fine when I deleted the 2 minus signs after the equal sign and the other one: =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MI D(A1,9,2),MID(A1,11,2),RIGHT(A1,2)) generated just the date which might be helpfull. But I couldn't get the Custom format to work with yyyy/mm/dd hh:mm:ss or mm/dd/yyyy hh:mm:ss. These just return an infinite row of # signs as wide as you set the column width. thanks again, BDT Your doing something unexpected, or your data is not as you describe. The TEXT function, after you removed the double unary, will return a text string and not a true date. So formatting will predictably not have any affect. The DATE function, generates both time and date -- you probably didn't format the result it properly. Getting an infinite row of # signs in response to your date/time formatting will occur if there is a negative number in that cell. Possibly you only removed one of the minus signs, or your value is somehow negative. Post back with more details of the precise problem, as well as copies (using the Windows copy/paste functions) of the information that is present in the FORMULA BAR when you select the source cell and the formula cells. --ron |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks everybody, your suggestions worked fine and I am able to format my
sheet fine. I appreciate the help. BDT "Ron Rosenfeld" wrote: On Thu, 30 Oct 2008 10:52:23 -0700, BDT wrote: Thanks everybody. The formula: =--TEXT(A1,"0000-00-00 00\:00\:00") worked fine when I deleted the 2 minus signs after the equal sign and the other one: =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MI D(A1,9,2),MID(A1,11,2),RIGHT(A1,2)) generated just the date which might be helpfull. But I couldn't get the Custom format to work with yyyy/mm/dd hh:mm:ss or mm/dd/yyyy hh:mm:ss. These just return an infinite row of # signs as wide as you set the column width. thanks again, BDT Your doing something unexpected, or your data is not as you describe. The TEXT function, after you removed the double unary, will return a text string and not a true date. So formatting will predictably not have any affect. The DATE function, generates both time and date -- you probably didn't format the result it properly. Getting an infinite row of # signs in response to your date/time formatting will occur if there is a negative number in that cell. Possibly you only removed one of the minus signs, or your value is somehow negative. Post back with more details of the precise problem, as well as copies (using the Windows copy/paste functions) of the information that is present in the FORMULA BAR when you select the source cell and the formula cells. --ron |
#9
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Thu, 6 Nov 2008 14:21:01 -0800, BDT wrote:
Thanks everybody, your suggestions worked fine and I am able to format my sheet fine. I appreciate the help. BDT Glad you got it figured out. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
splitting and formating a number series into date/time | Excel Worksheet Functions | |||
Sorting a large list by date (month number) | Excel Discussion (Misc queries) | |||
large number in cell | Excel Discussion (Misc queries) | |||
Entering Large Number | Excel Discussion (Misc queries) | |||
dissect large number | Excel Discussion (Misc queries) |