Help to create custom formula to determine expired date

[aSoundMind]

Hi there,

I am trying to display 3 graphical indicators of red, yellow and
green.

The condition is

If the difference between current date and project finish < 1
display Green
If the difference between current date and project finish bettween 1
to 14
display Yellow
If the difference between current date and project finish > 14
display Red

I have tried to modify my formula to this but the result is #Error

Enterprise Project Duration 2 = ProjDateDiff(Now(), [Finish])

Enterprise ProjectDuration 1= [Enterprise Project Duration 2] -
[baseline duration]

The result is #Error. Is there anyway how to solve this? I need the
formula. Any help appreciated. And also please tell me how to debug
this formula, it's so troublesome to open n close microsoft project
just to try and retry the formula.

Thanks,
Jack

 

[aSoundMind]

Hi there,

I am trying to display 3 graphical indicators of red, yellow and
green.

The condition is

If the difference between current date and project finish < 1
display Green
If the difference between current date and project finish bettween 1
to 14
display Yellow
If the difference between current date and project finish > 14
display Red

I have tried to modify my formula to this but the result is #Error

Enterprise Project Duration 2 = ProjDateDiff(Now(), [Finish])

Enterprise ProjectDuration 1= [Enterprise Project Duration 2] -
[baseline duration]

The result is #Error. Is there anyway how to solve this? I need the
formula. Any help appreciated. And also please tell me how to debug
this formula, it's so troublesome to open n close microsoft project
just to try and retry the formula.

Thanks,
Jack

Hi,

I also tried to change the formula to

Enterprise Project Duration 1 = ProjDateDiff([current date], [project
finish]

But still it's giving the same #Error

Regards
//Jack

 

[Arun]

Try the following steps.
1.Insert the formula ProjDateDiff(Now(), [Finish]) inside the formula box.
2. In the graphical Indicators select 'is less than' and type one in the
value field and select the image.
3. Do the same thing for other condisions.

I hope this will help.
Thanks,
Arun

Hi there,

I am trying to display 3 graphical indicators of red, yellow and
green.

The condition is

If the difference between current date and project finish < 1
display Green
If the difference between current date and project finish bettween 1
to 14
display Yellow
If the difference between current date and project finish > 14
display Red

I have tried to modify my formula to this but the result is #Error

Enterprise Project Duration 2 = ProjDateDiff(Now(), [Finish])

Enterprise ProjectDuration 1= [Enterprise Project Duration 2] -
[baseline duration]

The result is #Error. Is there anyway how to solve this? I need the
formula. Any help appreciated. And also please tell me how to debug
this formula, it's so troublesome to open n close microsoft project
just to try and retry the formula.

Thanks,
Jack

Hi,

I also tried to change the formula to

Enterprise Project Duration 1 = ProjDateDiff([current date], [project
finish]

But still it's giving the same #Error

Regards
//Jack

 

[aSoundMind]

Hi Arun,

Yes, that's exactly what I did. I used "Enterprise Project Duration 1
=" for indicating in which field I put my code.

//Jack

 

[Arun]

Hi,
I could not understand why you are using the following..

Enterprise ProjectDuration 1= [Enterprise Project Duration 2] -
[baseline duration]

Put the formula ProjDateDiff(Date(), [Finish]) in the formula box.
Note : In the above formula I changed Now() into Date()

And set the condisions in Graphical indicators.

Thanks,
Arun

 

[aSoundMind]

Hi Arun,

Thanks for your reply.

I've tried your solution. Removing the enterprise project duration 2
and just use the enterprise duration 1 with the one u gave. I found
the formula confusing. For example, If I put the project finish at
4/4/2007 and the current date is 4/17/2007. So by right based on the
formula
dateDiff("d", date(), finish), I should be getting 14d. But instead I
am
getting 0.2d.

And also My concern is also eventhough latter the formula turn out to
be fine, there might be
case where a completed healthy project (green) displays green
indicator on
early stage, but as time goes on it will become yellow and then red
due to
current date keeps on increasing which is not what I am planning to
have.

I apologize in advance. Perhaps my question wasn't set right at first
place.
What I do need is to calculate the difference between the baseline
duration
with the actual duration. I have managed to do this.

But the problem is when the project is incomplete (the last task which
determine the project finish date is not 100%), then there is no
project
finish date. This results in either value = 0 or error. Whereby green
supposed to be indicate a NO problem project. So How to solve this
problem?
Formula is much appreciated

I hope you can get my scenario right. I apologize for giving confusing
question on earlier post.

Regards and Thanks,

Hi,
I could not understand why you are using the following..

Enterprise ProjectDuration 1= [Enterprise Project Duration 2] -
[baseline duration]

Put the formula ProjDateDiff(Date(), [Finish]) in the formula box.
Note : In the above formula I changed Now() into Date()

And set the condisions in Graphical indicators.

Thanks,
Arun

Hi Arun,

Yes, that's exactly what I did. I used "Enterprise Project Duration 1
=" for indicating in which field I put my code.

//Jack

 

[Arun]

Hi,
You can try with the following formula

IIf(INT([Finish]-Date())>0 And
INT([Finish]-Date())<14,'Approaching',IIf(INT([Finish]-Date())>14,"On
Schedule",IIf(INT([Finish]-Date())<=0 And [%
Complete]<100,"Late","Completed")))

In the grapicah indicators set the following

1.In the first column select 'Equal' and value column 'Approaching' and
select the image yellow
2.In the first column select 'Equal' and value column 'On Schedule' and
select the image green
3.In the first column select 'Equal' and value column 'Late' and select the
image red
4.In the first column select 'Equal' and value column 'Completed' and select
the image green

I hope this will help!

Thanks,
Arun

Hi Arun,

Thanks for your reply.

I've tried your solution. Removing the enterprise project duration 2
and just use the enterprise duration 1 with the one u gave. I found
the formula confusing. For example, If I put the project finish at
4/4/2007 and the current date is 4/17/2007. So by right based on the
formula
dateDiff("d", date(), finish), I should be getting 14d. But instead I
am
getting 0.2d.

And also My concern is also eventhough latter the formula turn out to
be fine, there might be
case where a completed healthy project (green) displays green
indicator on
early stage, but as time goes on it will become yellow and then red
due to
current date keeps on increasing which is not what I am planning to
have.

I apologize in advance. Perhaps my question wasn't set right at first
place.
What I do need is to calculate the difference between the baseline
duration
with the actual duration. I have managed to do this.

But the problem is when the project is incomplete (the last task which
determine the project finish date is not 100%), then there is no
project
finish date. This results in either value = 0 or error. Whereby green
supposed to be indicate a NO problem project. So How to solve this
problem?
Formula is much appreciated

I hope you can get my scenario right. I apologize for giving confusing
question on earlier post.

Regards and Thanks,

Hi,
I could not understand why you are using the following..

Enterprise ProjectDuration 1= [Enterprise Project Duration 2] -
[baseline duration]

Put the formula ProjDateDiff(Date(), [Finish]) in the formula box.
Note : In the above formula I changed Now() into Date()

And set the condisions in Graphical indicators.

Thanks,
Arun

Hi Arun,

Yes, that's exactly what I did. I used "Enterprise Project Duration 1
=" for indicating in which field I put my code.

//Jack

 

[aSoundMind]

Hi,
You can try with the following formula

IIf(INT([Finish]-Date())>0 And
INT([Finish]-Date())<14,'Approaching',IIf(INT([Finish]-Date())>14,"On
Schedule",IIf(INT([Finish]-Date())<=0 And [%
Complete]<100,"Late","Completed")))

In the grapicah indicators set the following

1.In the first column select 'Equal' and value column 'Approaching' and
select the image yellow
2.In the first column select 'Equal' and value column 'On Schedule' and
select the image green
3.In the first column select 'Equal' and value column 'Late' and select the
image red
4.In the first column select 'Equal' and value column 'Completed' and select
the image green

I hope this will help!

Thanks,
Arun

Hi Arun,

When using your formula, on the conditional set I recieved error that
it can't get the value.

So 'on schedule', 'completed', etc can't be found. I changed them to
0,1,2,3 in order of what you told me
'Approaching' = 0
'On Schedule' = 1

and so on. the color was inserted accrodingly too. But when I run the
formula I still recieve the value 0.01 when I view it in project
information. In Pwa, it shows as 0.01 too.

If you don't mind, would you consider if I send my project plan .mpp
file to you. Perhaps you could point out if there's an error related
to the plan itself. Please find the uploaded plan here. Thanks

http://gudangupload.com/filelink.ph...c67a433adce7b44e854653597f565a8fb7ff7bb2af2fe

//Jack

 

[aSoundMind]

Hi,
You can try with the following formula

IIf(INT([Finish]-Date())>0 And
INT([Finish]-Date())<14,'Approaching',IIf(INT([Finish]-Date())>14,"On
Schedule",IIf(INT([Finish]-Date())<=0 And [%
Complete]<100,"Late","Completed")))

In the grapicah indicators set the following

1.In the first column select 'Equal' and value column 'Approaching' and
select the image yellow
2.In the first column select 'Equal' and value column 'On Schedule' and
select the image green
3.In the first column select 'Equal' and value column 'Late' and select the
image red
4.In the first column select 'Equal' and value column 'Completed' and select
the image green

I hope this will help!

Thanks,
Arun

Hi Arun,

When using your formula, on the conditional set I recieved error that
it can't get the value.

So 'on schedule', 'completed', etc can't be found. I changed them to
0,1,2,3 in order of what you told me
'Approaching' = 0
'On Schedule' = 1

and so on. the color was inserted accrodingly too. But when I run the
formula I still recieve the value 0.01 when I view it in project
information. In Pwa, it shows as 0.01 too.

If you don't mind, would you consider if I send my project plan .mpp
file to you. Perhaps you could point out if there's an error related
to the plan itself. Please find the uploaded plan here. Thanks

http://gudangupload.com/filelink.php?filecode=e344ac1f1fe492d68a5c67a...

//Jack- Hide quoted text -

- Show quoted text -

Hi Again,

Maybe this might not be related coding wise, but I have the logic
in .net. which is

date_variance = datediff(dd, task_start_date, task_act_finish) -
datediff(dd, task_start_date, task_finish_date)

Then in the code, there will be cases where the date_variance is null
usuall because the actual duration is null
so I check

If Row.Isdate_varianceNull = True Then
Days = DateDiff(DateInterval.Day,
DateTime.Now(), Row.task_finish_date)
Else
Days = Row.date_variance
End If

Then I specify the day condition

If days >= 0 Then
path = "green.png"
ElseIf days > 0 And days < -14 Then
path = "yellow.png"
Else
path = "red.png"
End If

Based on this code I achieved what I want. But I am clueless on how to
do the formula exactly in Project Pro. I hope this code is clear
enough to illustrate what I need. Thank you

//Jack

 

[Arun]

Hi,

I uploaded your project plan, in my project server , it is working fine. Few
things I want to confirm from you.
1.Whether you are using Text field for this custom field?
2.If so whether it is working fine in your project plan first?

Thanks,
Arun

Hi,
You can try with the following formula

IIf(INT([Finish]-Date())>0 And
INT([Finish]-Date())<14,'Approaching',IIf(INT([Finish]-Date())>14,"On
Schedule",IIf(INT([Finish]-Date())<=0 And [%
Complete]<100,"Late","Completed")))

In the grapicah indicators set the following

1.In the first column select 'Equal' and value column 'Approaching' and
select the image yellow
2.In the first column select 'Equal' and value column 'On Schedule' and
select the image green
3.In the first column select 'Equal' and value column 'Late' and select the
image red
4.In the first column select 'Equal' and value column 'Completed' and select
the image green

I hope this will help!

Thanks,
Arun

Hi Arun,

When using your formula, on the conditional set I recieved error that
it can't get the value.

So 'on schedule', 'completed', etc can't be found. I changed them to
0,1,2,3 in order of what you told me
'Approaching' = 0
'On Schedule' = 1

and so on. the color was inserted accrodingly too. But when I run the
formula I still recieve the value 0.01 when I view it in project
information. In Pwa, it shows as 0.01 too.

If you don't mind, would you consider if I send my project plan .mpp
file to you. Perhaps you could point out if there's an error related
to the plan itself. Please find the uploaded plan here. Thanks

http://gudangupload.com/filelink.ph...c67a433adce7b44e854653597f565a8fb7ff7bb2af2fe

//Jack

 

[aSoundMind]

Hi Arun,

To answer your question

1. > 1.Whether you are using Text field for this custom field?
No, I use enterprise project duration. so it's duration field

2.If so whether it is working fine in your project plan first?

I have not try it on text as for now.

Regards,
//Jack

Hi,

I uploaded your project plan, in my project server , it is working fine. Few
things I want to confirm from you.
1.Whether you are using Text field for this custom field?
2.If so whether it is working fine in your project plan first?

Thanks,
Arun

Hi,
You can try with the following formula

IIf(INT([Finish]-Date())>0 And
INT([Finish]-Date())<14,'Approaching',IIf(INT([Finish]-Date())>14,"On
Schedule",IIf(INT([Finish]-Date())<=0 And [%
Complete]<100,"Late","Completed")))

In the grapicah indicators set the following

1.In the first column select 'Equal' and value column 'Approaching' and
select the image yellow
2.In the first column select 'Equal' and value column 'On Schedule' and
select the image green
3.In the first column select 'Equal' and value column 'Late' and select the
image red
4.In the first column select 'Equal' and value column 'Completed' and select
the image green

I hope this will help!

Thanks,
Arun

Hi Arun,

When using your formula, on the conditional set I recieved error that
it can't get the value.

So 'on schedule', 'completed', etc can't be found. I changed them to
0,1,2,3 in order of what you told me
'Approaching' = 0
'On Schedule' = 1

and so on. the color was inserted accrodingly too. But when I run the
formula I still recieve the value 0.01 when I view it in project
information. In Pwa, it shows as 0.01 too.

If you don't mind, would you consider if I send my project plan .mpp
file to you. Perhaps you could point out if there's an error related
to the plan itself. Please find the uploaded plan here. Thanks

http://gudangupload.com/filelink.ph...c67a433adce7b44e854653597f565a8fb7ff7bb2af2fe

//Jack

 

[aSoundMind]

Hi Arun

Answering your question
1.Whether you are using Text field for this custom field?
I am using Enterprise Project Duration 1 field, so it's the duration
field
2.If so whether it is working fine in your project plan first?
I have not try it on text field coz my thought is since this is
looking for duration, then put it in the duration field.

Thanks,
Jack

Hi,

I uploaded your project plan, in my project server , it is working fine. Few
things I want to confirm from you.
1.Whether you are using Text field for this custom field?
2.If so whether it is working fine in your project plan first?

Thanks,
Arun

Hi,
You can try with the following formula

IIf(INT([Finish]-Date())>0 And
INT([Finish]-Date())<14,'Approaching',IIf(INT([Finish]-Date())>14,"On
Schedule",IIf(INT([Finish]-Date())<=0 And [%
Complete]<100,"Late","Completed")))

In the grapicah indicators set the following

1.In the first column select 'Equal' and value column 'Approaching' and
select the image yellow
2.In the first column select 'Equal' and value column 'On Schedule' and
select the image green
3.In the first column select 'Equal' and value column 'Late' and select the
image red
4.In the first column select 'Equal' and value column 'Completed' and select
the image green

I hope this will help!

Thanks,
Arun

Hi Arun,

When using your formula, on the conditional set I recieved error that
it can't get the value.

So 'on schedule', 'completed', etc can't be found. I changed them to
0,1,2,3 in order of what you told me
'Approaching' = 0
'On Schedule' = 1

and so on. the color was inserted accrodingly too. But when I run the
formula I still recieve the value 0.01 when I view it in project
information. In Pwa, it shows as 0.01 too.

If you don't mind, would you consider if I send my project plan .mpp
file to you. Perhaps you could point out if there's an error related
to the plan itself. Please find the uploaded plan here. Thanks

http://gudangupload.com/filelink.ph...c67a433adce7b44e854653597f565a8fb7ff7bb2af2fe

//Jack

 

[Arun]

Hi,
I think it is better you can use text field. Because your going to use
for graphical display only...

Thanks,
Arun

Hi Arun

Answering your question
1.Whether you are using Text field for this custom field?
I am using Enterprise Project Duration 1 field, so it's the duration
field
2.If so whether it is working fine in your project plan first?
I have not try it on text field coz my thought is since this is
looking for duration, then put it in the duration field.

Thanks,
Jack

Hi,

I uploaded your project plan, in my project server , it is working fine. Few
things I want to confirm from you.
1.Whether you are using Text field for this custom field?
2.If so whether it is working fine in your project plan first?

Thanks,
Arun

Hi,
You can try with the following formula

IIf(INT([Finish]-Date())>0 And
INT([Finish]-Date())<14,'Approaching',IIf(INT([Finish]-Date())>14,"On
Schedule",IIf(INT([Finish]-Date())<=0 And [%
Complete]<100,"Late","Completed")))

In the grapicah indicators set the following

1.In the first column select 'Equal' and value column 'Approaching' and
select the image yellow
2.In the first column select 'Equal' and value column 'On Schedule' and
select the image green
3.In the first column select 'Equal' and value column 'Late' and select the
image red
4.In the first column select 'Equal' and value column 'Completed' and select
the image green

I hope this will help!

Thanks,
Arun

Hi Arun,

When using your formula, on the conditional set I recieved error that
it can't get the value.

So 'on schedule', 'completed', etc can't be found. I changed them to
0,1,2,3 in order of what you told me
'Approaching' = 0
'On Schedule' = 1

and so on. the color was inserted accrodingly too. But when I run the
formula I still recieve the value 0.01 when I view it in project
information. In Pwa, it shows as 0.01 too.

If you don't mind, would you consider if I send my project plan .mpp
file to you. Perhaps you could point out if there's an error related
to the plan itself. Please find the uploaded plan here. Thanks

http://gudangupload.com/filelink.ph...c67a433adce7b44e854653597f565a8fb7ff7bb2af2fe

//Jack

 

[aSoundMind]

Hi,
I think it is better you can use text field. Because your going to use
for graphical display only...

Thanks,
Arun

Hi Arun

Answering your question
1.Whether you are using Text field for this custom field?
I am using Enterprise Project Duration 1 field, so it's the duration
field
2.If so whether it is working fine in your project plan first?
I have not try it on text field coz my thought is since this is
looking for duration, then put it in the duration field.

Thanks,
Jack

Hi,
I uploaded your project plan, in my project server , it is working fine. Few
things I want to confirm from you.
1.Whether you are using Text field for this custom field?
2.If so whether it is working fine in your project plan first?
Thanks,
Arun
:
Hi,
You can try with the following formula
IIf(INT([Finish]-Date())>0 And
INT([Finish]-Date())<14,'Approaching',IIf(INT([Finish]-Date())>14,"On
Schedule",IIf(INT([Finish]-Date())<=0 And [%
Complete]<100,"Late","Completed")))
In the grapicah indicators set the following
1.In the first column select 'Equal' and value column 'Approaching' and
select the image yellow
2.In the first column select 'Equal' and value column 'On Schedule' and
select the image green
3.In the first column select 'Equal' and value column 'Late' and select the
image red
4.In the first column select 'Equal' and value column 'Completed' and select
the image green
I hope this will help!
Thanks,
Arun
Hi Arun,
When using your formula, on the conditional set I recieved error that
it can't get the value.
So 'on schedule', 'completed', etc can't be found. I changed them to
0,1,2,3 in order of what you told me
'Approaching' = 0
'On Schedule' = 1
and so on. the color was inserted accrodingly too. But when I run the
formula I still recieve the value 0.01 when I view it in project
information. In Pwa, it shows as 0.01 too.
If you don't mind, would you consider if I send my project plan .mpp
file to you. Perhaps you could point out if there's an error related
to the plan itself. Please find the uploaded plan here. Thanks
http://gudangupload.com/filelink.php?filecode=e344ac1f1fe492d68a5c67a...
//Jack- Hide quoted text -

- Show quoted text -

Hi Arun,

Thanks I tried using the text field and now it's working fine. It's
still being tested by the users. I will update you if there's any
problem. Thank you for your assistance. I really appreciate it.