Date Time Stamp Dilemna

[hansjhamm]

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

 

[Guest]

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.

 

[vezerid]

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

 

[hansjhamm]

Guys,

Thanks for the help...Exactly what I needed!!!

Hans

 

[Guest]

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

 

[Guest]

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

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

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.

 

[Guest]

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?

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

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

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


:

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

 

[Guest]

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

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?

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

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
:

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


:

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

 

[Guest]

Gary''s Student

Thank you for you assistance. It is appreciated!

Mike Rogers

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

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?

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


:

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
:

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


:

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

 

[Guest]

You are very welcome !
--
Gary's Student

Gary''s Student

Thank you for you assistance. It is appreciated!

Mike Rogers

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

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?

:

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


:

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
:

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


:

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