Calculations Based on Dates

Discussion created by EssexBiker on May 7, 2016
Latest reply on May 7, 2016 by EssexBiker

Just before I posted this, I had a lightbulb moment, did a double check, and then realised that one of my fields [termination date] was a text field and not a date.


Posted to make the other daft ones like me have a think again before sticking an axe through the computer!




We all have to learn to look for the obvious, I guess!!!




Help, help, help!


I really need to just have a calculation that returns a number value of employed days for the current (calendar) year, correctly,  whether the termination date is populated or not  if I do populate the termination date, unexpected results are returned.

I have


Engagement date (date field)

Termination Date ( date field).... Big Bloody Whoops, It was a text field!


I  have created  a calculation  that returns the maximum value of Engagement Date or 01.01 of the current year.   (for those that were hired  before the present year.  ( or to my friends across the pond 12, 31, XXXX) Or to my friends across the pond 12, 31, XXXX, we're a bit funny over in Limey Land, but we do spell Colour, beautifully ;-) )


Case( IsEmpty (Termination Date); Get(CurrentDate) - Holiday  Current Year Start Date_cn +1 ;


Termination Date - Holiday  Current Year Start Date_cn +1)


I've been able to write calculations to return the result of employed days either in the current (calendar year, 01,01.xx) , or since they started if that was before ( 31.12.XX), or indeed if they started after the 01 Jan in the current year, that's all good.


But I am struggling....


If a member of staff leaves and  the Termination Date is populated,  then get unexpected results in the calculation of the amount of days. Either ?, ( the display field is long enough, or a value that's in the 10s of thousands,



Any assistance would be most gratefully received.