Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time Stamp Dilemna
I posted something similar to this a while back and the solution didn't
work. Now I am back on this issue. I import from a crystal report a summary log containing the records of when people where working. There is a start time and end time column that are formated as such : 9/26/2006 12:35:47 AM To extract the date from the time so I can compare who was where and on what date etc... I use text to columns, and this is where I have problems. For instance take the above date time stamp, when it is broken down by date and time I have the following: 9/26/2006 (In one column) 12:35:47 PM ( In the second column) I believe it is because the way Windows/Excel looks at the date and time...and is reversing the actual AM/PM section. I have another example that the date/time is: 10/18/2006 7:51:35 PM But it breaks out as: 10/18/2006 and 7:51:35 AM How can I get the time to remain AM if it is AM and PM if it is PM??? Thanks, Hans |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time Stamp Dilemna
Place a single quote (apostrophe ) before the data. When running Text to
Columns, put a single separator after the date. Excel should then leave the AM/PM alone. -- Gary's Student " wrote: I posted something similar to this a while back and the solution didn't work. Now I am back on this issue. I import from a crystal report a summary log containing the records of when people where working. There is a start time and end time column that are formated as such : 9/26/2006 12:35:47 AM To extract the date from the time so I can compare who was where and on what date etc... I use text to columns, and this is where I have problems. For instance take the above date time stamp, when it is broken down by date and time I have the following: 9/26/2006 (In one column) 12:35:47 PM ( In the second column) I believe it is because the way Windows/Excel looks at the date and time...and is reversing the actual AM/PM section. I have another example that the date/time is: 10/18/2006 7:51:35 PM But it breaks out as: 10/18/2006 and 7:51:35 AM How can I get the time to remain AM if it is AM and PM if it is PM??? Thanks, Hans |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time Stamp Dilemna
Guys,
Thanks for the help...Exactly what I needed!!! Hans vezerid wrote: Instead of using text-to-columns you can use formulas. If A2 correctly contains a date/time then B2 can hold the date and C2 the time with these formulas: B2: =INT(A2) ---format as m/d/yyyy C2: =MOD(A2,1) ---format as h:mm AM/PM HTH Kostis Vezerides Both cells to be formatted wrote: I posted something similar to this a while back and the solution didn't work. Now I am back on this issue. I import from a crystal report a summary log containing the records of when people where working. There is a start time and end time column that are formated as such : 9/26/2006 12:35:47 AM To extract the date from the time so I can compare who was where and on what date etc... I use text to columns, and this is where I have problems. For instance take the above date time stamp, when it is broken down by date and time I have the following: 9/26/2006 (In one column) 12:35:47 PM ( In the second column) I believe it is because the way Windows/Excel looks at the date and time...and is reversing the actual AM/PM section. I have another example that the date/time is: 10/18/2006 7:51:35 PM But it breaks out as: 10/18/2006 and 7:51:35 AM How can I get the time to remain AM if it is AM and PM if it is PM??? Thanks, Hans |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time Stamp Dilemna
Gary''s Student
I have seen this solution several times here in the newsgroup, but I have a question. Having run into the same problem on my own I solved it with formating instead of formulas. Formate your choice of date cell with a date formate and your choice of time cell with a time formate, use "=[original cell]" (without brackets and quotes of course) in the desired destination cells. Because this is a very "simple" solution and no one else has offered it, am I setting myself up for future problems? Mike Rogers "Gary''s Student" wrote: Place a single quote (apostrophe ) before the data. When running Text to Columns, put a single separator after the date. Excel should then leave the AM/PM alone. -- Gary's Student " wrote: I posted something similar to this a while back and the solution didn't work. Now I am back on this issue. I import from a crystal report a summary log containing the records of when people where working. There is a start time and end time column that are formated as such : 9/26/2006 12:35:47 AM To extract the date from the time so I can compare who was where and on what date etc... I use text to columns, and this is where I have problems. For instance take the above date time stamp, when it is broken down by date and time I have the following: 9/26/2006 (In one column) 12:35:47 PM ( In the second column) I believe it is because the way Windows/Excel looks at the date and time...and is reversing the actual AM/PM section. I have another example that the date/time is: 10/18/2006 7:51:35 PM But it breaks out as: 10/18/2006 and 7:51:35 AM How can I get the time to remain AM if it is AM and PM if it is PM??? Thanks, Hans |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time Stamp Dilemna
I think you will be O.K. with date/time datatypes. You just need to be
careful. Text to Columns can't always "see" the displayed format. For example, if you take a cell and: Format Cell Number Custom and enter General" is a nice number" in place of General then the number 2 would display as 2 is a nice number But Text to Columns can't see the trailing verbiage. -- Gary's Student "Mike Rogers" wrote: Gary''s Student I have seen this solution several times here in the newsgroup, but I have a question. Having run into the same problem on my own I solved it with formating instead of formulas. Formate your choice of date cell with a date formate and your choice of time cell with a time formate, use "=[original cell]" (without brackets and quotes of course) in the desired destination cells. Because this is a very "simple" solution and no one else has offered it, am I setting myself up for future problems? Mike Rogers "Gary''s Student" wrote: Place a single quote (apostrophe ) before the data. When running Text to Columns, put a single separator after the date. Excel should then leave the AM/PM alone. -- Gary's Student " wrote: I posted something similar to this a while back and the solution didn't work. Now I am back on this issue. I import from a crystal report a summary log containing the records of when people where working. There is a start time and end time column that are formated as such : 9/26/2006 12:35:47 AM To extract the date from the time so I can compare who was where and on what date etc... I use text to columns, and this is where I have problems. For instance take the above date time stamp, when it is broken down by date and time I have the following: 9/26/2006 (In one column) 12:35:47 PM ( In the second column) I believe it is because the way Windows/Excel looks at the date and time...and is reversing the actual AM/PM section. I have another example that the date/time is: 10/18/2006 7:51:35 PM But it breaks out as: 10/18/2006 and 7:51:35 AM How can I get the time to remain AM if it is AM and PM if it is PM??? Thanks, Hans |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time Stamp Dilemna
Gary''s Student
All I have used this for is the date/time data types. I was questioning my own way as useful to only move the part that I needed. The first time I did this I used NOW() in a hidden cell/column/row and used formating to do both date and time in my selected cell, and it worked fine. Another question people have is when using the NOW() function how to stop it from updating? I put it in data Validation as a list and use a dropdown to select, and formating to get the date or time I want. And it does not update! This is again a solution I have not seen here and was wondering what kind of troubles I am asking for? "Gary''s Student" wrote: I think you will be O.K. with date/time datatypes. You just need to be careful. Text to Columns can't always "see" the displayed format. For example, if you take a cell and: Format Cell Number Custom and enter General" is a nice number" in place of General then the number 2 would display as 2 is a nice number But Text to Columns can't see the trailing verbiage. -- Gary's Student "Mike Rogers" wrote: Gary''s Student I have seen this solution several times here in the newsgroup, but I have a question. Having run into the same problem on my own I solved it with formating instead of formulas. Formate your choice of date cell with a date formate and your choice of time cell with a time formate, use "=[original cell]" (without brackets and quotes of course) in the desired destination cells. Because this is a very "simple" solution and no one else has offered it, am I setting myself up for future problems? Mike Rogers "Gary''s Student" wrote: Place a single quote (apostrophe ) before the data. When running Text to Columns, put a single separator after the date. Excel should then leave the AM/PM alone. -- Gary's Student " wrote: I posted something similar to this a while back and the solution didn't work. Now I am back on this issue. I import from a crystal report a summary log containing the records of when people where working. There is a start time and end time column that are formated as such : 9/26/2006 12:35:47 AM To extract the date from the time so I can compare who was where and on what date etc... I use text to columns, and this is where I have problems. For instance take the above date time stamp, when it is broken down by date and time I have the following: 9/26/2006 (In one column) 12:35:47 PM ( In the second column) I believe it is because the way Windows/Excel looks at the date and time...and is reversing the actual AM/PM section. I have another example that the date/time is: 10/18/2006 7:51:35 PM But it breaks out as: 10/18/2006 and 7:51:35 AM How can I get the time to remain AM if it is AM and PM if it is PM??? Thanks, Hans |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time Stamp Dilemna
Once again there is little risk.
The "standard approach" is either to: 1. enter =NOW() in the cell and then copy/pastespecial value back onto the cell replacing the function with its value 2. just enter the value of NOW with a CTRL+; (semicolon) CTRL+SHIFT+: (colon) -- Gary's Student "Mike Rogers" wrote: Gary''s Student All I have used this for is the date/time data types. I was questioning my own way as useful to only move the part that I needed. The first time I did this I used NOW() in a hidden cell/column/row and used formating to do both date and time in my selected cell, and it worked fine. Another question people have is when using the NOW() function how to stop it from updating? I put it in data Validation as a list and use a dropdown to select, and formating to get the date or time I want. And it does not update! This is again a solution I have not seen here and was wondering what kind of troubles I am asking for? "Gary''s Student" wrote: I think you will be O.K. with date/time datatypes. You just need to be careful. Text to Columns can't always "see" the displayed format. For example, if you take a cell and: Format Cell Number Custom and enter General" is a nice number" in place of General then the number 2 would display as 2 is a nice number But Text to Columns can't see the trailing verbiage. -- Gary's Student "Mike Rogers" wrote: Gary''s Student I have seen this solution several times here in the newsgroup, but I have a question. Having run into the same problem on my own I solved it with formating instead of formulas. Formate your choice of date cell with a date formate and your choice of time cell with a time formate, use "=[original cell]" (without brackets and quotes of course) in the desired destination cells. Because this is a very "simple" solution and no one else has offered it, am I setting myself up for future problems? Mike Rogers "Gary''s Student" wrote: Place a single quote (apostrophe ) before the data. When running Text to Columns, put a single separator after the date. Excel should then leave the AM/PM alone. -- Gary's Student " wrote: I posted something similar to this a while back and the solution didn't work. Now I am back on this issue. I import from a crystal report a summary log containing the records of when people where working. There is a start time and end time column that are formated as such : 9/26/2006 12:35:47 AM To extract the date from the time so I can compare who was where and on what date etc... I use text to columns, and this is where I have problems. For instance take the above date time stamp, when it is broken down by date and time I have the following: 9/26/2006 (In one column) 12:35:47 PM ( In the second column) I believe it is because the way Windows/Excel looks at the date and time...and is reversing the actual AM/PM section. I have another example that the date/time is: 10/18/2006 7:51:35 PM But it breaks out as: 10/18/2006 and 7:51:35 AM How can I get the time to remain AM if it is AM and PM if it is PM??? Thanks, Hans |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time Stamp Dilemna
Gary''s Student
Thank you for you assistance. It is appreciated! Mike Rogers "Gary''s Student" wrote: Once again there is little risk. The "standard approach" is either to: 1. enter =NOW() in the cell and then copy/pastespecial value back onto the cell replacing the function with its value 2. just enter the value of NOW with a CTRL+; (semicolon) CTRL+SHIFT+: (colon) -- Gary's Student "Mike Rogers" wrote: Gary''s Student All I have used this for is the date/time data types. I was questioning my own way as useful to only move the part that I needed. The first time I did this I used NOW() in a hidden cell/column/row and used formating to do both date and time in my selected cell, and it worked fine. Another question people have is when using the NOW() function how to stop it from updating? I put it in data Validation as a list and use a dropdown to select, and formating to get the date or time I want. And it does not update! This is again a solution I have not seen here and was wondering what kind of troubles I am asking for? "Gary''s Student" wrote: I think you will be O.K. with date/time datatypes. You just need to be careful. Text to Columns can't always "see" the displayed format. For example, if you take a cell and: Format Cell Number Custom and enter General" is a nice number" in place of General then the number 2 would display as 2 is a nice number But Text to Columns can't see the trailing verbiage. -- Gary's Student "Mike Rogers" wrote: Gary''s Student I have seen this solution several times here in the newsgroup, but I have a question. Having run into the same problem on my own I solved it with formating instead of formulas. Formate your choice of date cell with a date formate and your choice of time cell with a time formate, use "=[original cell]" (without brackets and quotes of course) in the desired destination cells. Because this is a very "simple" solution and no one else has offered it, am I setting myself up for future problems? Mike Rogers "Gary''s Student" wrote: Place a single quote (apostrophe ) before the data. When running Text to Columns, put a single separator after the date. Excel should then leave the AM/PM alone. -- Gary's Student " wrote: I posted something similar to this a while back and the solution didn't work. Now I am back on this issue. I import from a crystal report a summary log containing the records of when people where working. There is a start time and end time column that are formated as such : 9/26/2006 12:35:47 AM To extract the date from the time so I can compare who was where and on what date etc... I use text to columns, and this is where I have problems. For instance take the above date time stamp, when it is broken down by date and time I have the following: 9/26/2006 (In one column) 12:35:47 PM ( In the second column) I believe it is because the way Windows/Excel looks at the date and time...and is reversing the actual AM/PM section. I have another example that the date/time is: 10/18/2006 7:51:35 PM But it breaks out as: 10/18/2006 and 7:51:35 AM How can I get the time to remain AM if it is AM and PM if it is PM??? Thanks, Hans |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Time Stamp Dilemna
You are very welcome !
-- Gary's Student "Mike Rogers" wrote: Gary''s Student Thank you for you assistance. It is appreciated! Mike Rogers "Gary''s Student" wrote: Once again there is little risk. The "standard approach" is either to: 1. enter =NOW() in the cell and then copy/pastespecial value back onto the cell replacing the function with its value 2. just enter the value of NOW with a CTRL+; (semicolon) CTRL+SHIFT+: (colon) -- Gary's Student "Mike Rogers" wrote: Gary''s Student All I have used this for is the date/time data types. I was questioning my own way as useful to only move the part that I needed. The first time I did this I used NOW() in a hidden cell/column/row and used formating to do both date and time in my selected cell, and it worked fine. Another question people have is when using the NOW() function how to stop it from updating? I put it in data Validation as a list and use a dropdown to select, and formating to get the date or time I want. And it does not update! This is again a solution I have not seen here and was wondering what kind of troubles I am asking for? "Gary''s Student" wrote: I think you will be O.K. with date/time datatypes. You just need to be careful. Text to Columns can't always "see" the displayed format. For example, if you take a cell and: Format Cell Number Custom and enter General" is a nice number" in place of General then the number 2 would display as 2 is a nice number But Text to Columns can't see the trailing verbiage. -- Gary's Student "Mike Rogers" wrote: Gary''s Student I have seen this solution several times here in the newsgroup, but I have a question. Having run into the same problem on my own I solved it with formating instead of formulas. Formate your choice of date cell with a date formate and your choice of time cell with a time formate, use "=[original cell]" (without brackets and quotes of course) in the desired destination cells. Because this is a very "simple" solution and no one else has offered it, am I setting myself up for future problems? Mike Rogers "Gary''s Student" wrote: Place a single quote (apostrophe ) before the data. When running Text to Columns, put a single separator after the date. Excel should then leave the AM/PM alone. -- Gary's Student " wrote: I posted something similar to this a while back and the solution didn't work. Now I am back on this issue. I import from a crystal report a summary log containing the records of when people where working. There is a start time and end time column that are formated as such : 9/26/2006 12:35:47 AM To extract the date from the time so I can compare who was where and on what date etc... I use text to columns, and this is where I have problems. For instance take the above date time stamp, when it is broken down by date and time I have the following: 9/26/2006 (In one column) 12:35:47 PM ( In the second column) I believe it is because the way Windows/Excel looks at the date and time...and is reversing the actual AM/PM section. I have another example that the date/time is: 10/18/2006 7:51:35 PM But it breaks out as: 10/18/2006 and 7:51:35 AM How can I get the time to remain AM if it is AM and PM if it is PM??? Thanks, Hans |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time & Date Stamp 2 worksheets | Excel Discussion (Misc queries) | |||
Add time and date stamp to Excel comments. | Excel Discussion (Misc queries) | |||
Using VLOOKUP with a Date and Time | Excel Discussion (Misc queries) | |||
convert interval to various separate date , time, hr, minutes | Excel Worksheet Functions | |||
help with date and time | Excel Worksheet Functions |