concatenating dates and text

C

Chris_J_S

Where I work, we have people that have for YEARS been putting TBD (to be
determined) in place of dates above milestones or TBR (to be resolved) next
to dates that need further investigation. Please, please, please, don't
question the wisdom of doing this --- if I were king I would zap this
practice all together. However, some of our schedulers have been doing this
for years on Powerpoint Gantt charts, and you can get away with this. The
Customer is used to seeing schedules in this format.

Now that we have transitioned our schedulers to MS-Project, they still want
to put their TBD's and TBR's on the Gantt. Up until now, the procedure has
been to override the date field on the milestone with a text field, where the
text field would have the TBD or date+TBR. We could even control whether or
not you use the text override via a flag. Overriding to use the TBD in place
of a Date is not too difficult because you are swapping the date with a piece
of text. The TBR was a little more problemmatic. You might know that the
date for the milestone is "2/16 TBR", so you just put that into the text
field. But if the date changes, now you have to manually go into the text
field and update the date.

I have manually come up with a procedure that automatically detects whether
their is a TBD or TBR associated with a date and then subtitutes in the
correct information on the milestone. The key to the solution is to convert
the date to text, then from there you can concatenate two pieces of text. In
our solution, we have an enterprise text field (we are using Project Server)
called "TBx", which could just as easily be Text10. This text field contains
whether or not a TBD or TBR is associated with the record. The following
equation is applied to a text field that is then is used to be the text that
is used above our milestones. There are one of three possible outcomes with
this equation:
(1) TBD will be used as the text in place of the date for the milestone
(2) the date in mm/dd format will be concatenated with one space and TBR
(3) the date will be converted to mm/dd format

IIF([TBx]="TBD","TBD",IIF([TBx]="TBR",Trim(ProjDateConv([Start],pjDate_mm_dd))+" TBR",Trim(ProjDateConv([Start],pjDate_mm_dd))))

I think I copied this correctly --- forgive me for any syntax erros, but the
formula was developed on a secure system and I had to fat-finger this onto
this posting.

This was the starting point to what took me to the final solution. Our
final solution uses another field to control whether you want to adjust the
date left or right by a certain amount of spaces to compensate for milestones
that are close together on a rollup-line.

Needless to say there are other applications for this...
 
R

Rod Gill

If only you had waited! Project 2010 has a manual scheduling mode that lets
you type text in place of dates etc. Try downloading the Beta and having a
play.

Download from http://www.microsoft.com/project/2010/en/us/default.aspx

--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

Author of the only book on Project VBA, see: http://www.projectvbabook.com




Chris_J_S said:
Where I work, we have people that have for YEARS been putting TBD (to be
determined) in place of dates above milestones or TBR (to be resolved)
next
to dates that need further investigation. Please, please, please, don't
question the wisdom of doing this --- if I were king I would zap this
practice all together. However, some of our schedulers have been doing
this
for years on Powerpoint Gantt charts, and you can get away with this. The
Customer is used to seeing schedules in this format.

Now that we have transitioned our schedulers to MS-Project, they still
want
to put their TBD's and TBR's on the Gantt. Up until now, the procedure
has
been to override the date field on the milestone with a text field, where
the
text field would have the TBD or date+TBR. We could even control whether
or
not you use the text override via a flag. Overriding to use the TBD in
place
of a Date is not too difficult because you are swapping the date with a
piece
of text. The TBR was a little more problemmatic. You might know that the
date for the milestone is "2/16 TBR", so you just put that into the text
field. But if the date changes, now you have to manually go into the text
field and update the date.

I have manually come up with a procedure that automatically detects
whether
their is a TBD or TBR associated with a date and then subtitutes in the
correct information on the milestone. The key to the solution is to
convert
the date to text, then from there you can concatenate two pieces of text.
In
our solution, we have an enterprise text field (we are using Project
Server)
called "TBx", which could just as easily be Text10. This text field
contains
whether or not a TBD or TBR is associated with the record. The following
equation is applied to a text field that is then is used to be the text
that
is used above our milestones. There are one of three possible outcomes
with
this equation:
(1) TBD will be used as the text in place of the date for the milestone
(2) the date in mm/dd format will be concatenated with one space and TBR
(3) the date will be converted to mm/dd format

IIF([TBx]="TBD","TBD",IIF([TBx]="TBR",Trim(ProjDateConv([Start],pjDate_mm_dd))+"
TBR",Trim(ProjDateConv([Start],pjDate_mm_dd))))

I think I copied this correctly --- forgive me for any syntax erros, but
the
formula was developed on a secure system and I had to fat-finger this onto
this posting.

This was the starting point to what took me to the final solution. Our
final solution uses another field to control whether you want to adjust
the
date left or right by a certain amount of spaces to compensate for
milestones
that are close together on a rollup-line.

Needless to say there are other applications for this...

__________ Information from ESET Smart Security, version of virus
signature database 4855 (20100210) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4855 (20100210) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top