Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
htm date & time format
I have opened a htm data set in Excel 2003 that contains the date & time as
yyyymmddhhmm (the cell is formatted as general) and need it to display as mm/dd/yyyy hh:mm. For example 199704010053 in cell A1 currently displays as 1.99704E+11. I would like it to display 04/01/1997 00:53. I have tried custom formatting but end up with ###... and hovering the mouse over the cell results in "Negative dates and times are displayed as ####" What is the correct formula and cell formatting? The htm data set dates & times are GMT and I need to convert it to PST/PDT. Is this possible or would it be a seperate formula? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
htm date & time format
Assuming that date/time is in A1, put this formula in B1:
=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2)) + TIME(MID(A1,9,2),RIGHT (A1,2),0) and format the cell using a custom format of "mm/dd/yyyy hh:mm". Copy down as required. Hope this helps. Pete On Nov 14, 12:25*am, Rob wrote: I have opened a htm data set in Excel 2003 that contains the date & time as yyyymmddhhmm (the cell is formatted as general) and need it to display as mm/dd/yyyy hh:mm. For example 199704010053 in cell A1 currently displays as 1.99704E+11. *I would like it to display 04/01/1997 00:53. I have tried custom formatting but end up with ###... and hovering the mouse over the cell results in "Negative dates and times are displayed as ####" What is the correct formula and cell formatting? The htm data set dates & times are GMT and I need to convert it to PST/PDT. * Is this possible or would it be a seperate formula? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
htm date & time format
I'm not sure what the time difference is between GMT and PST, but
let's say it is 5 hours, then you will need to add/subtract 5/24 to the formula I gave you. Hope this helps. Pete On Nov 14, 12:36*am, Pete_UK wrote: Assuming that date/time is in A1, put this formula in B1: =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2)) + TIME(MID(A1,9,2),RIGHT (A1,2),0) and format the cell using a custom format of "mm/dd/yyyy hh:mm". Copy down as required. Hope this helps. Pete On Nov 14, 12:25*am, Rob wrote: I have opened a htm data set in Excel 2003 that contains the date & time as yyyymmddhhmm (the cell is formatted as general) and need it to display as mm/dd/yyyy hh:mm. For example 199704010053 in cell A1 currently displays as 1.99704E+11. *I would like it to display 04/01/1997 00:53. I have tried custom formatting but end up with ###... and hovering the mouse over the cell results in "Negative dates and times are displayed as ####" What is the correct formula and cell formatting? The htm data set dates & times are GMT and I need to convert it to PST/PDT. * Is this possible or would it be a seperate formula?- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
htm date & time format
You can use this formula:
=--TEXT(A1,"0000\-00\-00 00\:00") And give it a custom format of: mm/dd/yyyy hh:mm You can add or subtract any number of hours you want to this formula: =--TEXT(A1,"0000\-00\-00 00\:00") - time(8,0,0) (or whatever you need) Rob wrote: I have opened a htm data set in Excel 2003 that contains the date & time as yyyymmddhhmm (the cell is formatted as general) and need it to display as mm/dd/yyyy hh:mm. For example 199704010053 in cell A1 currently displays as 1.99704E+11. I would like it to display 04/01/1997 00:53. I have tried custom formatting but end up with ###... and hovering the mouse over the cell results in "Negative dates and times are displayed as ####" What is the correct formula and cell formatting? The htm data set dates & times are GMT and I need to convert it to PST/PDT. Is this possible or would it be a seperate formula? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
htm date & time format
With the big number in A1, use:
=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID (A1,9,2),RIGHT(A1,2),0) and format as you like. I think PST is about GMT-9, but I am not sure. -- Gary''s Student - gsnu200814 "Rob" wrote: I have opened a htm data set in Excel 2003 that contains the date & time as yyyymmddhhmm (the cell is formatted as general) and need it to display as mm/dd/yyyy hh:mm. For example 199704010053 in cell A1 currently displays as 1.99704E+11. I would like it to display 04/01/1997 00:53. I have tried custom formatting but end up with ###... and hovering the mouse over the cell results in "Negative dates and times are displayed as ####" What is the correct formula and cell formatting? The htm data set dates & times are GMT and I need to convert it to PST/PDT. Is this possible or would it be a seperate formula? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
htm date & time format
Hi,
You can split this type of data by choose Data, Text to Columns and choosing Fixed Width, Next, then click between the 8th and 9th character in the Date Preview pane (between the date and the time) and a second one between the 10th and 11th character (between the hours and minutes. Click Next, determine the destination and, in the data preview pane click in the first column and from the Date drop down choose YMD. Click Finish. Let's say the destination cell was A1, you get three cells for each entry Date in column A, hours in B and minutes in C In D1 enter =A1+TIMEVALUE(B1&":"&C1) Format this as you need Cheers, Shane Devenshire "Rob" wrote: I have opened a htm data set in Excel 2003 that contains the date & time as yyyymmddhhmm (the cell is formatted as general) and need it to display as mm/dd/yyyy hh:mm. For example 199704010053 in cell A1 currently displays as 1.99704E+11. I would like it to display 04/01/1997 00:53. I have tried custom formatting but end up with ###... and hovering the mouse over the cell results in "Negative dates and times are displayed as ####" What is the correct formula and cell formatting? The htm data set dates & times are GMT and I need to convert it to PST/PDT. Is this possible or would it be a seperate formula? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
htm date & time format
Shane,
I was able to get one column to show the "mm/dd/yyyy", the second column to displays "hh", but the third column that displays the "mm" is incorrect. In this example the "mm" should be "53", but the result is "02", and the value of the cell is 02/22/1900. I formatted third column as custom "hh". Did I format the third column incorrectly? "Rob" wrote: I have opened a htm data set in Excel 2003 that contains the date & time as yyyymmddhhmm (the cell is formatted as general) and need it to display as mm/dd/yyyy hh:mm. For example 199704010053 in cell A1 currently displays as 1.99704E+11. I would like it to display 04/01/1997 00:53. I have tried custom formatting but end up with ###... and hovering the mouse over the cell results in "Negative dates and times are displayed as ####" What is the correct formula and cell formatting? The htm data set dates & times are GMT and I need to convert it to PST/PDT. Is this possible or would it be a seperate formula? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
htm date & time format
Thanks!
"Shane Devenshire" wrote: Hi, You can split this type of data by choose Data, Text to Columns and choosing Fixed Width, Next, then click between the 8th and 9th character in the Date Preview pane (between the date and the time) and a second one between the 10th and 11th character (between the hours and minutes. Click Next, determine the destination and, in the data preview pane click in the first column and from the Date drop down choose YMD. Click Finish. Let's say the destination cell was A1, you get three cells for each entry Date in column A, hours in B and minutes in C In D1 enter =A1+TIMEVALUE(B1&":"&C1) Format this as you need Cheers, Shane Devenshire "Rob" wrote: I have opened a htm data set in Excel 2003 that contains the date & time as yyyymmddhhmm (the cell is formatted as general) and need it to display as mm/dd/yyyy hh:mm. For example 199704010053 in cell A1 currently displays as 1.99704E+11. I would like it to display 04/01/1997 00:53. I have tried custom formatting but end up with ###... and hovering the mouse over the cell results in "Negative dates and times are displayed as ####" What is the correct formula and cell formatting? The htm data set dates & times are GMT and I need to convert it to PST/PDT. Is this possible or would it be a seperate formula? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
htm date & time format
Thanks!
"Gary''s Student" wrote: With the big number in A1, use: =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID (A1,9,2),RIGHT(A1,2),0) and format as you like. I think PST is about GMT-9, but I am not sure. -- Gary''s Student - gsnu200814 "Rob" wrote: I have opened a htm data set in Excel 2003 that contains the date & time as yyyymmddhhmm (the cell is formatted as general) and need it to display as mm/dd/yyyy hh:mm. For example 199704010053 in cell A1 currently displays as 1.99704E+11. I would like it to display 04/01/1997 00:53. I have tried custom formatting but end up with ###... and hovering the mouse over the cell results in "Negative dates and times are displayed as ####" What is the correct formula and cell formatting? The htm data set dates & times are GMT and I need to convert it to PST/PDT. Is this possible or would it be a seperate formula? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
htm date & time format
Thanks!
"Dave Peterson" wrote: You can use this formula: =--TEXT(A1,"0000\-00\-00 00\:00") And give it a custom format of: mm/dd/yyyy hh:mm You can add or subtract any number of hours you want to this formula: =--TEXT(A1,"0000\-00\-00 00\:00") - time(8,0,0) (or whatever you need) Rob wrote: I have opened a htm data set in Excel 2003 that contains the date & time as yyyymmddhhmm (the cell is formatted as general) and need it to display as mm/dd/yyyy hh:mm. For example 199704010053 in cell A1 currently displays as 1.99704E+11. I would like it to display 04/01/1997 00:53. I have tried custom formatting but end up with ###... and hovering the mouse over the cell results in "Negative dates and times are displayed as ####" What is the correct formula and cell formatting? The htm data set dates & times are GMT and I need to convert it to PST/PDT. Is this possible or would it be a seperate formula? -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
htm date & time format
Thanks for both replies!
"Pete_UK" wrote: Assuming that date/time is in A1, put this formula in B1: =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2)) + TIME(MID(A1,9,2),RIGHT (A1,2),0) and format the cell using a custom format of "mm/dd/yyyy hh:mm". Copy down as required. Hope this helps. Pete On Nov 14, 12:25 am, Rob wrote: I have opened a htm data set in Excel 2003 that contains the date & time as yyyymmddhhmm (the cell is formatted as general) and need it to display as mm/dd/yyyy hh:mm. For example 199704010053 in cell A1 currently displays as 1.99704E+11. I would like it to display 04/01/1997 00:53. I have tried custom formatting but end up with ###... and hovering the mouse over the cell results in "Negative dates and times are displayed as ####" What is the correct formula and cell formatting? The htm data set dates & times are GMT and I need to convert it to PST/PDT. Is this possible or would it be a seperate formula? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
htm date & time format
Shane,
Please disregard my reply that the "mm" column was incorrect. I re-checked the formula and I had an error. I corrected the error and everything works properly. Thanks! "Shane Devenshire" wrote: Hi, You can split this type of data by choose Data, Text to Columns and choosing Fixed Width, Next, then click between the 8th and 9th character in the Date Preview pane (between the date and the time) and a second one between the 10th and 11th character (between the hours and minutes. Click Next, determine the destination and, in the data preview pane click in the first column and from the Date drop down choose YMD. Click Finish. Let's say the destination cell was A1, you get three cells for each entry Date in column A, hours in B and minutes in C In D1 enter =A1+TIMEVALUE(B1&":"&C1) Format this as you need Cheers, Shane Devenshire "Rob" wrote: I have opened a htm data set in Excel 2003 that contains the date & time as yyyymmddhhmm (the cell is formatted as general) and need it to display as mm/dd/yyyy hh:mm. For example 199704010053 in cell A1 currently displays as 1.99704E+11. I would like it to display 04/01/1997 00:53. I have tried custom formatting but end up with ###... and hovering the mouse over the cell results in "Negative dates and times are displayed as ####" What is the correct formula and cell formatting? The htm data set dates & times are GMT and I need to convert it to PST/PDT. Is this possible or would it be a seperate formula? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time and Date Format | Excel Discussion (Misc queries) | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
format date time | Excel Worksheet Functions | |||
how do I format cells to change date and time to just date | Excel Discussion (Misc queries) | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions |