Date Formatting Problem

Hi

I run Excel 2K

I download info from the mainframe into a workbook.

This information contains a date for each entry.

This is the format that the "date" is downloaded in (29:53.0)....when the
curser is placed on this example it displays it as 31/07/2009 12:29:53 AM in
the "edit bar" of the spreadsheet.

I can then format the (29:53.0) to dd/mmm/yy which then reads 31/Jul/09.

However in the edit bar still shows it as 31/07/2009 12:29:53 AM.

I need to be able to format it so that in the edit bar it only shows the
dd/mmm/yy and not have the time format attched to it.

The reason I need this to happen is that I have a formula that looks at this
date and if it is greater than or equal to the date it displays a TRUE or
FALSE.

The "time" at the end is what is mucking it up as when I just put a date in
manually the formula works fine. I have used this formula in many worksheets
before so I know it works ok.

By the way the formula is =AND(D3=Start_Date,D3=End_Date)

Anyway, what I need is a way to format this date so that it does not include
the time component or a formula that removes it.


Thanks


John

On Mon, 10 Aug 2009 18:44:01 -0700, John Calder
wrote:

Hi

I run Excel 2K

I download info from the mainframe into a workbook.

This information contains a date for each entry.

This is the format that the "date" is downloaded in (29:53.0)....when the
curser is placed on this example it displays it as 31/07/2009 12:29:53 AM in
the "edit bar" of the spreadsheet.

I can then format the (29:53.0) to dd/mmm/yy which then reads 31/Jul/09.

However in the edit bar still shows it as 31/07/2009 12:29:53 AM.

I need to be able to format it so that in the edit bar it only shows the
dd/mmm/yy and not have the time format attched to it.

The reason I need this to happen is that I have a formula that looks at this
date and if it is greater than or equal to the date it displays a TRUE or
FALSE.

The "time" at the end is what is mucking it up as when I just put a date in
manually the formula works fine. I have used this formula in many worksheets
before so I know it works ok.

By the way the formula is =AND(D3=Start_Date,D3=End_Date)

Anyway, what I need is a way to format this date so that it does not include
the time component or a formula that removes it.


Thanks


John


Formatting does not change the contents of a cell, only the appearance.

To effectively remove the time portion, use the INT worksheet function:

=AND(D3=INT(Start_Date),D3=INT(End_Date))
--ron

Ron

Thanks for your prompt response.

Unfortunately your response was not what I wa after as this formula returns
a true or false which has nothing to do with my date issue. (although I did
try it anyway and I still have the problem)


I really need a formula that just strips the hh:mm:ss from the date. I think
this will fix my problem.



Thanks

John


"Ron Rosenfeld" wrote:

On Mon, 10 Aug 2009 18:44:01 -0700, John Calder
wrote:

Hi

I run Excel 2K

I download info from the mainframe into a workbook.

This information contains a date for each entry.

This is the format that the "date" is downloaded in (29:53.0)....when the
curser is placed on this example it displays it as 31/07/2009 12:29:53 AM in
the "edit bar" of the spreadsheet.

I can then format the (29:53.0) to dd/mmm/yy which then reads 31/Jul/09.

However in the edit bar still shows it as 31/07/2009 12:29:53 AM.

I need to be able to format it so that in the edit bar it only shows the
dd/mmm/yy and not have the time format attched to it.

The reason I need this to happen is that I have a formula that looks at this
date and if it is greater than or equal to the date it displays a TRUE or
FALSE.

The "time" at the end is what is mucking it up as when I just put a date in
manually the formula works fine. I have used this formula in many worksheets
before so I know it works ok.

By the way the formula is =AND(D3=Start_Date,D3=End_Date)

Anyway, what I need is a way to format this date so that it does not include
the time component or a formula that removes it.


Thanks


John


Formatting does not change the contents of a cell, only the appearance.

To effectively remove the time portion, use the INT worksheet function:

=AND(D3=INT(Start_Date),D3=INT(End_Date))
--ron

On Tue, 11 Aug 2009 13:16:01 -0700, John Calder
wrote:

Ron

Thanks for your prompt response.

Unfortunately your response was not what I wa after as this formula returns
a true or false which has nothing to do with my date issue. (although I did
try it anyway and I still have the problem)


I really need a formula that just strips the hh:mm:ss from the date. I think
this will fix my problem.



Thanks

John

I guess I don't understand what you want.

"To effectively remove the time portion, use the INT worksheet function"

I just embedded in the formula you wrote you were using because you wrote:

The reason I need this to happen is that I have a formula that looks at this
date and if it is greater than or equal to the date it displays a TRUE or
FALSE.

By the way the formula is =AND(D3=Start_Date,D3=End_Date)

I thought I had given you instructions both on ways to remove the time
information, and also an example as to how to use it in the formula you are
using.

Since neither of those suggestions appear to be helpful, you will have to be
more specific, (or maybe someone else can understand what you want).
--ron