Create date field from numbers with inconsistent entries

Discussion created by kmakat on Aug 31, 2016
Latest reply on Sep 1, 2016 by kmakat



I have to enter a date but for many records I just have information about the year or the year and month. There are possibly much more elegant solutions, but I designed three number fields, Day, Month and Year, to enter the available information and then also created a calculation field cDate (result text) that creates a date format 16/31/2016 (result text); on the layout the format is changed to 2016-08-31.


While this works reasonably well for my single-user solution when I enter values for all three - day, month and year - it fails to evaluate if day or month is missing (result ?). I tried different conditional calculations of which the latest version is:


If(IsEmpty ( DayOfDiagnosis );

MonthOfDiagnosis & "/" & YearOfDiagnosis;

If(IsEmpty (MonthOfDiagnosis); YearOfDiagnosis; 

MonthOfDiagnosis & "/" & DayOfDiagnosis & "/" & YearOfDiagnosis))


Any tips and suggestions are very welcome!


Thank you,