1 Reply Latest reply on May 7, 2016 10:34 AM by EssexBiker

    Calculations Based on Dates

    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.