Formula Question using Calendar

[Marc]

I am trying to write a formula in a customized field that will show me which
tasks are:

1.) Overdue
2.) Due in 3 month
3.) Due in 3-6 month
4.) Due in over 6 months

I believe I have one that works, however, it is using the calendar within
project and when it goes out 'X' number of days, it is NOT counting weekends
and holidays.

Below is the formula I have. I believe I need to change the "Project
Calendar" to something else, but I don't know what that "something else" is.

IIf(Text1([Current Date],[Finish],[Project
Calendar])/10560<0,"Overdue",IIf(Text1([Current Date],[Finish],[Project
Calendar])/10560>=0 And Text1([Current Date],[Finish],[Project
Calendar])/10560<3,"Due within 3 months",IIf(Text1([Current
Date],[Finish],[Project Calendar])/10560>=3 And Text1([Current
Date],[Finish],[Project Calendar])/10560<6,"Due between 3 - 6
months",IIf(Text1([Current Date],[Finish],[Project Calendar])/10560>=6,"Due
beyond 6 months",""))))

Can someone please help me?

Thanks!
Marc

 

[Jan De Messemaeker]

Hi,

Not knowing what the formula in Text1 is, it's a bit guesswork but I suggest
the following:
Create a 7 days calendar (Tools, Change Working time, new) with all days
working days, give it a name (say "NoRest")
Replace [Project Calendar] by "NoRest"
HTH

 

[JackD]

If you want calendar days, just use some simple math.

IIf([Finish]-[Current Date]>180,"More than 6 months",IIf([Finish]-[Current
Date]>90,"More than 3 months",IIf([Finish]-[Current Date]>0,"within 3
months","overdue")))

Only use the calendar if you want to calculate days according to Project's
calendars, otherwise it is simple subtraction. Note that order is important.
The statement will stop at the first condition that is satisfied.

 

[Marc]

Jack - Thank you for the response. I tried using the formula below but MS
Project rejects is. I get a window that says: "The formula contains a syntax
error or contains a reference to an unrecognized field or function name."

Any suggestions?

If you want calendar days, just use some simple math.

IIf([Finish]-[Current Date]>180,"More than 6 months",IIf([Finish]-[Current
Date]>90,"More than 3 months",IIf([Finish]-[Current Date]>0,"within 3
months","overdue")))

Only use the calendar if you want to calculate days according to Project's
calendars, otherwise it is simple subtraction. Note that order is important.
The statement will stop at the first condition that is satisfied.
--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html

I am trying to write a formula in a customized field that will show me which
tasks are:

1.) Overdue
2.) Due in 3 month
3.) Due in 3-6 month
4.) Due in over 6 months

I believe I have one that works, however, it is using the calendar within
project and when it goes out 'X' number of days, it is NOT counting weekends
and holidays.

Below is the formula I have. I believe I need to change the "Project
Calendar" to something else, but I don't know what that "something else" is.

IIf(Text1([Current Date],[Finish],[Project
Calendar])/10560<0,"Overdue",IIf(Text1([Current Date],[Finish],[Project
Calendar])/10560>=0 And Text1([Current Date],[Finish],[Project
Calendar])/10560<3,"Due within 3 months",IIf(Text1([Current
Date],[Finish],[Project Calendar])/10560>=3 And Text1([Current
Date],[Finish],[Project Calendar])/10560<6,"Due between 3 - 6
months",IIf(Text1([Current Date],[Finish],[Project Calendar])/10560>=6,"Due
beyond 6 months",""))))

Can someone please help me?

Thanks!
Marc

 

[JackD]

It worked when I wrote it and it still works. If you cut and pasted make
sure you got everything and that you remove any line breaks. If you do not
fix the line breaks you will get a syntax error. Look for a line break
between "Current" and "Date" and remove it. Same with the one between 3 and
Months.

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
..

Jack - Thank you for the response. I tried using the formula below but MS
Project rejects is. I get a window that says: "The formula contains a syntax
error or contains a reference to an unrecognized field or function name."

Any suggestions?

If you want calendar days, just use some simple math.

IIf([Finish]-[Current Date]>180,"More than 6 months",IIf([Finish]-[Current
Date]>90,"More than 3 months",IIf([Finish]-[Current Date]>0,"within 3
months","overdue")))

Only use the calendar if you want to calculate days according to Project's
calendars, otherwise it is simple subtraction. Note that order is important.
The statement will stop at the first condition that is satisfied.
--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html

I am trying to write a formula in a customized field that will show me which
tasks are:

1.) Overdue
2.) Due in 3 month
3.) Due in 3-6 month
4.) Due in over 6 months

I believe I have one that works, however, it is using the calendar within
project and when it goes out 'X' number of days, it is NOT counting weekends
and holidays.

Below is the formula I have. I believe I need to change the "Project
Calendar" to something else, but I don't know what that "something

else"
is.

IIf(Text1([Current Date],[Finish],[Project
Calendar])/10560<0,"Overdue",IIf(Text1([Current Date],[Finish],[Project
Calendar])/10560>=0 And Text1([Current Date],[Finish],[Project
Calendar])/10560<3,"Due within 3 months",IIf(Text1([Current
Date],[Finish],[Project Calendar])/10560>=3 And Text1([Current
Date],[Finish],[Project Calendar])/10560<6,"Due between 3 - 6
months",IIf(Text1([Current Date],[Finish],[Project Calendar])/10560>=6,"Due
beyond 6 months",""))))

Can someone please help me?

Thanks!
Marc

 

[Marc]

That was exactly the problem... THANK YOU!!! It works beautifully.

In fact, I altered it a bit. The way it is written below, if a task was
already 100%, it would state "overdue" when it wasn't, so I added a line so
that if it is already 100% complete, to state "Complete".

The new formula is: IIf([% Complete]=100,"Complete",IIf([Finish]-[Current
Date]>180,"More than 6 Months",IIf([Finish]-[Current Date]>90,"More than 3
Months",IIf([Finish]-[Current Date]>0,"Within 3 Months","Overdue"))))

It worked when I wrote it and it still works. If you cut and pasted make
sure you got everything and that you remove any line breaks. If you do not
fix the line breaks you will get a syntax error. Look for a line break
between "Current" and "Date" and remove it. Same with the one between 3 and
Months.

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
..

Jack - Thank you for the response. I tried using the formula below but MS
Project rejects is. I get a window that says: "The formula contains a syntax
error or contains a reference to an unrecognized field or function name."

Any suggestions?

If you want calendar days, just use some simple math.

IIf([Finish]-[Current Date]>180,"More than 6 months",IIf([Finish]-[Current
Date]>90,"More than 3 months",IIf([Finish]-[Current Date]>0,"within 3
months","overdue")))

Only use the calendar if you want to calculate days according to Project's
calendars, otherwise it is simple subtraction. Note that order is important.
The statement will stop at the first condition that is satisfied.
--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html



I am trying to write a formula in a customized field that will show me
which
tasks are:

1.) Overdue
2.) Due in 3 month
3.) Due in 3-6 month
4.) Due in over 6 months

I believe I have one that works, however, it is using the calendar within
project and when it goes out 'X' number of days, it is NOT counting
weekends
and holidays.

Below is the formula I have. I believe I need to change the "Project
Calendar" to something else, but I don't know what that "something else"
is.

IIf(Text1([Current Date],[Finish],[Project
Calendar])/10560<0,"Overdue",IIf(Text1([Current Date],[Finish],[Project
Calendar])/10560>=0 And Text1([Current Date],[Finish],[Project
Calendar])/10560<3,"Due within 3 months",IIf(Text1([Current
Date],[Finish],[Project Calendar])/10560>=3 And Text1([Current
Date],[Finish],[Project Calendar])/10560<6,"Due between 3 - 6
months",IIf(Text1([Current Date],[Finish],[Project
Calendar])/10560>=6,"Due
beyond 6 months",""))))

Can someone please help me?

Thanks!
Marc

 

[JackD]

No problem. Glad you got it working.

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
..

That was exactly the problem... THANK YOU!!! It works beautifully.

In fact, I altered it a bit. The way it is written below, if a task was
already 100%, it would state "overdue" when it wasn't, so I added a line so
that if it is already 100% complete, to state "Complete".

The new formula is: IIf([% Complete]=100,"Complete",IIf([Finish]-[Current
Date]>180,"More than 6 Months",IIf([Finish]-[Current Date]>90,"More than 3
Months",IIf([Finish]-[Current Date]>0,"Within 3 Months","Overdue"))))

It worked when I wrote it and it still works. If you cut and pasted make
sure you got everything and that you remove any line breaks. If you do not
fix the line breaks you will get a syntax error. Look for a line break
between "Current" and "Date" and remove it. Same with the one between 3 and
Months.

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
..

Jack - Thank you for the response. I tried using the formula below but MS
Project rejects is. I get a window that says: "The formula contains a syntax
error or contains a reference to an unrecognized field or function name."

Any suggestions?



:

If you want calendar days, just use some simple math.

IIf([Finish]-[Current Date]>180,"More than 6 months",IIf([Finish]-[Current
Date]>90,"More than 3 months",IIf([Finish]-[Current Date]>0,"within 3
months","overdue")))

Only use the calendar if you want to calculate days according to Project's
calendars, otherwise it is simple subtraction. Note that order is important.
The statement will stop at the first condition that is satisfied.
--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html



I am trying to write a formula in a customized field that will show me
which
tasks are:

1.) Overdue
2.) Due in 3 month
3.) Due in 3-6 month
4.) Due in over 6 months

I believe I have one that works, however, it is using the calendar within
project and when it goes out 'X' number of days, it is NOT counting
weekends
and holidays.

Below is the formula I have. I believe I need to change the "Project
Calendar" to something else, but I don't know what that "something else"
is.

IIf(Text1([Current Date],[Finish],[Project
Calendar])/10560<0,"Overdue",IIf(Text1([Current Date],[Finish],[Project
Calendar])/10560>=0 And Text1([Current Date],[Finish],[Project
Calendar])/10560<3,"Due within 3 months",IIf(Text1([Current
Date],[Finish],[Project Calendar])/10560>=3 And Text1([Current
Date],[Finish],[Project Calendar])/10560<6,"Due between 3 - 6
months",IIf(Text1([Current Date],[Finish],[Project
Calendar])/10560>=6,"Due
beyond 6 months",""))))

Can someone please help me?

Thanks!
Marc