Truncate within a cell

M

matt.barton.uk

I've got a column of cells with a date and time separated
by a single space. I'm trying to delete the time.

The format is as follows

23-Apr-2006 20:00:00, This is stored as a text field.

I know that I can use the left function, but that gives me a small
issue and I have tried the following:

=srw.xls!G2+=LEFT(G2,FIND(" ",G2)-1)


The data is linked from another spreadsheet, but if I use a function to
shorten the field, it stays as a text field. Once the function is in
place, any link with the original data is lost.

How can I get around this?

In essance, I need to have an automatic updating field, linked to
another spreadsheet, that will only display the first 11 characters
whilst retaining the link to the original data source.

Thanks in anticipation.
 
R

Roger Govier

Hi Matt

When Excel stores date and time in a cell,
=INT(A1) will return just the date part
=MOD(A1,1) will return just the time

Maybe if your link just took the INT() of the linked cell this would
solve your problem.
 
M

matt.barton.uk

Hi Roger,

Thanks for the prompt reply.

Unfortunatly, when I use the INT function, I get incorrect date
information returned.

On an example cell that contains 23-Apr-2006 20:00:00 as a text field,
I get the date generated as 15th August 2112.

Any more options are more than welcome.

Cheers
 
P

Pete_UK

When you say you get your data from another spreadsheet, do you mean
from another sheet in the same file, or from another workbook? If the
latter, then will this workbook be open at the same time? I didn't
understand the formula you posted.

Pete
 
M

matt.barton.uk

Hi Pete,

Sorry for the confusion.

There are two workbooks involved. Only the one will be open. This will
be the report.

For example.

Data.xls - This will contain comma seperated values exported from a
logging system.

Report.xls - This will contain the above information, but formatted
nice.

The end user has not a lot of IT experience, so the idea is to make it
as simple as possible.

The data.xls file will be created by the user on a weekly basis. This
is all text based data. The date fields though include a created time
which we do not need. The format being as previously described.

Once this data.xls file has been created, it will be saved to the same
folder as Report.xls.

Once Report.xls is opened, after the option to update the external data
is taken, all the new information is displayed, ready for printing or
emailing out.

The issue that I am having is reducing the date / time field when it is
shown in Report.xls, but still retaining the link to the information
held in data.xls. When ammending the date field in Report.txt, the
field then go to a static text field and all links to the external data
held in data.xls is lost.

I am unable to perform any manipulation within data.xls.

Hope that clarifies my issue.

Thanks
 
P

Pete_UK

Hi Matt,

let's assume for the moment that the two files are open and that the
date/time information is in cell A1 of Sheet1 in Data.xls. This formula
in Report.xls will bring the data from Data.xls and truncate the time
portion of it:

=LEFT([Data.xls]Sheet1!$A$1,FIND(" ",[Data.xls]Sheet1!$A$1)-1)

Note the syntax - the filename is in square brackets.

However, if you now close the file Data.xls, then the filename gets
expanded to include the full path - on my machine the formula becomes:

=LEFT('C:\Documents and Settings\Pete\My
Documents\Developments\[Data.xls]Sheet1'!$A$1,FIND(" ",'C:\Documents
and Settings\Pete\My Documents\Developments\[Data.xls]Sheet1'!$A$1)-1)


Obviously, if you develop something on your PC and then transfer it to
another PC, then the full path may be different, as the username is
likely to be different, but you could get round this by creating a
folder directly in C:.

Anyway, this formula will maintain the link to Data.xls and will give
you what you require - obviously, you may need to change the cell
references (I think you referred to G2 earlier).

Hope this helps.

Pete
 
M

matt.barton.uk

Hi Pete,

Thanks for the update, but I am afraid I have the same situation.

Filenames are as such.

Datafile - srw.xls

Report - closure.xls

Both files reside in the same folder on a network share. This should
not change.

The relevant date field in srw.xls is located at G2 onwards (G3,G4
etc). Field example is 28-Apr-2006 20:00:00.

The data in the report, just refrences the srw.xls file. Example the
cell of F9 is reffered back to the original data by =srw.xls!$G$2

When I used your example formula, this is what I entered.

=LEFT([srw.xls]Sheet1!$G$2,FIND(" ",[wrs.xls]Sheet1!$G$")-1)

I did indeed get the correct format of the date in F9 of closure.xls,
but when the original cell of srw.xls!$G$2 is ammended, then the date
information in closure.xls!$F$9 stays static. It even shows up in the
formula bar as plain text.

Other fields update correctly when opening.

Help !!!!! :)



Pete_UK said:
Hi Matt,

let's assume for the moment that the two files are open and that the
date/time information is in cell A1 of Sheet1 in Data.xls. This formula
in Report.xls will bring the data from Data.xls and truncate the time
portion of it:

=LEFT([Data.xls]Sheet1!$A$1,FIND(" ",[Data.xls]Sheet1!$A$1)-1)

Note the syntax - the filename is in square brackets.

However, if you now close the file Data.xls, then the filename gets
expanded to include the full path - on my machine the formula becomes:

=LEFT('C:\Documents and Settings\Pete\My
Documents\Developments\[Data.xls]Sheet1'!$A$1,FIND(" ",'C:\Documents
and Settings\Pete\My Documents\Developments\[Data.xls]Sheet1'!$A$1)-1)


Obviously, if you develop something on your PC and then transfer it to
another PC, then the full path may be different, as the username is
likely to be different, but you could get round this by creating a
folder directly in C:.

Anyway, this formula will maintain the link to Data.xls and will give
you what you require - obviously, you may need to change the cell
references (I think you referred to G2 earlier).

Hope this helps.

Pete

Hi Pete,

Sorry for the confusion.

There are two workbooks involved. Only the one will be open. This will
be the report.

For example.

Data.xls - This will contain comma seperated values exported from a
logging system.

Report.xls - This will contain the above information, but formatted
nice.

The end user has not a lot of IT experience, so the idea is to make it
as simple as possible.

The data.xls file will be created by the user on a weekly basis. This
is all text based data. The date fields though include a created time
which we do not need. The format being as previously described.

Once this data.xls file has been created, it will be saved to the same
folder as Report.xls.

Once Report.xls is opened, after the option to update the external data
is taken, all the new information is displayed, ready for printing or
emailing out.

The issue that I am having is reducing the date / time field when it is
shown in Report.xls, but still retaining the link to the information
held in data.xls. When ammending the date field in Report.txt, the
field then go to a static text field and all links to the external data
held in data.xls is lost.

I am unable to perform any manipulation within data.xls.

Hope that clarifies my issue.

Thanks
Click to expand...
Click to expand...
 
P

Pete_UK

Sorry, Matt,

I can't repeat your findings here, but then again we have different
setups.

I did wonder, though, why you have these as two separate workbooks?
Could you not have a Data sheet and a Report sheet in one workbook?
This should simplify the linking.

Pete

[email protected] said:
Hi Pete,

Thanks for the update, but I am afraid I have the same situation.

Filenames are as such.

Datafile - srw.xls

Report - closure.xls

Both files reside in the same folder on a network share. This should
not change.

The relevant date field in srw.xls is located at G2 onwards (G3,G4
etc). Field example is 28-Apr-2006 20:00:00.

The data in the report, just refrences the srw.xls file. Example the
cell of F9 is reffered back to the original data by =srw.xls!$G$2

When I used your example formula, this is what I entered.

=LEFT([srw.xls]Sheet1!$G$2,FIND(" ",[wrs.xls]Sheet1!$G$")-1)

I did indeed get the correct format of the date in F9 of closure.xls,
but when the original cell of srw.xls!$G$2 is ammended, then the date
information in closure.xls!$F$9 stays static. It even shows up in the
formula bar as plain text.

Other fields update correctly when opening.

Help !!!!! :)



Pete_UK said:
Hi Matt,

let's assume for the moment that the two files are open and that the
date/time information is in cell A1 of Sheet1 in Data.xls. This formula
in Report.xls will bring the data from Data.xls and truncate the time
portion of it:

=LEFT([Data.xls]Sheet1!$A$1,FIND(" ",[Data.xls]Sheet1!$A$1)-1)

Note the syntax - the filename is in square brackets.

However, if you now close the file Data.xls, then the filename gets
expanded to include the full path - on my machine the formula becomes:

=LEFT('C:\Documents and Settings\Pete\My
Documents\Developments\[Data.xls]Sheet1'!$A$1,FIND(" ",'C:\Documents
and Settings\Pete\My Documents\Developments\[Data.xls]Sheet1'!$A$1)-1)


Obviously, if you develop something on your PC and then transfer it to
another PC, then the full path may be different, as the username is
likely to be different, but you could get round this by creating a
folder directly in C:.

Anyway, this formula will maintain the link to Data.xls and will give
you what you require - obviously, you may need to change the cell
references (I think you referred to G2 earlier).

Hope this helps.

Pete

Hi Pete,

Sorry for the confusion.

There are two workbooks involved. Only the one will be open. This will
be the report.

For example.

Data.xls - This will contain comma seperated values exported from a
logging system.

Report.xls - This will contain the above information, but formatted
nice.

The end user has not a lot of IT experience, so the idea is to make it
as simple as possible.

The data.xls file will be created by the user on a weekly basis. This
is all text based data. The date fields though include a created time
which we do not need. The format being as previously described.

Once this data.xls file has been created, it will be saved to the same
folder as Report.xls.

Once Report.xls is opened, after the option to update the external data
is taken, all the new information is displayed, ready for printing or
emailing out.

The issue that I am having is reducing the date / time field when it is
shown in Report.xls, but still retaining the link to the information
held in data.xls. When ammending the date field in Report.txt, the
field then go to a static text field and all links to the external data
held in data.xls is lost.

I am unable to perform any manipulation within data.xls.

Hope that clarifies my issue.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
M

matt.barton.uk

Hi Pete,

Yes all things being equal, that would be the best...... but.

The user is not very IT literate.

They have the option of exporting from their logging system a comma
delimited xls file.

Up until now, they have printed this off and manually entered the
information into the report spreadsheet. They do not want to have the
idea of exporting the data and using that by importing it to an all
signing & dancing spreadsheet. Old ways die hard and all that.

So my options are as follows.

1). find a really easy automated solution.
2). Tell them that the date is how it is and tough.
3). Go back to the manual way :)

But either way, thanks for your assitance on this matter, it has given
me some ideas and avenues to go down.

Cheers
 
M

matt.barton.uk

Hi Pete,

Yes all things being equal, that would be the best...... but.

The user is not very IT literate.

They have the option of exporting from their logging system a comma
delimited xls file.

Up until now, they have printed this off and manually entered the
information into the report spreadsheet. They do not want to have the
idea of exporting the data and using that by importing it to an all
signing & dancing spreadsheet. Old ways die hard and all that.

So my options are as follows.

1). find a really easy automated solution.
2). Tell them that the date is how it is and tough.
3). Go back to the manual way :)

But either way, thanks for your assitance on this matter, it has given
me some ideas and avenues to go down.

Cheers
 
M

matt.barton.uk

Update,

Tried it on another range of dates within the same spreadsheets and it
appears to work.

So off I shall go to look at the cell properties.

Thanks for all your help chaps..
 
M

matt.barton.uk

Update,

Tried it on another range of dates within the same spreadsheets and it
appears to work.

So off I shall go to look at the cell properties.

Thanks for all your help chaps..
 
Top