the cell content = Fri 18/08/06 17:00
I want to display in date time format just the date

so i select

Select Format cell to dd/mm/yyyy

however this doesnot alter the field value to display in that format. I am
working with a csv file would this effect this??

I basically want to use that date formatted field and adjust another cell
representing that field by 7 days, however i get the #Value error duie to the
previous cell format not working!

Any help would be greatly appreciated!

I have tried doing this with the file saved as an excel workbook however the
field still doesnt change the format to dd/mm/yyyy?

"Jon" wrote:

the cell content = Fri 18/08/06 17:00
I want to display in date time format just the date

so i select

Select Format cell to dd/mm/yyyy

however this doesnot alter the field value to display in that format. I am
working with a csv file would this effect this??

I basically want to use that date formatted field and adjust another cell
representing that field by 7 days, however i get the #Value error duie to the
previous cell format not working!

Any help would be greatly appreciated!

The original date structure has been imported from MSP and appears in the
following format - Fri 19/05/06 08:00

"Jon" wrote:

I have tried doing this with the file saved as an excel workbook however the
field still doesnt change the format to dd/mm/yyyy?

"Jon" wrote:

the cell content = Fri 18/08/06 17:00
I want to display in date time format just the date

so i select

Select Format cell to dd/mm/yyyy

however this doesnot alter the field value to display in that format. I am
working with a csv file would this effect this??

I basically want to use that date formatted field and adjust another cell
representing that field by 7 days, however i get the #Value error duie to the
previous cell format not working!

Any help would be greatly appreciated!

Hope this helps someone in the future it was to do with the MSP export which
was formatted to export as Fri 19/05/06 etc.....i set theis to 19/05/06
etc....a nd it worked fine!

"Jon" wrote:

The original date structure has been imported from MSP and appears in the
following format - Fri 19/05/06 08:00

"Jon" wrote:

I have tried doing this with the file saved as an excel workbook however the
field still doesnt change the format to dd/mm/yyyy?

"Jon" wrote:

the cell content = Fri 18/08/06 17:00
I want to display in date time format just the date

so i select

Select Format cell to dd/mm/yyyy

however this doesnot alter the field value to display in that format. I am
working with a csv file would this effect this??

I basically want to use that date formatted field and adjust another cell
representing that field by 7 days, however i get the #Value error duie to the
previous cell format not working!

Any help would be greatly appreciated!

At a guess - Excel is treating the entry as text, not a date. Therefore,
changing the cell's format will have no affect on the display.

You can determine how Excel is treating the entry by selecting an empty cell
formatted as General and entering a reference to the cell with the entry. If
it comes back as the same string, then Excel thinks it's text. If a number,
then Excel thinks it's a date


"Jon" wrote:

The original date structure has been imported from MSP and appears in the
following format - Fri 19/05/06 08:00

"Jon" wrote:

I have tried doing this with the file saved as an excel workbook however the
field still doesnt change the format to dd/mm/yyyy?

"Jon" wrote:

the cell content = Fri 18/08/06 17:00
I want to display in date time format just the date

so i select

Select Format cell to dd/mm/yyyy

however this doesnot alter the field value to display in that format. I am
working with a csv file would this effect this??

I basically want to use that date formatted field and adjust another cell
representing that field by 7 days, however i get the #Value error duie to the
previous cell format not working!

Any help would be greatly appreciated!

This formula in a helper column seems to work:

=DATE("20"&MID(A4,11,2),MID(A4,8,2),MID(A4,5,2))

Doesn't seem like you should need to specify 20 (for 2006,) but when I left
it off, it used 1906 dates.
--
Kevin Vaughn


"Jon" wrote:

The original date structure has been imported from MSP and appears in the
following format - Fri 19/05/06 08:00

"Jon" wrote:

I have tried doing this with the file saved as an excel workbook however the
field still doesnt change the format to dd/mm/yyyy?

"Jon" wrote:

the cell content = Fri 18/08/06 17:00
I want to display in date time format just the date

so i select

Select Format cell to dd/mm/yyyy

however this doesnot alter the field value to display in that format. I am
working with a csv file would this effect this??

I basically want to use that date formatted field and adjust another cell
representing that field by 7 days, however i get the #Value error duie to the
previous cell format not working!

Any help would be greatly appreciated!