2

I need to subtract two date fields in order to get the number of days as the difference. I tried subtracting them in the "Default Value" section of the Field. But the message Incorrect Data Type for operator or @function appears on the screen. The field was defined as an editable/number field.

Can you please advice on how to properly solve this problem using the @function in the "default value" section of the field property?

Thank you for kind help.

Arnab
  • 4,216
  • 2
  • 28
  • 50
ronald martinez
  • 43
  • 1
  • 4

2 Answers2

2

You can find the number of seconds between two dates by subtracting them. Then get the days by dividing by 86400 (seconds in a day). No @function is required.

Ken Pespisa
  • 21,989
  • 3
  • 55
  • 63
  • I tried to do that exactly in a field called DaysElapsed (number field and computed field) but the error "Incorrect data type for operator of @Function" appears on the screen once I try to view it in the client mode. What am I doing wrong sir? – ronald martinez Sep 09 '11 at 06:34
  • Are you sure that error is coming from the DaysElapsed field? If you don't have an @function in the computed field, it could be coming from elsewhere. – Ken Pespisa Sep 09 '11 at 11:36
  • Yes Ken I finally figured out where the error comes from. My start date and end dates did not have the default date values. When placed @Today or @ Now as the default values for these fields I was able to compute for the difference between the start and end dates. Thanks! – ronald martinez Sep 14 '11 at 12:37
1

I tried to simulate your problem, and I think I know what your issue is. In fact you almost have it working. Use this formula in the computed "DaysElapsed" field.

@If(@IsTime(Date1) & @IsTime(Date2);(Date2-date1)/86400;0);

If the date fields are blank whilst in edit mode, the calculation will fail because a blank value is not a "date", they are blank values which cannot have mathematical functions performed on them. So, a bit of type checking should fix it. I have used a default of zero whilst the values cannot be calculated.

angryITguy
  • 9,332
  • 8
  • 54
  • 82
  • Hi giulio! I have tried to use your formula in my "DaysElapsed" and placed in "Input Translation" property of the field. I computed the no of days difference perfectly! Thank very much for your help. Furthermore to accurately compute the business days between dates I have modified your formula, instead of subtracting the dates, I placed the function BusinessDays in the formula. It now computes the correct no of business days from the Start Date and End Date. Once again thank you for your Help!!!!! – ronald martinez Sep 12 '11 at 05:13
  • Why would you put it in the input translation property of the field? Make it a computed field and just put it in the value of the field. – David Navarre Jun 27 '12 at 14:06
  • @David. Good point as Ronald's comment indicates that's what he wanted to do all along. So yeah, you can put it there to. Computations in computed fields make the values available for other calculations, in LotusScript and other fields, whereas translation formulas are isolated. It's a matter of requirements and personal preference. – angryITguy Jun 27 '12 at 23:46
  • @guilio, I'm trying to imagine what requirement one would be fulfilling by using that formula, which does not reference the current field, in an input translation formula. Anything the user enters in the editable field would be replaced with the computation of the other two fields, making it not really an editable field any more. While there are often multiple ways to do things in Notes that can be left to personal preference and requirements, this particular instance is not one of them. – David Navarre Jun 28 '12 at 17:49