ProjDateDiff anomaly

[Dytham]

On a Number field, and using a standard formula:

IIf(Finish1<>ProjDateValue(0),ProjDateDiff([Finish1],[Finish])/480,0)

I am getting a spurious value returned against a few SUMMARY rows.

This does not happen if a TEXT field is used for the output.

The values being returned are:
-4294927579.67 once
-4294927579.67 three occurrences.

On the rows in question, the values in Finish and Finish1 are identical -
comfirmed by re-pasting into the Finish1.

 

[JulieS]

Hello Dytham,

What have you set the summary rows equal to? It sounds as there may
be an error at the summary line.

I would also suggest rewriting the formula. A Date field cannot
equal 0, so if you are trying to test whether Finish1 is a date or
"NA", I'd suggest the following revised formula:

IIf(Finish1=ProjDateValue("NA"),0,ProjDateDiff([Finish1],[Finish])/480)

In testing, works fine in a number field.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[Dytham]

Yes, I was attempting to test Finish1 for a date or NA but messed it up.

That was, however, a red herring.

Reversing the formula has made no difference - still returns large negative
value.

All rows, including the the summary rows were made equal, firstly by setting
interim baseline, and later by direct copy and paste to Finish 1.

Hello Dytham,

What have you set the summary rows equal to? It sounds as there may
be an error at the summary line.

I would also suggest rewriting the formula. A Date field cannot
equal 0, so if you are trying to test whether Finish1 is a date or
"NA", I'd suggest the following revised formula:

IIf(Finish1=ProjDateValue("NA"),0,ProjDateDiff([Finish1],[Finish])/480)

In testing, works fine in a number field.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

On a Number field, and using a standard formula:

IIf(Finish1<>ProjDateValue(0),ProjDateDiff([Finish1],[Finish])/480,0)

I am getting a spurious value returned against a few SUMMARY rows.

This does not happen if a TEXT field is used for the output.

The values being returned are:
-4294927579.67 once
-4294927579.67 three occurrences.

On the rows in question, the values in Finish and Finish1 are
identical -
comfirmed by re-pasting into the Finish1.

 

[JulieS]

Dytham,

In Tools > Customize Toolbars, what is the setting for the summary
rows on Finish1? What have you set for the summary row calculation
for the Number1 date? Is your calculation mode set to automatic
(Tools > Options, Calculation)?

Julie

Yes, I was attempting to test Finish1 for a date or NA but messed
it up.

That was, however, a red herring.

Reversing the formula has made no difference - still returns large
negative
value.

All rows, including the the summary rows were made equal, firstly
by setting
interim baseline, and later by direct copy and paste to Finish 1.

Hello Dytham,

What have you set the summary rows equal to? It sounds as there
may
be an error at the summary line.

I would also suggest rewriting the formula. A Date field cannot
equal 0, so if you are trying to test whether Finish1 is a date
or
"NA", I'd suggest the following revised formula:

IIf(Finish1=ProjDateValue("NA"),0,ProjDateDiff([Finish1],[Finish])/480)

In testing, works fine in a number field.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

On a Number field, and using a standard formula:

IIf(Finish1<>ProjDateValue(0),ProjDateDiff([Finish1],[Finish])/480,0)

I am getting a spurious value returned against a few SUMMARY
rows.

This does not happen if a TEXT field is used for the output.

The values being returned are:
-4294927579.67 once
-4294927579.67 three occurrences.

On the rows in question, the values in Finish and Finish1 are
identical -
comfirmed by re-pasting into the Finish1.

 

[JulieS]

Sorry. Not fingers moving -- brain not participating. It should
have been Tools > Customize > Fields.

Dytham,

In Tools > Customize Toolbars, what is the setting for the summary
rows on Finish1? What have you set for the summary row
calculation for the Number1 date? Is your calculation mode set to
automatic (Tools > Options, Calculation)?

Julie

Yes, I was attempting to test Finish1 for a date or NA but messed
it up.

That was, however, a red herring.

Reversing the formula has made no difference - still returns
large negative
value.

All rows, including the the summary rows were made equal, firstly
by setting
interim baseline, and later by direct copy and paste to Finish 1.

Hello Dytham,

What have you set the summary rows equal to? It sounds as there
may
be an error at the summary line.

I would also suggest rewriting the formula. A Date field cannot
equal 0, so if you are trying to test whether Finish1 is a date
or
"NA", I'd suggest the following revised formula:

IIf(Finish1=ProjDateValue("NA"),0,ProjDateDiff([Finish1],[Finish])/480)

In testing, works fine in a number field.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project


On a Number field, and using a standard formula:

IIf(Finish1<>ProjDateValue(0),ProjDateDiff([Finish1],[Finish])/480,0)

I am getting a spurious value returned against a few SUMMARY
rows.

This does not happen if a TEXT field is used for the output.

The values being returned are:
-4294927579.67 once
-4294927579.67 three occurrences.

On the rows in question, the values in Finish and Finish1 are
identical -
comfirmed by re-pasting into the Finish1.

 

[Dytham]

Glad you clarified that

"Calculation for task and Group Summary rows" are set to None on both
Finish1 and Number1. Calculation mode is set to automatic.

Sorry. Not fingers moving -- brain not participating. It should
have been Tools > Customize > Fields.

Dytham,

In Tools > Customize Toolbars, what is the setting for the summary
rows on Finish1? What have you set for the summary row
calculation for the Number1 date? Is your calculation mode set to
automatic (Tools > Options, Calculation)?

Julie

Yes, I was attempting to test Finish1 for a date or NA but messed
it up.

That was, however, a red herring.

Reversing the formula has made no difference - still returns
large negative
value.

All rows, including the the summary rows were made equal, firstly
by setting
interim baseline, and later by direct copy and paste to Finish 1.

:

Hello Dytham,

What have you set the summary rows equal to? It sounds as there
may
be an error at the summary line.

I would also suggest rewriting the formula. A Date field cannot
equal 0, so if you are trying to test whether Finish1 is a date
or
"NA", I'd suggest the following revised formula:

IIf(Finish1=ProjDateValue("NA"),0,ProjDateDiff([Finish1],[Finish])/480)

In testing, works fine in a number field.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project


On a Number field, and using a standard formula:

IIf(Finish1<>ProjDateValue(0),ProjDateDiff([Finish1],[Finish])/480,0)

I am getting a spurious value returned against a few SUMMARY
rows.

This does not happen if a TEXT field is used for the output.

The values being returned are:
-4294927579.67 once
-4294927579.67 three occurrences.

On the rows in question, the values in Finish and Finish1 are
identical -
comfirmed by re-pasting into the Finish1.

 

[Dytham]

Changing the setting for "Calculation for task and Group Summary rows" on
Number1 to "Use Formula" cures the anomaly.

Thanks for your help Julie.

Glad you clarified that

"Calculation for task and Group Summary rows" are set to None on both
Finish1 and Number1. Calculation mode is set to automatic.

Sorry. Not fingers moving -- brain not participating. It should
have been Tools > Customize > Fields.

Dytham,

In Tools > Customize Toolbars, what is the setting for the summary
rows on Finish1? What have you set for the summary row
calculation for the Number1 date? Is your calculation mode set to
automatic (Tools > Options, Calculation)?

Julie
Yes, I was attempting to test Finish1 for a date or NA but messed
it up.

That was, however, a red herring.

Reversing the formula has made no difference - still returns
large negative
value.

All rows, including the the summary rows were made equal, firstly
by setting
interim baseline, and later by direct copy and paste to Finish 1.

:

Hello Dytham,

What have you set the summary rows equal to? It sounds as there
may
be an error at the summary line.

I would also suggest rewriting the formula. A Date field cannot
equal 0, so if you are trying to test whether Finish1 is a date
or
"NA", I'd suggest the following revised formula:

IIf(Finish1=ProjDateValue("NA"),0,ProjDateDiff([Finish1],[Finish])/480)

In testing, works fine in a number field.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project


On a Number field, and using a standard formula:

IIf(Finish1<>ProjDateValue(0),ProjDateDiff([Finish1],[Finish])/480,0)

I am getting a spurious value returned against a few SUMMARY
rows.

This does not happen if a TEXT field is used for the output.

The values being returned are:
-4294927579.67 once
-4294927579.67 three occurrences.

On the rows in question, the values in Finish and Finish1 are
identical -
comfirmed by re-pasting into the Finish1.

 

[JulieS]

You're most welcome Dytham. As you discovered, you need to
specifically tell Project to use the formula for summary rows.

Thanks for the feedback.
Julie

Changing the setting for "Calculation for task and Group Summary
rows" on
Number1 to "Use Formula" cures the anomaly.

Thanks for your help Julie.

Glad you clarified that

"Calculation for task and Group Summary rows" are set to None on
both
Finish1 and Number1. Calculation mode is set to automatic.

Sorry. Not fingers moving -- brain not participating. It
should
have been Tools > Customize > Fields.
Dytham,

In Tools > Customize Toolbars, what is the setting for the
summary
rows on Finish1? What have you set for the summary row
calculation for the Number1 date? Is your calculation mode
set to
automatic (Tools > Options, Calculation)?

Julie
Yes, I was attempting to test Finish1 for a date or NA but
messed
it up.

That was, however, a red herring.

Reversing the formula has made no difference - still returns
large negative
value.

All rows, including the the summary rows were made equal,
firstly
by setting
interim baseline, and later by direct copy and paste to
Finish 1.

:

Hello Dytham,

What have you set the summary rows equal to? It sounds as
there
may
be an error at the summary line.

I would also suggest rewriting the formula. A Date field
cannot
equal 0, so if you are trying to test whether Finish1 is a
date
or
"NA", I'd suggest the following revised formula:

IIf(Finish1=ProjDateValue("NA"),0,ProjDateDiff([Finish1],[Finish])/480)

In testing, works fine in a number field.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project


message
On a Number field, and using a standard formula:

IIf(Finish1<>ProjDateValue(0),ProjDateDiff([Finish1],[Finish])/480,0)

I am getting a spurious value returned against a few
SUMMARY
rows.

This does not happen if a TEXT field is used for the
output.

The values being returned are:
-4294927579.67 once
-4294927579.67 three occurrences.

On the rows in question, the values in Finish and Finish1
are
identical -
comfirmed by re-pasting into the Finish1.