0

I have a field that calculates "Years of Service" and calculated to either TODAY for a active employee OR to the termination date if terminated.  However, on the list dashboard if the start date is in the future it returns an error  #Name?

How can I also add a statement that if "start date" is in future then do not calculate anything, instead either leave field blank or return the text "Future Start"?  I have tried a few things but I cannot figure it out.  Help would be appreciated!  I am using SharePoint Online - Modern pages.

Here is the current statement I am using:

=CONCATENATE(IF(ISBLANK([Termination Date]),DATEDIF([Hire Date],NOW(),"y"),DATEDIF([Hire Date],[Termination Date],"y")),"  yrs, ",IF(ISBLANK([Termination Date]),DATEDIF([Hire Date],NOW(),"ym"),DATEDIF([Hire Date],[Termination Date],"ym"))," mths")

enter image description here

Improve this question
2
  • Another question - will this type of column only update when the record is edited? Would I have to use a flow to update on a regular basis?
    – lauri
    Sep 6, 2022 at 14:27
  • Yes, the calculation will only be performed on item modification/creation. If you want a stored value then, yes, you'll need to trigger updates to trigger recalculation. However, if you'd like the value calculated each time an item is viewed then you'll want to implement this with List Formatting.
    – theChrisKent
    Sep 7, 2022 at 13:54

1 Answer 1

Reset to default
0

Try using this formula:

=IF([Hire Date] > NOW(), "Future Start", CONCATENATE(IF(ISBLANK([Termination Date]),DATEDIF([Hire Date],NOW(),"y"),DATEDIF([Hire Date],[Termination Date],"y")),"  yrs, ",IF(ISBLANK([Termination Date]),DATEDIF([Hire Date],NOW(),"ym"),DATEDIF([Hire Date],[Termination Date],"ym"))," mths"))

Assuming [Hire Date] is the "start date" you mentioned in your question.

Improve this answer
1
  • 1
    Thank you!! Works perfect!
    – lauri
    Sep 6, 2022 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.